Excelling at Excel - Getting started with PivotTables: Prep your data

I get two responses whenever I mention PivotTables at parties: People either love them or run screaming.

Clearly there has to be some way to get the latter group into the former. No one I talk to ever says, "Oh, I totally get how to use PivotTables but I'm just not that into them."

First, you should know what they do: PivotTables allow you to quickly analyze the data you've collected in Microsoft Excel, so you can spot the trends that shape your business and help you to maximize it.

That sounds good, right?

The biggest problems people have with using PivotTables are: a) their data isn't optimized for use as a PivotTable, and b) they don't know how the fields work to organize that data into an effective PivotTable.

Let's start with the first issue. If you're an advanced Excel user, you probably perform all kinds of fancy tricks on your Excel data. Alas, things like totaling and averaging and filtering are best left to the PivotTable. The beauty of PivotTables (and why so many people love them!) is that they can speed up this sort of slicing and dicing of data. What you need is a good raw data feed for the PivotTable to help you crunch.

For instance, this data isn't ideal for a PivotTable (click to enlarge):

First, all of the totals get in the way. Also, the monthly sales figures are broken out in a manner that makes creating a useful PivotTable a challenge. It's not much work to move the monthly data into a better format that will scale across multiple years, if needed:

See how month and year have their own columns now? All I did was add those two columns and copy/paste the matching data for the sales data and for SKU, Product, and Price. Revenue is no longer calculated as the total of all months, but as simply price times units sold. We'll do all of that totaling in our PivotTable.

Depending on how your data is pulled or compiled, you may simply need to request a different format or go back to source data from before you started to make changes to perform your analysis. Again, we can let the PivotTable do the heavy lifting there.

Now you can select all of the data you want to include (click the arrow at upper left to select all), click on the Insert tab, click the PivotTable button and select PivotTable from the dropdown list. Accept the defaults to create a new worksheet and you should generate an empty PivotTable with all your header row labels listed as fields:

With the preparations complete, now we're ready to tackle the second step: How to create a good, working PivotTable. This requires a little more depth, so get your data ready and we'll tackle PivotTable fields and areas on Monday.

Suzanne