Discover Pivot Tables – Excel’s most powerful feature and also least known
This article explains the basics of excel pivot tables, I have included vba code for the most common actions.
What is an excel pivot table?
A pivot table allows you to summarize huge amounts of values amazingly fast in groups and sub-groups you specify. You can then analyze the data with ease, compare values by date or by group and see important trends. It is one of the best and most powerful excel feature and also one of the least known.
Pivot table charts is a great tool for visualizing your data.
Slicers allows you to quickly filter data, however the report filter has the same functionality but perhaps not as elegant.
The following article shows you how to analyze pivot table data:
Analyze trends using pivot tables
Table of Contents Introduction to pivot tables Create pivot table Group data Analyze data (pivot table) Compare performance, year to […]
Analyze trends using pivot tables
Prepare your source data
Before you build your first pivot table make sure your data source table follows some simple rules.
- Use unique header names for all your columns.
- Make sure you have no blank cells.
- Check your spelling. If you have one cell "West" and another "Westt" they will both show up in the pivot table. You can correct this later.
- Convert your data source to an excel defined table (optional).
Rearrange values
This picture below shows you a table with bad data structure, you can't use it in a pivot table.
The table below is much better, all values in this table are not shown for obvious reasons. A couple of things are missing though, can you see it? Unique table header names and the data table is not an excel defined table.
You don't have to use an excel defined table but it will make it a lot easier if you add more values later on to your table. An excel defined table is dynamic and it will save you time not needing to adjust the pivot table source range.
I have made a macro/udf that can help you rearrange your data, see these posts:
To be able to use a Pivot Table the source data you have must be arranged in way that a […]
Prepare data for Pivot Table – How to split concatenated values?
This article demonstrates a macro that allows you to rearrange and distribute concatenated values across multiple rows in order to […]
Prepare data for Pivot Table – How to split concatenated values?
Use an excel defined table as a data source
Why do you want to use an Excel Table as a data source to your pivot table? You don't have to but it will make your life easier whenever you want to add or delete records to your data set.
The Excel table adjusts automatically to new data and this saves you time since you don't need to update the data source cell reference each time.
Here is how you convert a generic data set to an excel defined table:
- Select a cell in your data table
- Go to tab "Insert" on the ribbon
- Click "Table" button
You can also use these shortcut keys: CTRL + T
This dialog box shows up.
Excel finds the entire data table automatically =$A$1:$C$48, do make sure this is correct.
My table does not have table headers so I don't select "My table has headers" check box.
Click OK button.
Excel has now converted your data table to an excel defined table. It has also inserted new column headers, see picture above. I don't want to use these table header names as they are not descriptive of each column.
This table is much better, later on these table header names will make it much easier for us when working with the pivot table.
Learn how to change data source with a drop down list:
Change PivotTable data source using a drop-down list
In this article, I am going to show you how to quickly change Pivot Table data source using a drop-down […]
Change PivotTable data source using a drop-down list
Learn more about excel tables:
Become more productive – Learn Excel Defined Tables
An Excel table allows you to easily sort, filter and sum values in a data set where values are related.
Become more productive – Learn Excel Defined Tables
How to build a pivot table
- Select a cell in your data table
- Go to tab "Insert" on the ribbon
- Click "Pivot table" button
This dialog box appears.
It uses the excel defined table name in the first field, very good. It also allows you to choose where you want your new pivot table. I am going to place the pivot table on a new sheet. Click OK button.
Excel creates a blank pivot table for us on a new worksheet, see picture above.
This is what the macro recorder returns while inserting a pivot table.
Sheets.Add ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ "Source data!R1C1:R365C7", Version:=6).CreatePivotTable TableDestination:= _ "Sheet17!R3C1", TableName:="PivotTable7", DefaultVersion:=6 Sheets("Sheet17").Select Cells(3, 1).Select
The following aticle shows you how to refresh a pivot table automatically:
In a previous post: How to create a dynamic pivot table and refresh automatically I demonstrated how to refresh a pivot […]
Configuring a pivot table
The pivot table on the worksheet is blank and it tells us "To build a report, choose fields from the PivotTable Field List".
You can find our fields in the blue box named 1, see picture below. The fields are Region, Date and Amount the same as your header names in your data source table, now you understand why it is important to name your data source headers.
The way this works is that you can click and hold with left mouse button on one of the fields and then drag it to an area. The areas are Filters, Columns, Rows and Values and I have drawn a blue box around them with the name 2, see above picture.
Drag Region to Filters, Date to Rows and Amount to Values, see picture below. Excel is trying to help me out here, it automatically grouped my dates into months se column "Row Labels". Don't worry, I will show you later on how to group and ungroup dates.
This is what the macro recorder returns when I drag Region to Filters area.
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Region") .Orientation = xlPageField .Position = 1 End With
Here is the output when I drag Region to Columns area.
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Region") .Orientation = xlColumnField .Position = 1 End With
This happens while recording Date to Rows area.
With ActiveSheet.PivotTables("PivotTable3").PivotFields("Date") .Orientation = xlRowField .Position = 1 End With ActiveSheet.PivotTables("PivotTable3").PivotFields("Date").AutoGroup
Finally Amount to Values area.
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _ "PivotTable3").PivotFields("Amount"), "Sum of Amount", xlSum
Learn how to use hyperlinks in a pivot table:
Use hyperlinks in a pivot table
Sean asks: Basically, when I do a refresh of the data in the "pivotdata" worksheet, I need it to recognise […]
Use hyperlinks in a pivot table
Report Filters
The report filter allows you to select a subset of your data. In this case I want to work with values in region "West". Simply click the arrow and select a Region. Click OK. See animated picture below.
If you are interested in the vba code for this action here it is.
ActiveSheet.PivotTables("PivotTable3").PivotFields("Region").ClearAllFilters ActiveSheet.PivotTables("PivotTable3").PivotFields("Region").CurrentPage = _ "West"
Column Labels
Click and drag Region to Columns area.
Unique values from column Region in your source data table shows up horizontally on the pivot table (cell range C4:E4, pic above).
Row Labels
The following picture shows Region in Rows area and Date in Filters area.
Values
The last area is Values. You are not limited to numbers (Amount) here, you can drag Dates or Region here also. Notice that you can't sum text values but you can count them.
Learn how to link a combo box with a pivot table:
Populate a combobox with values from a pivot table [VBA]
In this post I am going to demonstrate two things: How to populate a combobox based on column headers from […]
Populate a combobox with values from a pivot table [VBA]
Pivot table features
Here is a list of the most used functionalities.
Summarize and analyze
The pivot table is phenomenal at processing huge amounts of data very quickly. Since calculations are done in almost no time you can easily drill down in every detail very quickly.
I have shown you earlier in this article that if you drag a field to Values area, the pivot table sums your values into groups depending on what specific fields you have in the Columns and Rows area.
It also allows you to see trends in your numbers, follow this example. Drag "Amount" once again to Values area, you should now have two "Amount" there.
Left click on the down pointing arrow next to the second "Amount" and then click on "Value Field Settings...". Click on tab "Show Values As"
Select Date in Base field: and (previous) in Base item:, see picture above. Click OK
There is now a new column next to the sums. It shows the % difference compared to the previous values. The number in cell B8 compared to the number in cell B7 is -4.86% less. (8410/8840) -1 = -4.86%
Learn to build a pivot table calendar:
This article demonstrates how to build a calendar in Excel. The calendar is created as a Pivot Table which makes […]
Count
The following picture shows Region in both Values area and Rows area. Date in Columns area grouped by month.
Unique distinct list
You can use the a pivot table to extract a unique distinct list from a column in a large list. The picture shows countries, simply drag Country to Rows area.
Extract unique distinct records
Here is a table with many duplicate records.
- Select a cell in the table above.
- Go to tab "Insert" and click "Pivot table" button
- Place the pivot table somewhere on your worksheet/workbook.
- Drag "Name", "City" and "State" to "Row Labels" field
- Also drag "Name" to "Values" field
- Values are not on the same row which is confusing. Go to tab "Design" on the ribbon, click "Report Layout" button and then "Show in tabular form"
- To show all values, go to tab "Design" and click "Report Layout". Then click "Repeat All Item Labels"
- The last thing to do is to hide "Subtotals", click "Do Not Show Subtotals" button.
Here is a picture of the final table, it shows you unique distinct records. In other words, duplicate records are removed from the source data table.
It is possible to extract unique distinct records using a formula, read this post:
Filter unique distinct records
Table of contents Filter unique distinct row records Filter unique distinct row records but not blanks Filter unique distinct row […]
Filter unique distinct records
Count unique distinct values
Excel 2013 and later versions allows you to count unique distinct values. The following picture shows you data table, the scenario is this:
How many unique distinct products did Jennifer sell in region "South" and in January 2011?
I have highlighted the values below that match above criteria.
For this to work you need to enable a check box before creating a new pivot table.
Click OK button. To be able to filter month January and not individual dates only, drag "Date" to Rows area.
There are now two "Date" fields in Rows area, the other is "Date (Month)". Drag "Date (Month)" from Rows area to Filters area and drag "Date" back to top area. It now looks like this:
Drag "Region" and "Sales person" to Filters area. Drag "Product" to Rows and Values area.
Click on "Count of Products" then click on "Value Field Settings..."
Select "Distinct Count" and click "OK" button
Now let's filter the pivot table, click on filter "arrow" next to All and select "January", "South" and "Jennifer". Click OK button.
Here is the final pivot table:
There are two unique distinct products shown next to "Grand Total".
Related article:
Count unique distinct values in an Excel Pivot Table
ExcelBeginner asks: I have a small problem that I am not sure on how to solve. I now have a […]
Count unique distinct values in an Excel Pivot Table
Sort data
It is possible to sort almost anything with data in a pivot table. This picture shows a pivot table and I want to sort column East from smallest to largest. Right click on a cell in a column you want to sort. Click on "Sort" and then sort Smallest to Largest.
Macro creates links to all sheets, tables, pivot tables and named ranges
This article demonstrates a macro that automatically populates a worksheet with a Table of Contents, it contains hyperlinks to worksheets, […]
Macro creates links to all sheets, tables, pivot tables and named ranges
Group data
A pivot table allows you to group dates. You can group dates (and time) by seconds, minutes, hours, days, months, quarters and years.
Right click on a column or row you want to group, then click group and this dialog box appears.
Below Group is Ungroup and I don't think I have to explain that.
Disable autofit column widths for Pivot table
I read this interesting article Quick Trick: Resizing column widths in pivot tables on the Microsoft Excel blog. It is […]
Disable autofit column widths for Pivot table
Multiple levels
You can rearrange the pivot table so it has multiple levels on Rows area or Columns area or both. Drag more than one field to an area to create multiple levels.
The picture shows you data grouped by quarter and then by region. You can also drag fields in an area to sort them, the order is important.
The Date and Region field switched places.
See data behind a pivot table cell
To see the data behind a pivot table cell just double click on a cell that interests you and excel creates a new sheet with the corresponding data shown.
Row and column grand totals
If you don't need the grand totals, turn them off by going to tab "Design", click "Grand Totals" button and pick a setting you want. The choices are:
- Off for Rows and Columns
- On for Rows and Columns
- On for Rows only
- On for Columns only
Insert a pivot chart
A pivot chart helps you visualize data.
- Make sure you have a cell selected on your pivot table
- Go to tab "Analyze" on the ribbon
- Click "Pivot chart" button
From here you can pick a variety of charts, the preview helps you in your decision.
The chart will change if you apply a filter or sort pivot table.
Slicers
Excel 2010 and later excel versions lets you insert slicers, they are no different than the Report Filter except that they look different and take up more space on your worksheet.
Go to tab "Insert" and click "Slicers" button.
From here you can pick a field, I will select region and then press OK button. The slicer appears on your worksheet, you may need a new location for it.
Refresh a pivot table
If you add, remove or edit values in your source data table you must update the pivot table to reflect the changes made, every time. This is easy to forget, here is how to do it.
Right click on a pivot table cell.
Click "Refresh".
You can automate this with a macro.
Private Sub Worksheet_Activate() Sheets("Pivot table").PivotTables("PivotTable1").RefreshTable End Sub
Put it in your worksheet code module, see this post for more details.
Copy a pivot table without
If you want to share your pivot table but not the source data, follow these steps.
- Select the pivot table you want to copy
- Press Ctrl +C or press copy button on tab "Home" on the ribbon
- Right click on a cell where you want to paste the pivot table
- Click on "Paste Special..."
- Select "Values"
- Click OK button
- Repeat step 3 and 4 and then select "Formats"
- Click OK button
You can tell that the copy has no link to source data by first selecting a single cell in the copied pivot table and then a single cell in the original pivot table. The are two more tabs (Analyze and Design) on the ribbon if the original pivot table is selected
Customize the layout
You can easily change the pivot table layout with one of the pivot table styles or create a entirely new one.
Select a cell in the pivot table. Go to tab design. Click on a style and the pivot table is instantly changed. You can hover with mouse pointer over different styles and see the changes to your pivot table change before you make up your mind.
What happens if I record a macro while changing pivot table style?
Sub Macro2() ActiveSheet.PivotTables("PivotTable1").TableStyle2 = "PivotStyleLight14" End Sub
To create a new pivot table style, go to tab Design. Click the arrow in the lower right corner of pivottable styles window, see picture below.
Select a table element and click "Format" button.
Change formatting and click OK button.
Repeat with the remaining table elements you want to change.
Consolidate data from multiple cell ranges
Excel has a feature that lets you consolidate data from multiple pivot tables or cell ranges.
Here are my two pivot tables for 2015 and 2014. I want to consolidate both pivot tables into one, notice that they share the same structure
Pivot table 1 - 2015
Pivot table 2 - 2014
Here is how to do it.
- Press Alt + D + P (This opens the pivot table and pivot chart wizard)
- Click on "Multiple consolidation ranges"
- Click Next
- Select "Create a single page field for me"
- Click Next button
- Select the first worksheet range you want to consolidate
Notice that I select the row and column labels as well but not the grand totals. - Click Add button
- Repeat step 6 and 7 with your remaining ranges.
- Click Next button
- Select if you want to create a new pivot table on an existing sheet or a new worksheet
- Click Finish
- Left click on arrow next to Count of Value
- Click "Value Field Settings..."
- Click "Sum"
- Click OK
In a previous post: How to create a dynamic pivot table and refresh automatically I demonstrated how to refresh a pivot […]
Use hyperlinks in a pivot table
Sean asks: Basically, when I do a refresh of the data in the "pivotdata" worksheet, I need it to recognise […]
Change PivotTable data source using a drop-down list
In this article, I am going to show you how to quickly change Pivot Table data source using a drop-down […]
How to create a dynamic pivot table and refresh automatically
This article shows you how to refresh a pivot table automatically using a small VBA macro. If you add or delete […]
To be able to use a Pivot Table the source data you have must be arranged in way that a […]
Count unique distinct records (rows) in a Pivot Table
Excel 2013 allows you to count unique distinct values in a pivot table, this article explains how to use a […]
This article demonstrates how to build a calendar in Excel. The calendar is created as a Pivot Table which makes […]
How to calculate totals of stock transactions based on dates
Did you know that you can use a pivot table to summarize portfolio holdings at any point in time? If you trade […]
Count unique distinct values in an Excel Pivot Table
ExcelBeginner asks: I have a small problem that I am not sure on how to solve. I now have a […]
Disable autofit column widths for Pivot table
I read this interesting article Quick Trick: Resizing column widths in pivot tables on the Microsoft Excel blog. It is […]
3 Responses to “Discover Pivot Tables – Excel’s most powerful feature and also least known”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Excellent article, Debra!
Thank you, Felipe.
lol I'm terrible sorry - I wrote Debra because I was at Debra's site looking for pivot table articles and then I visited yours.
Excellent article, *OSCAR!