#270 Demystifying Excel Pivot Tables

For as long as pivot tables have been around, they’re probably one of Excel’s least understood, and therefore least-used features. I think most people are simply unclear how they work and often a bit intimidated by pivot tables.

If you can think of your data in terms of groups, values, and filters you can master pivot tables and harness their power to work much more efficiently. Let’s work through one simple example to illustrate.

Looking at our data, let’s consider three things: logical groupings, values, and filters.

1. Columns to group on:
Do you see any columns you may want to use for grouping? By product, By Region or By month are some logical groupings.

2. Values to be used in calculations:
Now what values would you want to do calculations on? No. of customers, Net Sales or Profit/Loss are likely candidates right?

3. Columns to filter by:
Finally, how would you like filter the data? By salesperson? By product? etc

Once you know the questions you want to ask of your data, it’s really quick to get the answers using a pivot table.

Scenario

Let’s say we want to use this data, to look at ours sales grouped by product and region with the ability to filter on each salesperson. How would you do all of this with basic formulas? Or would you give us and phone a friend for help? A pivot table can give you this result below in about 30 seconds.

Getting Started

1. Start by launching the Pivot Table & Pivot Chart Wizard from within the workbook containing your data.

2. Indicate that your data is in a “Microsoft Excel list or database” and that you want a “Pivot Table”

3. Specify the range of data you want to work with.

4. Finally tell the wizard where you want to put your new pivot table; on a new or existing worksheet.

Now you should see something similar to this:

Making the ‘Magic’ Happen

I think most people get stuck at this point. From here the trick is to drag a grouping to the row area, another grouping to the Column area, the values to the data area and, if you want, something to the page field area at the top.