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:
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:
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:
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.
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.
.Orientation = xlPageField
.Position = 1
Here is the output when I drag Region to Columns area.
.Orientation = xlColumnField
.Position = 1
This happens while recording Date to Rows area.
.Orientation = xlRowField
.Position = 1
Finally Amount to Values area.
ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
"PivotTable3").PivotFields("Amount"), "Sum of Amount", xlSum
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.
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.
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..."
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
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?
ActiveSheet.PivotTables("PivotTable1").TableStyle2 = "PivotStyleLight14"
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.