Classifying data in Excel

January 20, 2007 by Mike.Blakley.

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.

A simple procedure for stratifying data in Excel:

Select the menu item “data stratification” .

Select the Excel work book

Select the work sheet

Select the work sheet

Select the column of interest - i.e. data to be stratified.

Specify the stratification values to be used

Click the “Run” button to produce the chart.

Note there is also a text file produced that can be pasted into Excel to see the results as text. There is no charge for the software - it may be used for any purpose, including commercial and educational.

More info is available.

User manual.

Posted in Data Classification | No Comments »