How Do Pivot Tables Work?
Bottom line: This post explains how pivot tables work. You will learn what actually happens when you drag a field into each area of the pivot table, and why the structure of your source data is so important.
Skill level: Beginner
Pivot Tables are one of the most powerful and useful tools in Excel. They will save you a lot of time by allowing you to quickly summarize large amounts of data into a meaningful report.
Understanding exactly how a pivot table works will really help you unleash the full potential of this amazing tool. This post should help both beginners and experienced users understand the mechanics of a pivot table, and why the structure of your source data is so important.
The following screencast animations shows how quickly a summary report can be created from a table (list) of data using a pivot table.
Let’s learn how this magic happens… 🙂
The Sample Data
For this post I will be using a set of transactional sales data an example for the source of the pivot table. Each row in the data set contains sales data for a product sold to a customer.
You can download this workbook to follow along.
How Pivot Tables Work.xlsx (23.8 KB)
How to Insert a Pivot Table
The first step is to insert a pivot table into your workbook.
Typically you will want to insert your pivot table on a new worksheet.
After you create the pivot table you will see a list of fields in the task pane on the right side of the screen. These fields are the columns in your data set.
The Pivot Table Areas
The pivot table contains four areas that you can drag the fields into to create a report.
- Filters area
- Columns area
- Rows area
- Values area
The following diagram shows where each area of the pivot table is located on the report. This can be used as a guide to familiarize yourself with the different areas.
We are going to create this quarterly sales report by region as an example.
The Pivot Table Areas Explained
In the example report above I created the pivot table by dragging fields into the different areas on the Field List (task pane on right side).
Some magic happens when a field is placed in an area. That magic is different for each area of the pivot table. So I’m going to explain what happens when a field is added to each area.
Here is another link to download the sample file.
How Pivot Tables Work.xlsx (23.8 KB)
The Rows Area
The Rows area of the pivot table is where we typically start when building the outline of our report.
When you drag a field into the Rows area of the pivot table, all the unique values in that field will be displayed in the first column of the pivot. The pivot table removes all the duplicates in the field (column of source data) and only displays the unique values.
In my sample data set you can see the regions are listed more than once because we made sales in each region multiple times throughout the years.
When I place the Region field in the rows area, each region is only listed one time in the first column of the pivot table. Again, the pivot table automatically removes the duplicates and only displays the unique values.
The layout of the source data is extremely important for this to work, and I will explain more about this below.
The Values Area
The Values area displays the data (values) that we want to summarize in our pivot table report.
When you drag a field into the Values area, the pivot table will automatically sum or count the data in that field. If the data in the field contains numbers, then the sum will be calculated. If the data contains text or blanks, then the count will be calculated.
The calculation type can be changed later to other functions like Average, Max, Min, etc.
In this example I will place the Revenue field in the Values area. This field contains numbers (sales dollars) and the pivot table will automatically sum the revenue for each region that is listed in the Rows area.
Filter & Calculate – The Values Area Explained
What happens when we add a field to the Values area?
The pivot table performs its magic by filtering and calculating the data for each cell in the values area. This is a really important concept to learn. It will help you understand how pivot tables work, so let’s dig deeper.
In the image above, the pivot table shows 5,349 in cell K4. This is the Sum of Revenue for the Northeast region.
How did the pivot table calculate this number?
We can break it out in two steps:
- First, the pivot table filters the source data for the criteria in the Fields, Columns, and Rows areas. In this case we only have one field in the Rows area, so the Region column in the source data is filtered for “Northeast”.
- Next, the pivot table calculates the Sum of the Revenue column.
This process is then repeated for each cell in the values area of the pivot table. The Region column is basically filtered for each of the four regions, and the Sum of Revenue is calculated and placed in the Values area after each filter.
It’s important to note that I’m just explaining the concept of how this works. The source data table is not actually filtered on the sheet when you add a field into the Values area. However, understanding the concept of the calculation will also help you understand why the structure of the source data is so important.
Tabular Data Structure – Getting the Source Data Right
Since we now know that a pivot table uses filters to calculate the results in the Values area, it is critical that the data is structured in a way that can be filtered.
Pivot tables require your source data to be in a Tabular layout (format).
Tabular means that the source data can only have one row of headers (descriptive names for each column), and rows of data below the header.
However, there is more to it…
Unique Field Characteristics
Each header (column name) in the source data must only describe ONE characteristic of the data.
Let’s look at an example to help explain this. The following image shows data that could be considered tabular because it is a table of data with one row of headers.
However, this is NOT the best layout for source data of a pivot table because the column headers describe more than ONE characteristic of the data in the column.
For example, column D contains revenue for January. This means the column contains two characteristics: the revenue amount and the month (January).
When the source data is structured like this, it makes it impossible for the pivot table to calculate the total revenue for a region. The revenue is divided up into columns by month and you cannot calculate the sum of one column to get total revenue.
Instead, the pivot table requires that there only be one column for revenue. This will allow you to create a report that displays total revenue by month, quarter, product, region, or whatever way you want.
Therefore, each column should only contain one characteristic that describes the data field (Revenue). To fix this we would need to create separate Revenue and Month columns. The data table should then look like the following.
This is called unpivoting the data. It means the number of rows will grow, and the table will get much longer. But this is ok!
Remember that the job of the pivot table is to Filter and Calculate. It can only filter columns in a vertical manner, so all the revenue numbers need to be in a single column.
This gives you a lot more flexibility when you add the Revenue field to the Values area of the pivot. You will be able to quickly place the Months field in the Rows, Columns, or Filters area to see your numbers in different ways.
With this understanding of how the data should be formatted, you will begin to see the power of the pivot table.
I have a full article that goes into more detail on How to Structure Your Source Data for a Pivot Table.
The Columns Area
Now that we have an understanding of how the pivot table filters and calculates data, let’s see what happens when we add more fields to the report.
The Columns area works just like the Rows area. It lists the unique values of a field in the pivot table. The only difference is that it lists the values across the top row of the pivot table.
The image above shows what the pivot table will look like if you put the Region field in the Columns area versus the Rows area.
When the Region is in the Columns area, the values (Sum of Revenue) are placed horizontally on the sheet. The Sum of Revenue calculation works the same as before. The pivot table must Filter and Calculate the Region field in the same way.
Filter & Calculate on Multiple Fields
Let’s add another field to our pivot table and see how it works.
In the following example I have the Region field in the Rows area, and the Quarter (Qtr) field in the Columns area. The values area is calculating the Sum of Revenue. This is starting to look like a more useful report.
The pivot table calculates the Sum of Revenue the exact same way as before, by filtering and calculating. Now it just has two fields to filter for when it calculates a cell in the Values area.
In the image below the Total Revenue for the South region for Q2 is 1,772. How is this calculated?
- First, the Qtr column is filtered for “Q2”.
- Then, the Region column is filtered for “South”.
- The Revenue column is then summed to get 1,772.
As you add more fields to the Rows and Columns areas, the pivot table just filters on more fields, then calculates. Although the report may get complex looking, the calculation process is actually a very simple two step process; filter then calculate.
The Filters Area – Filter the Entire Pivot Table
The Filters area applies a filter to the entire pivot table.
In the example below I placed the Year field in the Filters area. This does NOT make any immediate changes to the pivot table. Instead, it gives me the option to apply a filter to the entire pivot table.
The fields in the Filters area contain drop-down menus that allow you to apply a filter to the entire pivot table.
When I select 2014 from the Year filter, the entire pivot table is also filtered for only the rows in the source data that contain “2014” in the Year column.
As you can see, our filter and calculate methodology still applies here.
When calculating a cell in the Values area, three filters are being applied to the source data to calculate the Sum of Revenue. The following image shows how cell L79 is calculated in the pivot table.
- First, the Qtr column is filtered for “Q2”.
- The Year column is filtered for “2014”.
- The Region column is filtered for “South”.
- The Revenue column is then summed to get 1,702.
Each cell in the Values area represents an intersection of the fields in the Rows, Columns, and Filter areas. The source data is filtered based on this criteria, then calculates the Values area for the specified type of calculation (sum, count, average, etc.).
Bonus – Add Multiple Fields to One Area
What happens when we add more than one field to an area?
Well, we can take everything we just learned and apply it here. The pivot table works in the same basic way.
In the image above I put the Year and Qtr fields in the Rows area of the pivot table. You can see how this instantly gives us a different report using the exact same source data. This flexibility is the beauty and power of the pivot table.
Let’s take a step back and see what happens when I add each field to the Rows area.
First, I add the Year field to the Rows area. As we learned before, the pivot table will only list the unique values (removes duplicates) in the Rows area. Even though there are a lot of rows in the source data that contain 2013 and 2014, the values (2013,2014) are only listed once in the pivot table.
Next, I add the Qtr field to the Rows area below the Year field. This time the unique values for the Qtr (Q1,Q2,Q3,Q4) are listed under each unique value in the Year field (2013,2014).
The list of unique values in the child field (Qtr) repeats for each unique value in the parent field (Year). This continues to happen as you add more fields to the Rows area.
The report above displays our quarterly sales trend for each year. It nests each quarter under each year in the Rows area, and makes for a very useful report.
Now let’s make a quick change to the report…
In the following image I reversed the fields in the Rows area of the pivot. Now Qtr is on top and Year is below it. The same principal still applies. The unique values for each Year are listed below the unique values of each Qtr.
This gives our report a totally different look, but it is still very useful. This report allows us to compare the year-over-year sales for each quarter.
The same thing happens as you add multiple fields to the Columns area. I won’t go into details, but give it a try and see what happens when you move Year and Qtr to the Columns area.
Filter and Calculate on Multiple Fields in One Area
Now let’s take a look at how that year-over-year report works to calculate the Values area.
When multiple fields are added to one area, the Values area of the pivot table still works the same way using the filter and calculate principal.
The source data will be filtered for all the filter criteria, then the Sum of Revenue is calculated. This happens for each cell in the Values area of the pivot table.
It doesn’t matter how many fields you have in each of the Filters, Rows, or Columns areas of the pivot. The Values area will still be filtered and calculated in the same way!
Filter and Calculate the RIGHT Tabular Data
I hope you have learned how pivot tables work by filtering and calculating your data. But the most important part is how the data is structured in a tabular format. Each field must have one unique characteristic that describes the data. The proper layout of the source data will really help you conceptualize your pivot table reports.
This will also help you obtain your data in the proper format. Often times we are at the mercy of our IT department to provide us with data. Requesting your data in the correct tabular structure will allow you to create just about any report you can imagine with a pivot table.
As you learn the power of pivot tables, your reports will naturally get more advanced. You will be able to quickly mine and analyze your data in new ways, helping you discover trends that can make a big impact for your organization.
Want to Learn More?
I have only scratched the surface of what pivot tables can do. There is a seemingly infinite number of ways you can use pivot tables and pivot charts to answer business questions, and impress your boss. 🙂
With new tools like PowerPivot, there is no question that pivot tables are the way of the future for analyzing and presenting data. Understanding the concepts of how pivot tables work will really prepare you for any reporting tool outside of Excel.
My recommendation is to: Learn > Practice > Repeat
There are a ton of great ways to learn pivot tables depending on what type of learning environment suites you best.
If you really want to immerse yourself and learn quickly, I recommend taking an online course. Here are two great resources.
Resource #1 – Free Video Series on Pivot Tables & Dashboards
I have put together a free 3-part video training series on pivot tables and dashboards. You can also download my pivot table checklist and all the practice Excel files that go with the videos.
Resource #2 – Core Pivot Online Course
A friend of mine, Dave Bruns from ExcelJet.net, has a great online course called Core Pivot. This course is a great way to get started with pivot tables. It contains over 2 hours of instruction that is broken up into short 2-3 minute videos. Learning pivot tables will really save you a lot of time, and help you get the most out of Excel’s most powerful tool.
Still Have Questions?
Learning pivot tables takes time. With practice you will gain a better understanding of how pivot tables work, and how they can benefit you.
Please leave a comment below with any questions or suggestions. Thank you! 🙂