Pivot tables are among the most powerful features of Microsoft Excel, yet many people are either unaware of them or intimidated by them. If you’re in either of those camps, this post will introduce you to pivot tables and walk you through the steps for creating one.
What is a pivot table?
A pivot table is a reporting tool that lets you sort, summarize, analyze, and present data in multiple ways while leaving your original data unchanged. Pivot tables work independently of your original rows and columns, thereby allowing you to create different views and reports without altering the original data table.
The benefits are many, including:
- The ability to generate and extract meaningful information from large tables within minutes.
- Time savings and efficiency. Pivot tables can be created in minimal clicks and you don’t need to write a macro or track down a programmer.
- Low memory usage. Pivot tables won’t slow down your PC.
- Automatic updating. Because pivot tables reference your original data, they stay in synch with updates and changes.
So let’s create a simple pivot table. (You’re welcome to download the template and follow along, or grab it at the end of this post.)
But before we do …
Ensure your data is organized properly
Before you can create a pivot table, it’s essential your data is organized in a way Excel can understand. Otherwise you’ll be embarking on a frustrating, futile and fruitless journey.
- Organize your data in rows and columns, with each row containing information about one record, such as a sales order or inventory transaction.
- Use headings. The first row must have headings for each column, and each heading should be unique.
- Keep data types together. Each column should contain the same type of data, such as “text” or “currency” or “percentage” or “date”. Do not mix these in a single column.
- No blank rows or columns. There can be blank cells (though keep that at a minimum), but do not have any completely blank rows or columns in the table.
- Keep data isolated. Leave some distance between the dataset you’re using for the pivot table and any other data on the worksheet. To do this, keep a buffer zone around your target data using one or more blank rows and columns to ensure it’s isolated.
Create your pivot table
For this example, I’m using Excel 2010 (for Mac users, this is synonymous with Excel 2011) because it’s still the predominant version used by most small and mid-sized businesses. Good news: my colleagues tested it with Excel 2007 (PC/Mac) and the steps are nearly identical. Eggggsellent.
Here’s the data and format we’re starting with:
As you can see, we’ve got salespeople (there are actually 4 of them in the data table: Samantha, Lou, Maria, and Matt) who are selling 3 different products across 3 regions. Their sales are being calculated monthly.
Here we go.
Select the data
1. Highlight the data – columns and rows – that you want to use. Be sure to include the header row.
2. From the Excel ribbon, click the Insert tab.
3. In the Tables group, click PivotTable.
5. Select New Worksheet or Existing Worksheet as the location of your pivot table, and then click OK.
Add fields to the pivot table, and begin creating reports
After completing step 5, you’ll be faced with an empty pivot table that lives on the existing sheet or a new sheet, depending on the option you chose. (See image below.)
When you select any cell within the pivot table (cell A3 in this example), a PivotTable Field List will appear to the right. It has two sections: A top “check box” list and a bottom 4-square area. Notice that the top section’s list of options will match the original data table header row names.
(Note that I’ve dramatically tightened the Excel window to ensure all the important bits are in the image. Yours will likely not be this squished.)
6. To build the pivot table, first choose fields from the top section. For each option (criteria) you choose, it will be automatically placed into a cell in the bottom section. Here’s an example of what it looks like when I choose 4 options:
7. And here’s what the resulting pivot report looks like:
Here’s another example using the same pivot table but choosing different options. In this example, I want to drill down and find out which products each salesperson is selling in each region … and I want to filter the report by month.
To do this, I choose my criteria from the top section, and then I drag “Month” and “Product” to different sections within the 4-square area.
Here’s what the PivotTable Field List looks like:
And here is the resulting pivot report:
Use filters to hone your reports
If you’re familiar with using the Excel filter feature, this will be very intuitive. But if you’re not, I want you to notice the little white boxes with upside-down triangles in them. They look like this:
When you click on them, a new menu will open that allows you to further filter your results. In this example, you can choose/unchoose the months you want to see in your report.
And remember, all of this reporting and manipulation does not change the original data or table structure. So go ahead and make mistakes, experiment, and get your arms around some great (and not so great) reports. Your master data will not be harmed.
Power to the pivot
Once you start using pivot tables, I guarantee you will be hooked and wonder how you ever did your job (or your volunteer work or your hobbies or your finances or your …) without them.
True, there are some very complex, interactive pivot tables, but they don’t have to be. Simple tables (like the one used in this post) can be tremendously effective at conveying useful and insightful information. It’s a good bet that you have oodles of data right now at your disposal just waiting to be converted and experimented with.
It will take some trial and error, but getting comfortable and functional with pivot tables is a relatively quick process.
Download a pivot table workbook and start practicing
Do a quick search and you’ll find multitudes of pivot table templates and tutorials. But if you’d like to download the workbook used to create the above examples, you can download it here. Feel free to play with it, modify it to fit your needs, and experiment with it.