Setting up a pivot table is a bit like news reporting – you can give a quick summary of the Who, What, When, Where and How (Much) of your data. After you’ve been using pivot tables for a while, it’s easy to create a new report, and drop the fields into the right locations. But, if you’re just starting out, it’s not clear what to put where. I’ve put together a short guide on how to plan a pivot table.
Thanks to Anne Walsh for suggesting today’s topic! Anne has led Excel classes for many years, and she knows that people struggle to get their pivot tables set up correctly. Anne recently published Your Excel Survival Kit, which is jam-packed with useful Excel tips, from her extensive experience.
How to Plan a Pivot Table
This blog post will cover the key steps on how to plan a pivot table, and then quickly create and modify it.
The full guide is on my Contextures website, on the How to Plan and Create a Pivot Table page. There is a sample workbook there too, that you can use for your experiments. Or, make a copy of one of your own workbooks, and use that.
1. Check Your Data
To make a useful and flexible pivot table, you need good data. So, before you start building, check the data that you’re going to use.
- Make sure that the data is in a well-organized list, with headings, no blank rows, and no blank columns.
- If possible, set up the list as a named Excel table. That will automatically change size if you add or remove rows and columns.
- Take a look at the column headings, to make sure that all the information you need is in the table. Does is have all the data on Who, What, Where, When and How Much?
2. Set a Pivot Table Goal
Next, think about what you want to show in the pivot table. It can quickly summarize hundreds or thousands of records.
For example, do you want to show a summary of
- how much of each product was sold in each region?
- how many orders there were each year?
- which salespeople had the highest sales amounts in each food category?
3. Think About the Layout
Once you know what you want to show, think about the way you’d like the report to look. If you were making a normal Excel report on the worksheet, what headings would you have across the top, and what headings would be down the left side?
To help you get a clear picture, sketch that plan with a pencil and paper, or type a rough plan on an Excel worksheet.
If you aren’t sure exactly how it should look, at least sketch out a rough idea or two.
4. Create a Quick Pivot Table
After you’ve done those preparation steps, it’s time to dive right in! If you’ve got Excel 2016 or later, use the Recommended PivotTables command, and find a pivot table layout that’s close to what you sketched out. You can add or remove fields later.
If you’re using an older version of Excel, or if none of the recommended pivot tables match what you need, use the PivotTable command. That will create a blank pivot table, and you can add fields to it.
5. Change the Pivot Table
Excel will create a new worksheet with your pivot table, and the PivotTable Field list should be showing at the right side of the screen. Add or remove check marks beside the field names, to add or remove those fields in the pivot table.
Excel will drop the fields into a default location, and you can move them later. Point to a field in the Layout Sections, and drag it to a different section.
Or, right-click on a name in the field list, then click a location command, such as Add to Report Filter.
In the screen shot below, the Region field will be at the top of the pivot table, in the Report Filter area. It will have a drop down list of regions, so you can focus on a the sales in a specific region.
6. Keep Experimenting
The best thing to do, if you want to learn about pivot tables, or get better at building them, is to keep experimenting. Just remember to work on a copy of your Excel files – don’t do your experimenting on the only copy of a crucial workbook!
Use a variety of sample data to build your pivot tables. Start with the sample workbook from my How to Plan a Pivot Table page, then build pivot tables from your own data – sales orders, employee project work hours, supply purchases, whatever you can find! That way, you’ll see some of the problems that you might run into, and find ways to work around them.
And to discover more of the amazing things that pivot tables can do, right-click on different parts of the pivot table, and experiment with the commands you find there.
Pivot Table Add-in
If you’re working with pivot tables, I’ve got a free tool that you can use — Pivot Power Free (PP Free). Use it to quickly change Sum to Count, format numbers, and a few other handy things.
Note: If you have a copy of my Pivot Power Premium (PPP) tool, you won’t need this free version – PPP has all those tools, and many more.
Excel VBA Master Course
Do your Excel programming skills need a boost? Are you wasting valuable time while you write and troubleshoot your code? You can get expert training at the Excel VBA Master Class that will be held in Amsterdam, on October 26-27. This two-day course, led by Jan Karel Pieterse and Tony de Jonker, should quickly pay for itself, in the time that you’ll save on your VBA projects.