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:
- Highlight the column to be pivoted (in this case, the Status column).
- In the Excel ribbon, click INSERT, PivotTable.
- Click OK to save the PivotTable in a new sheet.
- 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).
- Bonus tip: Right-click in the green box, and click Sort, Sort Largest to Smallest.