How Do Pivot Tables Work? - Excel Campus
26

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.

How Pivot Tables Work - Source Data to Summary 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.

Click here to watch the screencast in your browser

Pivot Tables Turn Data Into Report GIF 640x480

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.

Sample Source Data for Pivot Table

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.

How to Insert a Pivot Table

Typically you will want to insert your pivot table on a new worksheet.

Create a Pivot Table Window

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.

How Pivot Table Works Layout Overview

The Pivot Table Areas

The pivot table contains four areas that you can drag the fields into to create a report.

  1. Filters area
  2. Columns area
  3. Rows area
  4. 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.

Excel Pivot Table Areas Diagraml

We are going to create this quarterly sales report by region as an example.

Quarterly Sales by Region Pivot Table Layout

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

RowsAreaPivotTableDiagram.png

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.

Rows Area of Pivot Table - Unique Values Listed

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

ValuesAreaPivotTableDiagram.png

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.

Values Area of Pivot Table - Automatically Sums Numeric Data

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.

Sum of Revenue for Northeast Region - Pivot Table Filter and Calculate

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:

  1. 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”.
  2. Next, the pivot table calculates the Sum of the Revenue column.

Pivot Table Filter and Calculate Values for Rows Area

That’s it!

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 Layout for Source Data of Pivot Table

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.

Pivot Table Source Data in Wrong Tabular Layout

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.

Pivot Table Source Data in Correct Tabular Layout

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

ColumnsAreaPivotTableDiagram.png

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.

Rows vs Columns Area in 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 and Calculate on the Columns Area of Pivot Table

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.

Pivot Table with Fields in Rows and Columns Areas

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?

Pivot Table Filter and Calculate Values for Rows and Columns Area

  1. First, the Qtr column is filtered for “Q2”.
  2. Then, the Region column is filtered for “South”.
  3. 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

FiltersAreaPivotTableDiagram.png

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.

Filters Area of Pivot Table

The fields in the Filters area contain drop-down menus that allow you to apply a filter to the entire pivot table.

Filter Entire Pivot Table with Filter Drop-down Menus

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.

Filter and Calculate on the Filters Area of the Pivot Table

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.

Filter and Calculate on Three Areas of the Pivot Table

  1. First, the Qtr column is filtered for “Q2”.
  2. The Year column is filtered for “2014”.
  3. The Region column is filtered for “South”.
  4. 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.

Multiple Fields in One Area of the Pivot Table

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.

Step 1 - List Unique Values in the Rows Area with One Field

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.

Step 2 - List of Unique Values with Multiple Areas in the Pivot Table

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.

Reversing Fields Still Lists Unique Values in the Pivot Table

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.

Filter and Calculate on Mulitple Fields in One Area

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.

IMPORTANT - Source Data for Pivot Table in Tabular Layout

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.

Turn Data into a Dashboard with Pivot Tables

Click Here to Get the Pivot Table Checklist and Free 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.

Click Here to Learn More About the Core Pivot Course

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! 🙂

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 26 comments
nicole - July 7, 2017

Hi!

This was super helpful!
I do have a question about source data though. If one of my tabular columns is a percentage how does that calculate into the pivot table? Does it sum or average…I can’t quite figure it out, the value I’m getting is huge.

That was a lot of questions, any help is appreciated!
Thanks so much.

Reply
kAy - June 12, 2017

Thank you for this nice post, it is very informative. I was just wondering if you could share the actual logic behind this pivoting, you know, how interchanging multiple rows and columns would actually manipulate and display the data, I mean the algorithm for this pivoting table.

Reply
Jim Maguire - March 16, 2017

Jon,
Many thanks for this. Under the heading “Unique Field Characteristics” you have shown value headings Jan Feb Mar and under each revenues for that month.
In your very first example at the top you have similarly Q1 Q2 Q3, which do the same thing except for quarters not months.
So when you say:
“For example, column D contains revenue for January. This means the column contains two characteristics: the revenue amount and the month (January).” could not exactly (almost) the same be said about your first example, with the
only difference being quarter instead of month?
I am confused by this. thanks for any help

Reply
    Jon Acampora - March 20, 2017

    Hi Jim,
    I believe the first example you are referring to (with the quarters) is already a pivot table. If the source data did have columns for quarters, then we would want to unpivot that data to get all the cells that contain values in one column. I have another article that explains how to setup the source data and unpivot in more detail. I’m also working on a full pivot tables course that explains the source data setup in a lot more detail. This is my far the most important step in creating a pivot table. It can also be the most challenging step…

    Reply
Kels - October 24, 2016

Hi Jon,

I am very very grateful for the videos. It feels like they made especially for me as I got it. I got encouraged with your assurance that pivot tables are easy. Thank you for your big heart. I am now determined to keep learning something new weekly. Please help me with steps to take. I am a beginner.

Thank you ever so much,

Kels

Reply
Amy - September 18, 2016

This was of great help, thank you for posting this info am pretty sure lots of people use it.

Thanks again.

Reply
Hao - August 22, 2016

Thank you for very good information.
Could you tell me which software you use to create GIF:PivotTablesTurnDataIntoReportGIF640x480 in the tutorial.

Reply
    Jon Acampora - August 26, 2016

    Thanks Hao! I use Camtasia for all my videos and GIF creations.

    Reply
      Hao - August 27, 2016

      Thanks you Jon. I love your answer and I love your great articles too. They are very helpful. And I usually visit your website for more tips.

      Reply
Muhammad Sameen - March 19, 2016

I deleted the main sheet still the pivot table is acquiring data, and its working in the same manner , however , i have deleted the main sheet. the main source of the data is deleted but still its not affecting the pivot table, why?

Reply
    Jon Acampora - March 21, 2016

    Hi Muhammad,
    The pivot table data is stored in a pivot cache in the background. This means you can delete the sheet that contains the source data and the pivot table will still work. Some people do this to reduce their file size, since storing the data on a sheet and the pivot cache means you are technically storing it twice.

    You can turn this setting off in the PivotTable Options menu on the Data tab, by unchecking the option “Save source data with file”. This will remove the source data from the pivot cache when you save the file. I hope that helps.

    Reply
SHAJI - February 8, 2016

Hi Jon,
Appreciate your golden heart! many thanks for taking your time out and putting a nice piece of work for a beginner like me. It is a pleasant knowledge and will certainly take away my burden in some ways.
Many thanks once again

Reply
Laura Vallejo - January 13, 2016

Muchas gracias por compartir sus valiosos conocimientos y experiencias, quisiera orientación acerca de como consolidar las tablas y gráficas dinámicas en un tablero de control (dash board), si es necesario incluir procedimientos en VB o alguna instrucción especifica para su integración.

Agradezco de antemano sus amables comentarios, saludos.

Reply
Brad Edgar - November 10, 2015

Awesome resource Jon!

I’m definitely going to be including this one in a course that I currently have in process as a resource to refer my customers to. Your stuff is always on point.

Cheers,
Brad

Reply
    Jon Acampora - November 10, 2015

    Thanks Brad! I really appreciate that! Is that a live course or online?

    Reply
      Brad Edgar - November 10, 2015

      It will be an online course. It’s a great way to leverage and train/reach more people. I’ll be sure to send you a note once it’s complete.

      Reply
Virginia - November 3, 2015

Wish I’d found you 2 years ago!!! Better a little late than never!! ☺

Reply
Happy Titus - August 22, 2015

Very Nice,

Thanks
Happy

Reply
Kevin Lehrbass - January 10, 2015

I didn’t see this post until now! It’s a great explanation for beginners. The animated gifs turn it into a very easy to follow introduction about pivot tables. I wish I had seen this 15 years ago! I will recommend this to anyone who is new to Pivots. Thanks Jon!

Reply
joe - December 28, 2014

i agree, it was a very thorough post. I also like the animated gif you had in there. Helped to explain while the reader not having to click on a video link. Really good post to cover the basics.

Reply
    Jon Acampora - December 31, 2014

    Thanks Joe! I appreciate you letting me know about the animated GIF. I’m finding that many people prefer those over watching videos.
    Have a great day! 🙂

    Reply
Jeff Weir - December 2, 2014

That’s an epic post, Jon. Great resource for newbies, and I congratulate you on it. I especially like the PivotTable Fields and PivotTable Areas diagrams you put together. Just awesome.

Reply
    Jon Acampora - December 3, 2014

    Thanks Jeff! I tried to take a page out of the Weir book of blogging for this one. 🙂 You seem to crank out monster posts like this every time you write, and I don’t know how you do it. I’m actually thinking about cutting it up into a few posts to make it a little more consumable. Anyways, I’m glad you enjoyed it. Thanks again!

    Reply

Leave a Reply:

Take Your Excel Skills & Career to the Next Level

10 Excel Pro Tips eBook

Get my eBook & FREE weekly updates to help you learn Excel.

x