Why, When and How to Create Excel PivotTables

Excel PivotTables are one of those tools that users either seem to love or HATE. There is very little opinion in the middle! If you scratch the surface, however, you quickly see that the haters (very reasonably) base their dislike on the difficulty of creating them, and on not finding them useful when they do. The PivotTable likers, by contrast, rave about how easily it analyzes otherwise difficult-to-manage data.

To help you be a liker and not a hater, the first steps in creating a PivotTable must be:

• Recognizing when one will actually be useful, and
• Knowing why your data might or might not work well in a PivotTable.

Why Create a PivotTable?

PivotTables are designed to help you quickly analyze large sets of data. They convert detailed, raw records into useful, summarized groupings. Here is an example:

Imagine making sense of this 1000 row sheet of individual transaction data:

• Do you want to know what expenses are costing you the most?
• Do you want to know how much IT is spending?
• Do you want to know how much each department is spending in each category?

Not only are there a lot of records, there are a lot of questions you might ask of them.

A regular Table can allow you to sort and filter:

But this can still mean many rows that you then need to add together and it still doesn’t help you compare totals across expense categories. You could also Group and Subtotal, but this is a cumbersome process, not one you’d want to repeat for each kind of summary you might want to see (Department, Expense Category, Account, etc.)

You might want a PivotTable Because: You have many rows of detailed data that you want to summarize in several different ways.

When to Create a PivotTable

A “lot of data” by itself doesn’t automatically mean a PivotTable is the correct tool for your analysis job. You also need data that is structured in a way that makes PivotTables useful.

Here are the data characteristics that will work well for PivotTables:

• Tabular Format – Data should be organized in a table format, with rows representing individual records and columns representing different fields.
• Categorical Data – Data that includes categorical information works well with PivotTables. A single list of prices won’t lend itself to a PivotTable analysis if you don’t have additional information that groups each price into a larger bucket – such as prices by manufacturer, for example.
• Needs Column Headers
• Consistent Data Types – Each column should contain data of the same type
• Sufficient Volume – While PivotTables can handle small datasets, it’s primary benefit is in working with large datasets where manual analysis would be time-consuming and prone to errors.

It is likely that PivotTable “haters” are starting with data that is either formatted incorrectly, or are trying to use it to answer questions that the data is not suited to answer. Let’s Look at an example:

We have the same data as above, but without any categorical data. We know when purchases were made, how much they cost, the vendor and whether it’s reimbursable, but this data doesn’t tell us anything about what kind of expense it is, or which department make the purchase. The vendor name can help get a sense of this, but in this database, every line has a unique vendor.

If you still have the same questions:

• What expenses are costing you the most?
• How much IT is spending?
• How much is each department is spending in each category?

You can only answer the last question in a PivotTable with this data. And you could still analyze spending by date, but your “answers” are limited. If you are faced with this lack of usable categorization, you can either rightly stay away from PivotTables….or fix your data!

Use a PivotTable When: You have many rows of detailed, categorized data that you want to summarize in several different ways.

How to Create a PivotTable (From Scratch)

Once you’ve determined that a PivotTable is the correct solution for you, and that your data is formatted correctly, your next step is to:

Ask the question you want answered.

Not what you were expecting?

The other (big) reason that PivotTables can be so frustrating is because it can be difficult to figure out which fields to add to which PivotTable areas. Having a question to answer at this step can make it go more smoothly! Let’s use the data and the questions from the beginning of the article:

Create a PivotTable

1. Select the data range that you want to be included in the PivotTable. If your data is already a (normal) Table, select the top left cell
2. Click Insert > PivotTable
3. In the dialog, choose your table range or table name (if you haven’t already) and where you want the PivotTable to go
4. Click OK

Now you have your (panic inducing) blank PivotTable Fields pane.

Ask your question: What expenses are costing me the most?

Pull out the important words: Expenses, Cost

So, let’s click and drag the Expense Category field in the Rows area and the Amount (cost) field in the Values area, because that is what we want aggregated/summed into a single number.

One more: How much is each department spending on expenses in each category?

We’ll put Department in the Rows area, Expense Category in the Columns area and Amount (spending) in the Values area, because that’s still what we want aggregated/summed up:

That gave us a long, wide table. We can easily swap the fields in the rows and columns for the same data in a more pleasing format:

Same fields, different layout with the drag of the mouse.

Once you can answer simple questions, you’ll have the foundation to answer more complex ones. Such as adding month data to our PivotTable:

If you are a PivotTable hater, give it another try. You might just change your mind!