Balanced Scorecards and Operational Dashboards with Microsoft Excel, 2nd Edition

Chapter 21

Working with PivotTables

What we see depends mainly on what we look for.

—John Lubbock

Biologist and politician

1834–1913

It’s not hard to argue that PivotTables are the most powerful feature in Excel. What is amazing is that they are also one of the least-used features. Most Excel users have heard of them but have never used them.

If you have ever needed to analyze large amounts of data, such as building a summary table of sales by month, or doing statistical analysis on large lists, or finding the top 10 in a list, you would have benefited from knowing how to use PivotTables. All Excel users beyond novice level should at least learn the concept of a PivotTable so that they know when to use this powerful tool.

The power of PivotTables can have a big impact on accounting and finance people. I used to teach a course at Sonoma State University on computers and spreadsheets for finance and accounting. About a week after going through an exercise on PivotTables, I got a call from a small company’s internal accountant, who sounded close to tears. She told me how she used to spend a day and a half at the end of each month building and rebuilding tables in Excel so that she could categorize revenue and expenses in different ways, “slice and dice,” and do some auditing. With what she had recently learned about PivotTables, she had reduced that day and a half to less than an hour of work. She could use the rest of that time in more productive ways for her company. She was ecstatic! ...