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.

 

Comments

  1. Do you mind if I quote a couple of your articles as long as I provide credit and sources back to your site?
    My blog site is in the exact same area of interest as yours
    and my users would truly benefit from some of the information you provide here.

    Please let me know if this okay with you. Regards!

    • Cindy Shamel says:

      Information Update readers are always welcome to forward the newsletter to interested friends and colleagues. You may also reference it in your own writing with a link to the source. We prefer, however, that you not reproduce an entire story on your blog. Thank you for your interest in the Information Update.

Speak Your Mind

*