Performing common audit tasks and analyzing data containing on Excel worksheets can now be as easy as 1,2 3.... This is done by 1) opening an Excel worksheet, 2) selecting a range and copying the data to the clipboard and pasting the data into the Audit Commander and 3) analyzing it with the Audit Commander. The results can be then either be viewed or pasted back into Excel!

Note: A version with greatly expanded capabilities has been released. (also free)

To illustrate its possible use, consider an Excel data sheet shown below and containing fixed asset record data (highlighted area is to be analyzed):

With Excel open, also open a blank Audit Commander form, as shown below:

Then select the area in the worksheet to test (i.e. columns, ranges or specific cells) and copy it to the windows clipboard using either 1) the Excel menu item "edit|copy", 2) the keyboard shortcut "Control-C" or 3) right mouse click and select "Copy". Then click inside the Audit Commander text box form and "paste" the data either by clicking the "paste" button" or else using the keyboard shortcut "Control-V". At this point the form will display the Excel data which has been "pasted" into the form (not all of which can be seen):

Now, select the command to be processed from the drop down list and click the "OK" button. In this case we will use the default value for the command which is "Stat" - i.e. statistics. (The link "Tests" on the left side of this form describes the other tests which can be performed).

Specify the rows or columns to be tested (or blank for all). For purposes of this form the numbering for the columns and rows begins at one. So, for example, to test columns 1 and 2 the range would be specified as "C 1 2" (without the quotes - note that all the values are separated by one or more spaces). The "C" denotes that columns are to be used, an "R" indicates rows. In this test, we will test only the data in the second column, by using the range specification of "C 2". This means that all of the data in column two will be tested.

Now click on the "OK" button to process the data. This will cause the results to be written 1) on both the status bar, 2) to the "tab named "Reports" as well as 3) writing the results to the clipboard. View the results either on the status bar or in the "Report" tab. If you wish document the test results within the worksheet, they can be "pasted" onto any Excel worksheet. (This is done either by the menu option "Edit|Paste", the keyboard combination "Control-V" or else right mouse clicking in a cell and selecting "paste".

The results of the tests can be seen on the status bar, or under the "Reports" tab as shown above. Also, the results of each test are copied to the clipboard, so they may easily be "pasted" into any empty area on an Excel sheet, just by clicking on a cell and then either pressing "Control - V" or else selection the menu option "Edit|Paste". The image below shows the results when an empty area in a worksheet is selected and then the clipboard contents are pasted in by typing "Control - V" or else the menu item "Edit|Paste".

As can be seen, the contents of the report tab are written to the worksheet. For the statistics function, there are three reports produced - 1) histogram data (which classifies the data for use in preparing a histogram), 2) percentile report, and 3) the details of the population statistics. More information on the statistics function is available.

 

Suggestions/Comments/Questions?

I welcome all suggestions and comments, which can either be e-mailed to me ( Mike Blakley) or posted in the support forum. For postings to the forum use the logon id "1065732" with a password of "auditor" (without the quotes). Optionally you can register yourself and then send me an e-mail briefly explaining why you would like to join (free, but only for educational/business purposes).

I may not be able to implement your suggestion or answer your question right away, but I will try to do so as soon as possible.