Analyzing Data Using Pivot Tables

Imagine an Excel spreadsheet with thousands of rows of data and columns falling past X, Y, Z into the AA, AB, AC range.  Strategic information lies in there somewhere, but how do you mine this spreadsheet to pull it out?

Pivot tables offer an elegant solution.  Excel pivot tables help you organize and sort data to create customizable tables.  A pivot table can group and summarize huge amounts of information, making it “explorable.”

In a recent project the client provided a spreadsheet containing over 160,000 cells of data with each row recording a purchasing transaction from somewhere within this global corporation.  Instincts told me that this spreadsheet offered a gold mine of insights for the project at hand, if only I could dig them out.

Where to begin?

One call to a most accomplished Excel guru revealed the wonders of the pivot table.  Ten minutes on the phone, with the aid of a screen-sharing, and I was off and running.  In short order I could see what kind of supplies were being purchased as well as which departments and individuals were doing the most buying.  The analysis revealed a fair amount of duplications and opportunity for cost savings.  I don’t see how I could have uncovered these patterns and practices without using pivot tables.

For a seven minute demo on creating and using pivot tables, check out this video from Michael McDonald, author of Excel 2007: The Missing Manual.

For a quick look at a wide variety of pivot tables search ‘pivot table’ in Bing’s image search.