Auditor Struggles, Part 2

This is Part 2 of a Case File series that describes how real auditors tried to apply questionable methods to auditing and data profiling. See Part I.

I picked one of the fields and said, “Please show me how you profiled the Status field, for example.”

The auditor proudly projected his Excel spreadsheet on the conference room screen. He said, “I filtered the Status field to display only records containing ‘Complete’, noted the number of filtered records in the lower left corner, and recorded the value and the number of records in the document.”

“Then I filtered the same column for the other values, one by one, and recorded them likewise,” he said, as he looked back at me. He realized I wasn’t smiling.

I then said, “So each of these fields had at least 5 different values, and some have even more. But at a minimum you filtered this document at least 100 times (20 fields x 5 values each).”

“Yes,” he confirmed.

“And that didn’t take much time?” I asked again.

“No”, he said.

I showed him how to create a pivot table using only the Status field column in just a few clicks (if you don’t know how to do this, see way below).

The auditor squirmed, but just couldn’t let it go. “But that means I’d have to create a pivot table for each field I profile!” he said.

“That’s true,” I said. “And that’s more work than doing 5 filters and typing it all out in your workpaper?” I countered. “One pivot table provides the counts for all 5 values,” I continued. “Capture a screenshot of the pivot table and drop it into the document. No typing, no getting the numbers wrong.”

Silence. I looked at the other 2 auditors. They were taking notes on how to do the pivot table. I shook my head.

But there was more….

See Part 3.


To create a 1-field pivot table:

  1. Highlight the column to be pivoted (in this case, the Status column).
  2. In the Excel ribbon, click INSERT, PivotTable.
  3. Click OK to save the PivotTable in a new sheet.
  4. Drag & drop the Status field in the upper right corner to the Rows area, and then the Values area (see red box below). The Status field in the Values area automatically changes to Count of Status (the default).
  5. Bonus tip: Right-click in the green box, and click Sort, Sort Largest to Smallest.


Filed under Audit, Case Files, Data Analytics, Excel

3 responses to “Auditor Struggles, Part 2

  1. Pingback: Auditor Struggles, Part 1 | ITauditSecurity

  2. John Jenkins

    From the choir;
    Are you aware of any practice data sets for audit analytics? i.e. MS has Fabrikum and Northwind. I’ve tried building out a fake payroll data set in order to build a couple of simple examples for an industry analytics work group. But if there were something that pre-existed (and much larger) it might be worth baking in some fraud/error examples to show how analytics help find them.
    Anyway this is something I have been looking for. When asked how do I do this? I am happy to share but I don’t have generic or even test data to use.
    You definitely have a community and perhaps if there were a place to see data and straight up examples. The choir might grow.
    I have considered starting a github for audit analytics but honestly don’t have the talent.
    Regardless thanks for this blog.


    • If you have ACL, they have sample data as part of the install. I’m sure IDEA does also. However, these are not huge datasets.

      Lots of free datasets are available all over the ‘net, especially govt data. I’d search for ‘free payroll data’. I have some links somewhere and will post when I find them.

      Anyone else have ideas?


Leave a Comment

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.