A simple, yet efficient method to classify data within an Excel spreadsheet

Two common audit procedures are to classify data by their frequency of occurrence and to determine if there are any duplicate items. Often, this data may reside within an Excel spreadsheet. Both procedures can be done manually within Excel - for example by first sorting the data, doing subtotals, etc. However, this procedure can be both time consuming as well as error prone.

Here I discuss how this can be done very quickly, and I provide example procedure steps and screen shots of the results. The data I use to illustrate the concepts is the list of debarred medical providers in the State of New Jersey. This list can either be downloaded directly, or else you can use the spreadsheet I have already prepared. The reason for using this list is that it contains elements common in many audit tests - e.g. State code, dates, reason codes, etc. Also, the data is public record. The data elements that I will focus on in this exercise are: state code, effective date and action taken. Four analytical procedures will be performed:

1. Classify state codes by frequency

2. Determine if there are duplicate state codes.

3. Classify action taken by type,

4. Determine if there were any duplicate effective dates.

In order to follow this exercise, the following steps will need to be performed:

1. Download the Excel workbook which contains five sheets. Select the sheet named "NJInd". An alternative is to go directly to the State of New Jersey site, but you will need to import the data from text to Excel.

2. Select the range A2:L1625 (i.e. the columns from the name to the effective date for the entire sheet).

3. Once the range has been selected, copy it to the clipboard (Ctrl-C or Edit|Copy).

4. Open EZ-R XL. You can download it at no cost, if you don't have it installed. The installation "setup" is available directly for download.

5. "Paste" the data you selected in step 3, by clicking on the "Paste" button at the bottom of the EZ-R XL dialog.

6. Select the column named "IndState", by clicking on the name. An "X" should now appear in the "Selected" column and the column name should be highlighted.

7. From the drop-down menu in the upper left corner, select the drop-down item named "classify".

8. Click the "Run" bottom at the bottom of the dialog.

Below is the end result that you should have obtained...

This shows that about 80% of the individuals debarred in New Jersey are working/residing in New Jersey (as would be expected). However, there are individuals from other states (may need to click on the elevator bar ti see these).

A similar analysis can be performed to see if there are any state codes duplicated (there are). Select the column "Ind State" and the drop-down menu "duplicates". You should see the results shown below.

A similar analysis can be performed to classify the debarment actions. Select the column "Action" and the drop-down menu "classify". Suspension is the most prevalent action taken. You should see the results shown below.

Note that this same methodology can also be used for data files, which may contain considerably more rows than the Excel limitation of approximately 65,000.

There is also a very similar procedure available which does not require the installation of software. This procedure can be run directly from the Internet. Details and a help file are available.

You can leave comments, if you wish, at our audit blog.

There is also a web analytics version which can be run directly from the Internet using Excel or other data source (in tab separated value format). View Web Analytics details. All software is provided at no cost.