Pivot tables are awesome! They’re one of Excel’s most powerful features, they allow you to quickly summarize large amounts of data in a matter of seconds. This collection of awesome tips and tricks will help you master pivot tables and become a data ninja!
You’re gonna learn all the tips the pros use, so get ready for a very very long post!
Download the example file with the data used in this post to follow along.
When using a pivot table your source data will need to be in a tabular format. This means your data is in a table with rows and columns.
When creating a pivot table it’s usually a good idea to turn your data into an Excel Table. When adding new rows or columns to your source data, you won’t need to update the range reference in your pivot tables if your data is in a Table.
Without a table your range reference will look something like above. In this example, if we were to add data past Row 51 or Column I our pivot table would not include it in the results.
To create and name your table.
Now when you create a pivot table you can reference it with a name instead of a range. When you add data to the table, you won’t need to update the range in your pivot table. Just refresh it and the new data will appear in your results.
Ok, if you decide not to use a table for some reason, then you’re going to have to update the range when you add any new rows or columns outside the original range selected.
Select your pivot table and go to the Analyze tab and press the Change Data Source button then select Change Data Source from the menu. Update your range accordingly in the following Change PivotTable Data Source pop up dialog box.
To undock the PivotTable Fields window pane hover your mouse cursor over the title until it turns into a four way arrow, then right click and drag it to your desired location. You can either leave it floating somewhere in the spreadsheet or dock it to the left side by dragging it to the very left edge.
To quickly dock the PivotTable Fields window pane hover your mouse cursor over the title until it turns into a four way arrow, then double right click. It will dock to the last docked location (either to the right or left side).
You can get more screen real estate by hiding the PivotTable Fields window. Select a cell in your pivot table and then go to the Analyze tab in the ribbon. Press the Field List button in the Show section to toggle the PivotTable Fields window on or off.
You can also close the window using the X in the upper right corner.
You can also show or hide the PivotTable Fields window with a right click anywhere inside your pivot table then select Show Field List or Hide Field List (depending on the current state of your PivotTable Fields window).
Click on the gear wheel with a downward arrow to change default appearance of the PivotTable Fields window.
There are five different available options you can select from.
The list of data fields will show in the same order as the source data by default. You can change this to show in alphabetical order (A to Z) if you prefer. Left click on the options menu in the PivotTable Fields window to access the option.
Select the Sort A to Z option in the menu. Your fields will now display in descending order!
Right click on the small downward arrow to the right of the PivotTable Fields title to move, resize or close the window.
Use the keyboard shortcut Alt + D + P to open the PivotTable and PivotChart Wizard. This will take you through the steps to set up either a pivot table or pivot chart, select your data and the location for your new pivot table or chart.
Use the ribbon command keyboard shortcut Alt + N + V to quickly create a pivot table.
Double right click on a value inside a pivot table to quickly see the data behind that aggregated value. A new sheet will be created with only the data relating to that value.
You can also access this feature by right clicking on any value then selecting Show Details.
If the ability to show the detailed data behind a pivot table result doesn’t interest you, then you can turn this feature off. This means you and can avoid creating new sheets with bits of data in them because of accidental double clicks.
Select your pivot table and go to the Analyze tab in the ribbon. Press the Options button in the PivotTable section to open the options menu.
In the PivotTable Options menu go to the Data tab and uncheck the Enable show details box to disable this feature.
This pivot table contains blank cells because our source data does not contain any records for those combinations of dimensions. For example, there is no data for Arthur James and France so the intersection of the Arthur James row and France column is blank. We can change the settings to display something such as a zero or some text saying “N/A” instead of a blank.
Left click anywhere in the pivot table then select PivotTable Options.
In the PivotTable Options menu
Now the previously blank cells have been replaced by zeros.
In this example we have create a pivot table with Customer Name and Product Sold in the Rows area. Notice that under each customer, not all the possible products are listed. Only those which we have a transaction in our data are listed. We can change this so that we see all items even when there is no data.
Right click and select Field Settings from the menu.
Check the Show items with no data box and press the OK button.
Now we can see all the available items in the Product Sold field even when there is no data.
Highlight items in a row or column and press Ctrl + – to remove them from the filter. You can select non-adjacent cells by holding Ctrl and then clicking on the cell.
You can use the Search from within a pivot table filter to add items to your previously selected items. This is essentially like using an OR condition in your filtered item searches.
Quickly select or deselect all items in the filter by using the Select All filter toggle. This can be very handy when dealing with a long list of items. You can quickly deselect all and then manually select a small number of items or quickly select all and manually deselect a small number of items.
You can defer updating the pivot table while you make changes in the PivotTable Fields window. This is generally only useful if your table is connected to a very large data source and you need to make many changes to the layout. This option is more useful for connections to external data sources as pivot tables with any data you can fit into Excel should be pretty responsive.
You can quickly add fields to your pivot table by using the check box next to the field name from the field list in the PivotTable Fields window. This can save time if you have a lot of fields to add instead of dragging and dropping each item. Fields containing text data will be added to the Rows section and fields containing numeric data will be added to the Values section when using the check box.
You can filter items in a field from the field list in the PivotTable Fields window. The filter will only apply when the field is added to the filters, columns or rows area. Hover over the desired field and click on the small downward arrow to the right of the field name to open the filter menu.
You can rename any label in a pivot table simply by selecting the cell and typing over it. You can change item names in a field, row headings, column headings, filter labels, totals or grand total labels. The only conditions are you can’t rename it to something that already exists in your source data and you can’t type over a value. This doesn’t change the source data, it just changes how the item is labelled.
One thing you may want to do is change a column heading like our “Total” column that appears as “Sum of Total” to just show “Total” in the pivot table. Unfortunately, this can’t be done, since “Total” already exists in the source data. If you try to do this you will get a warning pop up saying “PivotTable field name already exists“. We can get around this by adding a space character to the end of the name. This will count as a different name but visually it will look the same as the old field name.
You can group items in a field together to further summarize your data. Highlight the items and then right click and select Group from the menu. You can select multiple non-adjacent field items by holding the Ctrl key while making your selection. By default, the grouped name for a set of items will be Group1, Group2, Group3 etc… But you can change these to something more meaningful.
You can also ungroup a grouped field. Select it and right click then choose Ungroup from the menu.
You will notice a new field in appear which has the same name as the grouped field but with a number appended to the end. This is the newly created grouped field and you can use it just like any other field in your data. You can move it to the Filter, Row, Column area or remove it completely from the pivot table. Note that removing it from the pivot table will not ungroup the field.
You can quickly group together items in a field by highlighting the items you want to group then pressing Alt + Shift + Right Arrow key.
You can quickly ungroup grouped items by highlighting the grouped item and then pressing Alt + Shift + Left Arrow key.
Grouping dates works a little differently than grouping items in a field. When you add a date field into either the rows or columns area, Excel will assume you probably want to view the data by Month, Quarter or Year and will automatically group the dates like this. If you actually wanted the view by date, you will need to right click on it and choose Ungroup from the menu.
I’ve added the Order Date into the rows area and we can see it’s been grouped by year, quarter and month.
Just like when grouping items in a text field, Excel creates new fields which can be use like any other field. You can remove the original date field without affecting the year or quarter fields.
When you right click on the date field and select Group from the menu, you will be presented with a variety of grouping options.
Excel can also group numerical fields. This can be handy if you want to know something like “How much of my sales are from orders less than $50?“.
If I place the Total field in both the Rows and Values area, I don’t get anything that useful.
If you right click on the row, this numerical grouping menu will open and you can select a Starting and Ending point along with the interval length.
Now it’s easy to see what range most of the sales are in.
If your source data has a lot of fields then using the search box can help to narrow down the list to find what you’re looking for.
Quickly change the style of any of your pivot tables using the preset PivotTable Styles.
Go to the Design tab in the ribbon and click on the small downward arrow in the PivotTable Styles section to reveal a full selection of pivot table styles available. Note, the Design tab is only visible when the active cell cursor is in a pivot table.
Toggle different PivotTable Style Options on or off. Go to the Design tab in the ribbon and look for the PivotTable Style Options section.
Each option can be independently turned on or off to add a particular style element to your pivot table.
You will need to refresh your pivot table when you add to or change your source data if you want to see these changes reflected in your pivot table results. You can do this from several locations.
Select a cell in your pivot table to activate the PivotTable Tools tabs.
You can also refresh with a Right Click anywhere inside a pivot table and selecting Refresh from the menu.
Refresh the connection to the active pivot table’s source data by using the Alt + F5 keyboard shortcut.
Refresh All data connections for all pivot tables in the workbook by using the Ctrl + Alt + F5 keyboard shortcut.
If you want to make sure you’re always looking at the latest data in your pivot tables, you can set the workbook to refresh all pivot tables connected to particular data source. This is especially useful with external data sources.
Select one of the pivot tables connected to your data source then go to the Analyze tab and press the Options button found in the PivotTables section.
From the PivotTable Options menu, go to the Data tab and check the Refresh data when opening the file box. This will refresh all pivot tables in the workbook which are connected to the same data source.
If you’re like most people, you’ll probably end up making several copies of a pivot table in order to have different views of the data at the same time. If your pivot table is large or has items in the filter area, it can be tricky to select all of it in order to copy and paste. This is when Select Entire PivotTable comes in handy.
Go to the Analyze tab and press the Select command under the Actions section then choose Entire PivotTable. This will select all of the pivot table including any filter elements above the table.
You can also choose to select only the Labels or the Values area from here.
If you have multiple filters engaged on your pivot table you can quickly clear them all without going into each individual filter menu and selecting the Clear Filter From option.
Select a cell in the pivot table which you want to clear filters from to activate the PivotTable Tools tabs in the ribbon.
Your pivot table will revert back to a completely unfiltered state showing results based on all source data.
You can clear your pivot tables entirely back to the initial blank state if you want to start over completely with your pivot table analysis.
Your pivot table will now be in its initial blank state with all fields and filters removed.
You might have seen this happen before. You delete old data and then add in the new data, but you still see items from the old data after you refresh the pivot table. These items are still stored in the pivot cache and displayed in filter selections even if there is no data for it at all. It can be very confusing when it happens.
You can change the settings so that your pivot cache doesn’t retain any of the old field items when you refresh your data. Go to the Analyze tab and press the Options button found under the PivotTable section to open the PivotTable Option. Then go to the Data tab and select None under the Number of items to retain per field option.
Now when you refresh, the old phantom items will no longer appear.
Unfortunately, number formatting from source data does not transfer into your pivot tables. You may want to format your numbers to make them more readable.
To format a given field, Right Click on any number in that field and select Number Format from the menu. The familiar Format Cell dialog box will open with only the Numbers tab available and you will be able to format the numbers in your field the same as any other cell in your workbook.
The cool thing is that applying your number formats this way will be dynamic. Even when you move the field around in the pivot table, add other fields or filter on items the formatting will remain applied to the entire field in the pivot table.
If your pivot table has multiple dimension fields in a row or column you can expand or collapse the outer fields to show more or less detail.
Right click on the field you want to expand or collapse and select Expand/Collapse from the menu.
You can expand or collapse fields with a double right click on the field item. This is great to de-clutter a pivot table when you only need to show the full detail for one item.
You can add expand or collapse buttons to your pivot tables to make it more obvious to another user that they can expand or collapse the pivot table view as well as which items are already expanded or collapsed.
To add these buttons, select your pivot table and go to the Analyze tab and press the +/- Buttons button in the Show section.
Let’s say you have a pivot table with a field in the Filter area and you would like a pivot table for each item in the field. You might think this has to be done manually by copying the pivot table and then filtering on a new item in the field, but this can actually be done automatically using Show Report Filter Pages. In our example we have the Customer Name field in the filter area and pivot table is currently filtered on Arthur James, and we want a pivot table like this for each customer.
Select you pivot table, it will need to have a field in the filter area. Go to the Analyze tab in the ribbon and press the Options button found in the PivotTable section then select Show Report Filter Pages from the menu.
Select the desired field from the Show Report Filter Pages dialog box if you have multiple fields in the filter area of your pivot table then press the OK button.
Excel will now create a new sheet for each item in the field you selected. Each sheet will be named after the item in your field and will contain a copy of your pivot table filtered on that item. It’s a big time saver when you have a lot of items in your field.
Excel has two types of filters available for a pivot table field, Label Filter and Value Filter. Let’s say you wanted to filter this pivot table on all Product Sold that start with “P” (using a Label Filter) and having a Total value larger than $20 (using a Value Filter), with the default settings this is not possible to have both filters at the same time. We can update the settings to allow this.
Select your pivot table and go to the Analyze tab in the ribbon and press the Options button in the PivotTable section.
Enable multiple filters in the PivotTable Options dialog box.
Now you will be able to use both Label Filter and Value Filter at the same time on one field.
You can use pivot tables to get a list of the unique values in any field of your data. Simply drag the field which you want unique values from into the Rows area of a blank pivot table and the resulting pivot table will contain a list of unique values from your data for that field.
Placing any field with text data into the Values area of the pivot table will cause the calculation to default to Count instead of Sum. This means we will get the count of the number of occurrences of each item. In this example, we have placed Product Sold field which contains text data, into both the Rows and Values area of the pivot table, and we see Count of Product Sold in the Values area.
After creating your pivot table you can delete the source data if you want to reduce the workbook file size. You can delete your source data by deleting the sheet it’s contained on. Right click on the sheet tab and select Delete from the menu. Your pivot table contains a cache of the data so it will continue to work as normal. If you want to see your data again you can double left click on the grand total of your pivot table and the data will appear in a new sheet.
Sort items alphabetically in either ascending or descending order. Left click on the filter icon and select Sort A to Z for ascending or Sort Z to A for descending order.
Select the item you want to move and hover your mouse cursor over the active cell border until it turns to a four-way arrow cross.
Left click and drag the item to its new position. You will see a large green bar that indicates where the item will be placed.
Release the item into its new position.
You can sort your pivot table by ascending or descending values.
From the filter menu select the More Sort Options.
Select either Ascending (A to Z) or Descending (Z to A) then choose one of the value fields in your pivot table and then press the OK button.
If sorting a field alphabetically in ascending or descending order doesn’t suit your needs, you can create a custom sort order by creating a custom list!
To add a custom list, go to the File tab in the ribbon and select Options. From the Excel Options menu choose Advanced then scroll down to the General section and press the Edit Custom List button.
Refresh your pivot table and the order will change to that of the list you entered. This will also be the default sort order now for that field any time you create a pivot table with that field in it.
For a less cluttered look and feel you can insert a blank line after each item in your pivot table. Select your pivot table and go to the Design tab of the ribbon and click on the Blank Rows button in the Layout section then select Insert Blank Line after Each Item.
Items in your pivot table will be visually separated with white space so the viewer knows that the data pertains to something different. You can get rid of these blank rows from the Design tab of the ribbon and clicking on the Blank Rows button in the Layout section then selecting Remove Blank Line after Each Item.
You can double right click on any column heading to open the Value Field Settings for that field.
To count distinct items you will need to create your pivot table with data added to the Data Model. Check the Add this data to the Data Model box when creating your pivot table.
In this example, we have our Product Sold field in the Rows area and Customer Name in the Values area which gives us a count of the orders by product. If we want a unique count of the customers who ordered each of the products then we need to change the default Count to Distinct Count for our values settings. Right click anywhere on the field which you want to obtain a distinct count for and then select Value Field Settings from the menu.
From the Value Field Settings select Distinct Count to summarize value field by and press the OK button.
Now the values will display the distinct count. Note the Grand Total now reflects that we have 7 distinct customer names in our data of 50 orders.
You can hide selected items quickly without going into the filter menu (small down arrow next to the column heading).
This allows you to quickly filter out items without going into the filter menu and checking or unchecking boxes in a long list of items.
Similarly to hiding selected items, you can choose to keep only the selected items with a filter.
To change the layout of your pivot table go to the Design tab and select Report Layout button under the Layout section. You can select from three different layout options.
To demonstrate the different layout options, we have created a pivot table with two fields (Product Sold and Customer Name) in the Rows section and a field (Total) in the Values section.
You can repeat all your pivot tables item labels by going to the Design tab and selecting the Report Layout button under the Layout section. Select Repeat All Item Labels to turn on repeated labels and select Do Not Repeat Item Labels to turn off repeated labels.
By default, a pivot table will show the field label and then blank cells underneath for all other sub-fields included in the field heading. Creating a Tabular Form layout with Repeat All Item Labels is a great way to create another set of more aggregated “Source Data” that you can copy and paste as values and use elsewhere.
You can add grand totals to your pivot table to help you see at a glance the total for any values field across any row or column.
Go to the Design tab and select the Grand Totals command from the Layout section. Select from the four option for displaying grand totals.
When your pivot table has more that one dimension, you can add or remove subtotals to make results easier to understand.
Go to the Design tab and select the Subtotals command from the Layout section. Select from three option for displaying subtotals in your pivot table.
By default when you try to reference a cell within a pivot table in a formula, Excel will create a GETPIVOTDATA formula for the reference. These can be annoying when you want a simple relative A1 style reference since the GETPIVOTDATA acts similarly to an absolute reference.
You can turn this default option off by selecting your pivot table then going to the Analyze tab in the ribbon and clicking on the small down arrow next to the Options button under the PivotTable section. Uncheck the Generate GetPivotData option to turn this feature off. You can also turn it back on from there too!
You can add the same field to the Values area of your pivot table two or more times.
Each time you add the field to the Values area it will get a sequential number added to the end, but remember you can change these titles. You can then change the summarize type to show a Count, Average, Max, Min, Variance or Standard Deviation instead of the Sum. This will allow you to summarize the field in a variety of different ways at the same time.
Adding data bars can be a great way visually show the relative value of each item in your pivot table. In the above table we’ve added the Total field to the pivot table twice and used one instance to add data bars to the pivot table.
Select the range in your pivot table where you’d like to add the data bars.
Go to the Home tab in the ribbon and under the Styles section press the Conditional Formatting button then select the Data Bars option from the menu. You can choose either a Gradient Fill or Solid Fill and there are several different color options available. You can also create your own style data bars using the More Rules options in the menu. The cool thing is these data bars will be dynamic and applied to the entire field even if the range changes when you add dimensions or update data.
You can add color scales to your pivot table to create a heat map to easily identify high, medium and low values in your data.
Select the range in your pivot table where you would like to add the color scales.
Go to the Home tab and under the Styles section press the Conditional Formatting button then select the Color Scales option from the menu. There are several different color options to choose from or you can create your own rules and color options by selecting More Rules.
You can add various icon sets to your pivot tables to visually indicate items that increased, decreased or stayed the same.
Select the range in your pivot table where you’re wanting to add the icons.
From the Home tab and in the Styles section press the Conditional Formatting button and then select the Icon Sets option. You’ll find a large variety of icon options to choose from including arrows, shapes, flags, checks and X’s, stars and many others. You can adjust the rules for when each symbol appears by using the More Rules option.
You can add conditional formatting to highlight cell values that fit certain rules to make them stand out. In this example I have created a rule to highlight cells between $100 and $300. You can create many different types of rules.
Go to the Home tab and in the Styles section select Conditional Formatting then select the Highlight Cells Rule option. You can then select from the options mentioned above and set the criteria values required.
You can add conditional formatting to highlight cells that are in the top N or bottom N values of the pivot table. In this example I have added the formatting to show the top 3 values. Choose from several different options.
Although these options mention top and bottom 10, the number can be selected as desired.
In the Home tab and under the Styles section select Conditional Formatting then select the Top/Bottom Rules option. You can then select from the options mentioned above.
If you’ve added some sort of conditional formatting like data bars to your pivot table and want to get rid of the numbers to clean up the look of the table, then you can format the numbers as invisible text.
Right click anywhere in the field which you want to format and select Number Format from the menu. In the Format Cells dialog box choose Custom from the Category and then type three semi-colons ;;; into the Type area and press OK. The data will still exist in your pivot table, but it just won’t be visible!
When you add data bars or icon sets with conditional formatting, there is actually a setting to show only the data bars or icons. This can be found in the More Rules menu when setting up your conditional formatting.
This is a more simple option than messing around with custom formats, but is limited to data bars and icons.
For the data bars check the Show Bar Only box.
For the icon sets check the Show Icon Only box.
By default Excel will automatically adjust columns of a pivot table so that everything fits. This means those really long headings like Count of Customer Country will take up a lot of column space. If you adjust these wide columns to a smaller size, the next time you update the pivot table they will auto adjust back to fit the long heading title. You can change the settings so this doesn’t happen.
Open the pivot table option. Select your pivot table and go to the Analyze tab in the ribbon then press the Options button in the PivotTable section.
In the PivotTable Options window under the Layout & Format tab uncheck the Autofit column widths on update box. This will allow you to make changes to your pivot table without the column width automatically adjusting.
Adding a calculated field to your pivot table is equivalent to adding a new column to your source data to perform a calculation based on the other data. For example, our data contains a Total Cost and Total amount for each order. If we want to calculate the Profit Margin on each order we could add another column with the calculation Profit Margin = 1 – (Total Cost / Total) or we can add calculated field.
For a rate type calculations like a profit margin, it’s better to add the calculations as a Calculated Field rather than add an extra column with the calculation to the source data. Adding a rate calculation to the source data may result in incorrect calculations in your pivot table when viewing a pivot table at a more aggregated view than the data. Always add a calculated field instead!
Select your pivot table and go to the Analyze tab in the ribbon and press the Fields, Items & Sets button found in the Calculations section. Then select Calculated Field from the menu.
Add your calculation in the Insert Calculated Field dialog box.
Your calculated field will appear in the PivotTable Field list and can be used to create your pivot table just like any other field.
You can delete a calculated field by selecting your pivot table by going to the Analyze tab in the ribbon and pressing the Fields, Items & Sets button then selecting Calculated Field from the menu.
Delete a calculated field from the Insert Calculated Field dialog box.
The calculated field will no longer show up in your PivotTable Field list. Note, this can’t be undone!
You can quickly open the Insert Calculated Field dialog box to create a new calculated field or edit an existing calculated field by using the Ctrl + Shift + + keyboard shortcut.
If adding a calculated field is like adding a new column to your source data, then adding a calculated item is like adding a new row.
Let’s say we have a simple table set up that shows the product sold along with the total sales. Our Total column in the data doesn’t include any tax, but there is a 15% chair tax we need to include in our analysis. No problem, we can add this with a Calculated Item!
Select a field cell in your pivot table (the calculated item option will be grayed out if you select a value cell). Go to the Analyze tab then press the Fields, Items & Sets button in the Calculations section. Select Calculated Item from the menu.
Give your new calculated row a name, then add in a formula. You can add an item into the calculation by selecting the appropriate field then double clicking on any of the items in the field or pressing the Insert Item button.
I named the calculation Chair Tax and the formula will calculate 15% of the value being summarized.
We now see a new row called Chair Tax appear in our Product Sold field and the value is 15% of the Chair value. Note that this new row does contribute to the grand total.
If you create a calculated field with a division operation like our profit margin calculation, then it’s possible you might see some #DIV/0! errors (divide by zero). You can replace these with a number like 0 or some text of your choosing to make the table more presentable. Seeing these errors won’t instill confidence in your audience, so it’s best to replace them with something more assuring.
Select your pivot table and go to the Analyse tab and select Options in the PivotTable section.
Enable error values option.
Now your pivot table will be much more presentable.
You can create relationships between different data tables using pivot tables and the Data Model. When creating a pivot table check the Add this to the Data Model box in the Create PivotTable window.
For example if our sales data only contained a customer ID and the customers name was stored in another table, this would allow us to relate the customer ID to the name and build sales data pivot tables based on the customer name.
Pivot tables are amazing, but even with a pivot table it’s sometimes hard to see the trend or anomaly in the data. PivotCharts allow you to create a visualization of your pivot table summary.
The cool thing is, they are dynamically linked together. If you change something in your pivot table the changes will happen in your pivot chart and vise-versa.
You can turn your pivot tables into a variety of different chart types.
To insert a PivotChart select the pivot table you want to create PivotChart based on. Go to the Analyze tab in the ribbon and select PivotChart from the Tools section. Select the type of chart you want from the Insert Chart menu.
This can also be accessed from the Insert tab in the Charts section with the PivotChart command.
Now we have a visual representation of our pivot table! You can use the field buttons in the chart (lower left corner in the above example) to filter and sort your chart, notice this will also update your pivot table!
Select a cell inside your pivot table and press Alt + F1 to quickly add a PivotChart to the same sheet as your pivot table.
You can use the alternate ribbon command shortcut keys of Alt + N + SZ
Slicers are great for making dynamic and interactive dashboards. They work exactly like a filter but the list of filtered items will remain visible to the user.
Go to the Analyze tab in the ribbon and select Insert Slicer under the Filter section.
Select the fields for which you want to create the slicer. Selecting multiple fields will result in a separate slicer for each field selected.
You can now filter on any combination of items from your slicer.
To add a Timeline to your pivot table or chart, your source data will need to contain a date field.
Timelines are exactly like Slicers, but only for use with date fields. They allow you to filter on dates with a visual time line slider bar.
Go to the Analyze tab in the ribbon and select Insert Timeline under the Filter section.
Select the date fields for which you want to create the Timeline. Selecting multiple fields will result in a separate timeline for each field selected.
You can now filter your data on any range of dates from your Timeline.
Generally speaking, having less junk on your charts is better! This is why I like to remove all the buttons on a PivotChart to free up valuable chart real estate. Any filtering needed can be done from the linked pivot table instead of from the chart.
Right click on any of the buttons on the chart and select Hide All Field Buttons on Chart.
You can connect your slicers and timelines to any number of pivot tables. This means you can control many pivot tables or pivot charts from one single slicer or timeline. This is great for creating interactive dashboards.
Right click on the slicer or timeline and then select Report Connections from the menu. You can also access this from the Slicer Tools Option ribbon tab when your slicer is selected.
Select any pivot tables you want to connect to the slicer by checking the corresponding box and press the OK button. This is where properly naming your pivot tables can really pay off.
If your field has a lot of items in it, you can conserve some space while still showing all items in the slicer by adjusting the number of columns.
Right click on the slicer and then select Size and Properties from the menu.
In the Format Slicer window under the Position and Layout section set the desired Number of columns.
Now you can fit the same number of items in a smaller area within your slicer.
You can add filters to show your top or bottom N from your pivot table.
From the filter icon, go to the Value Filters section and select Top 10. You will be able to select from a variety of options.
We can filter any field in the row or column area of a pivot table based on the associated value in the values area.
Click on the filter icon to the right of the field name. Select Value Filters from the menu. From here you can select any number of options.
Regardless of which value filter option you selected, you’ll be able to adjust it from the value filters criteria menu.
You can increase the indent for row labels in a compact form layout pivot table to add a bit more of a distinct separation between fields.
Select your pivot table and go to the Analyze tab and select Options.
Go to the Layout & Format tab then adjust the character count for your indent as desired.
When you add subtotals to your pivot table, by default it will just show the sum subtotal. It is possible to change this to show a different calculation like Count, Average, Minimum, Maximum, Standard Deviation and others. It’s also possible to show multiple different subtotal calculations at the same time!
For this, you’ll need to have a pivot table with at least two fields in the rows area of the pivot table.
Right click on the field you’re going to add different subtotals to and then select Field Settings from the menu.
From the Field Settings menu under the Subtotals & Filters tab select the Custom subtotals option then select any Subtotal Calculation type.
This is an awesome way to show more summary information in your pivots.
Let’s say you’ve spent a decent amount of time manually filtering your pivot table to a select number of field items.
You then add data to your source data set and the new data contains additional items in your field which weren’t in the previous data.
When you refresh your pivot table, the new data items will not be included in the filtered items. You have to go through and manually select those new items if you want them to appear in the filtered pivot table.
You can change this so that new data items in a field are automatically added to any manual filters. Right click on the field then select Field Settings.
From the Field Settings menu go to the Subtotals & Filters tab and check the Include new items in manual filter box.
You can use an external data source for your pivot table. This means you can store your data in another Excel file or CSV and do your analysis in a separate workbook. Your data can be updated by other people or systems without affecting your current workbook and analysis.
Select the cell where you want your new pivot table to appear then go to the Insert tab in the ribbon and select PivotTable from the Tables section.
From the Create PivotTable menu select the Use an external data source radio button then click on the Choose Connection button.
In the Existing Connection menu select Browse for More. In the resulting file picker menu, navigate to the desired file and select it then press the Open button.
In the resulting select table menu select the location of the data from your file. My data was in a table on a sheet called Data so I have selected Data$ from the list. Make sure to check the First row of data contains column headers box if your data has column headers and then press the Ok button.
You can now finish creating and building your pivot table as usual.
You can set up your external connections to refresh with any new or updated data on a periodic schedule of your choosing. Go to the Data tab in the ribbon and select the Queries & Connections command.
If you select the pivot table with your external connection first, you can directly open the Properties menu from the Data tab.
Right click on the external connection from the Queries & Connections window and select Properties from the menu.
Under the Usage tab in the Connection Properties menu, check the Refresh every N minutes box and then set the number of minutes.
Note that all the Refresh control options are disabled (unchecked) by default. You can also enable a few other options from this menu.
The next 10 tips are the among the most powerful features of pivot tables, yet most Excel users don’t know about them.
At some stage you’ve probably gone off to the side of your pivot table and done some formula calculations to see how much of a percentage a value represents, calculated a running total or a percent difference. This stuff is already a baked in feature known as Show Values As.
Unfortunately it’s sort of hidden in the right click menu or as the secondary tab in the Value Field Settings. It’s so useful and powerful it really deserves a featured spot in the Analyze tab of the ribbon.
You can access this feature a couple of different ways.
Right click on any value and then select Show Values As from the menu. In the sub-menu you’ll be able to select from many different calculation options. You’ll also be able to set a field back to No Calculation from here.
Another option is to access this through the Value Field Settings menu.
Go to the Analyze tab and press the Field Settings button found under the Active Field section.
Or you can right click anywhere on the field to open the menu and then select Value Field Settings.
Once you’re at the Value Field Settings menu go to the Show Values As tab.
There are many options here as to how to display your values. We’ll explore these in the following tips.
Select the % of Grand Total option to show all values as a percent of the grand total. When selected the Grand Total will show as 100% and all the values in the Value area will add up to 100%.
Select the % of Column Total option to show all values in each column as a percent of that columns total. When selected each column total will show as 100% and all the values in each column will add up to 100% including the Grand Total column.
Select the % of Row Total option to show all values in each row as a percent of that rows total. When selected each row total will show as 100% and all the values in each row will add up to 100% including the Grand Total row.
Select the % of Parent Column option to show all values in each row as a percent of its parent column. Each row of values within a parent column will add to 100%. The Grand Total column will contain all 100% values.
A parent column will be the top most field in the Columns area of the pivot table.
Select the % of Parent Row option to show all values in each column as a percent of its parent row. Each column of values within a parent row will add to 100%. The Grand Total row will contain all 100% values.
A parent row will be the top most field in the Rows area of the pivot table.
Select the Difference From option to show all values as the difference between the current item and previous item, next item or a fixed item’s value.
Select the % Difference From option to show all values as the percent difference between the current item and previous item, next item or a fixed item’s value.
Select the Running Total In option to show a running total for a given field.
Select the % Running Total In option to show the running total for a given field as a percent of the Grand Total.
Select the Rank Smallest to Largest or Rank Largest to Smallest option to show a fields rank.
John is a Microsoft MVP and qualified actuary with over 15 years of experience. He has worked in a variety of industries, including insurance, ad tech, and most recently Power Platform consulting. He is a keen problem solver and has a passion for using technology to make businesses more efficient.
Subscribe for awesome Microsoft Excel videos 😃
👉 Find out more about our Advanced Formulas course!
This post is going to show you all the different methods you can insert a.
Do you want to make impressive looking and functioning Excel workbooks without.
Learn how to summarize text data in the values area of a pivot table using the data model and DAX formulas.
Great post. This is one of the most comprehensive pivot table guides I’ve seen. Had no idea bout that invisible text trick – that’s awesome.
John on 20171112104405 at 10:44 Thanks Rob. Glad to hear you learnt something new! Brian G on 20180111182220 at 18:22John – tremendous job thank you for all your work in putting these tips and tricks together. I recently had to create some pivot charts and found this a helpful resource that I am sure to reference again in the future. Brian
John on 20180111182357 at 18:23 Thanks Brian! CristIAN on 20180212202614 at 20:26WOW, I’ve use Excel for six years, I am teacher in my city and there are a lot of things about pivot table that I’d never used. Thanks for this post.
John on 20180522225659 at 22:56 No problem Christian. johan van der merwe on 20180424122550 at 12:25Hi , I used Excel Extensively but your example show a number tricks. Execellent website and images are GREAT.
John on 20180424124746 at 12:47 Thanks Johan! thomas on 20180627171938 at 17:19As a new pivot table user I LOVE this website – very well written! I do have a unique issue I’m hoping to get assistance with. I have a pivot table built out with multiple rows and columns pertaining to new hire information. My boss likes the option to “drill down” and view the source data. However, when he shares the spreadsheet with his subordinates there is one column that CANNOT be allowed to be a “drill down” column. Is this even possible? I’ve figured out how to disable “click through” for the pivot table as a whole but nothing specifically on how to disable that option for one column ONLY. Any help is appreciated.
John on 20180627181623 at 18:16Thanks Thomas! That is a unique situation. As far as I’m aware, this isn’t possible. It’s only possible to either enable or disable the drill down for everything (tip 13). It might be possible with some VBA, but that would not be my expertise (VBA express forum might be a good place to ask). Also it’s not something that would be secure, you could see the detail behind the value by pulling in all the fields into the pivot table if there were not any duplicates in the source data or you could simply disable the VBA.
thomas on 20180627200207 at 20:02 Great to know, thanks again John! Thomas on 20180713150004 at 15:00Hi John, I’m back with a new question that I hope you can help me with. I have a full pivot table built out using 2 different “tables”. The issue is that I forgot to click “Add to data model” when creating my pivots (rookie mistake). Is there a way to have that option added into a currently built pivot so I can get distinct counts to appear as an option?
John on 20180715160308 at 16:03 Unfortunately, this is a start over situation. Thomas on 20180717212946 at 21:29 Bummer, no worries. Thanks again for the replies, John DAvid on 20180701222342 at 22:23Hi, awesome detail on the features of pivot tables, but i have 2 questions that i can’t find an answer to. i have an OLAP based Cube / pivot table, that I would like to use to produce process control charts. For this i need to calculate std deviation and mean based on what i have filtered. How would i do this? I can do this manually by adding colums next to my pivot table, but really need this to be inside the pivot table, as i have 100’s of products to produce these for. 2nd) I would like to filter the values to capture data that is incorrect, ie speed greater than machine design speed. I am unable to use speed (a calculated field) as a filter in the pivot table, so how can i do this? I have tried a trick i read online about turning on filters on the cell next to the value results, and this does filter the values, but when charted the filter values still show? All help greatly appreciated, David
John on 20180702151932 at 15:19For 1, You can add multiple instances of the field into the values area and then change the summary calculation type to standard deviation and average. Right click on the values in the pivot table and select Value Field Setting to do this. For 2, this is doable with Power Pivot, but is beyond the scope of this post.
Junaid Jamal on 20180711032708 at 03:27Awesome Tutorial on Pivot Tables!! Explained nicely, short and sweet. This gives the complete information about pivot tables. The last part about Show Value As is extremely helpful for me…Thanks 4 dat . Moreover, the images prepared for explanation is extremely helpful and clear. If you don’t mind, what tool you used to create those NICE images?
John on 20180711080059 at 08:00 Thanks! It’s Snagit by Techsmith for the images. Matt Bishop on 20180723165141 at 16:51 Hi John, Thanks for posting this! Minor typo in the beginning: “Change Source Date” should be “Data” John on 20180723171244 at 17:12 Thanks Matt! Fixed now. Wes on 20180907124626 at 12:46This is sweet! That Generate GetPivotTable issue has driven me crazy forever. Question though, was researching Top 10 issue and I can do what you have in your instructions. What if there are multiple columns, such as Jan-Aug and I want to Filter Top 10 for just one column, say the most recent month of August. I can’t figure this out. It always does it based off of the total for all months, not just a specific month. Is this possible?
John on 20180909102815 at 10:28Yes, GetPivotData annoys a lot of people. Regarding top 10, yes unfortunately it’s only for the entire field in the values area of the pivot table. This can be done with Power Pivot and DAX measures, but is beyond the scope of this article.
Elaine on 20181017082108 at 08:21Hi John…I love your site!! Please, could you help me? I created a pivot table to be my checkbook register; my raw data worksheet has been entered just as I would enter it in an actual register (but no check #): date, description of the transaction, debit amt., credit amt., and balance. Everything works GREAT except the description of transactions are sorted alphabetically instead of how they are actually entered in the raw data. This a problem because it can/will throw off the correct balance. Nowhere can I find how to NOT have the pivot table sort or how to make it sort by nothing. It’d be okay if I only had one entry per day (DATE), but sometimes I have multiple transactions in one day i.e. on 10/01/18 I might enter a transaction for RENT, AUTO payment, Walmart, Target, Aldi. Each entry would have a different DEBIT amount subtracted from the BALANCE and they need to be ordered in the pivot table as entered in the check register (raw data). Hope you understand this; if not let me know where to send samples. Thank you in advance for your help.
John on 20181017140636 at 14:06If I understand correctly, you won’t be able to do what you’re looking for with pivot tables alone. You will need to create a new index/serial number column in your source data to maintain the sort order. My approach would be to load data into power query to create the column automatically, then load the result into a pivot table doing something similar to this depending on your exact situation. Then you can add the new index column into the pivot table and sort by that. Best of luck!
Elaine on 20181017155735 at 15:57Hi John, thank you for your quick reply. So, I if I use the index/serial number method, it will keep my data from being sorted; it will cause the pivot table to leave my data as is? I’ve never worked with power query but I’m a quick learning; I’ve only been using pivot tables for a few weeks and I’ve got a pretty good handle on them. Thank you again, and I’m sure with your tutorial I’ll be able to get it. I’ll let you know how it goes.
John on 20181017160208 at 16:02It won’t change how pivot tables sort alphabetically, but it will give you another way of sorting the pivot table based on the index instead. The idea is to create the index in a way that gets your desired sort order.
Elaine on 20181019124743 at 12:47Hi John, I tried and succeeded in creating my pivot table using Power Query. But every time attempted to enter a new ‘check’ and then Refresh to update the ID number nothing happened.
Plus, I lost my calculation for my Balance field. I tried three different times to make the table using Power Query.
Good morning John, I solved the problem of keeping my data in the order that I entered it. I inserted a blank field as the first field in my raw data then used the =ROWS function i.e =ROW($a$2:a2). This numbers each line of data sequentially and even if I delete a row, this will renumber the remaining rows. Thank you so much for your help, but I’m going to have spend more time on using Power Query to learn it better. Again, thank you.
wHITNEY on 20181024122704 at 12:27Hi John! Great tricks in here. Wondering if you can help me with something. In my pivot table I need to add Status in Filter report but do not want it to show on my table because of limited space on my report. Is it possible to hide it from showing up on my table but still use it to filter out unwanted data? Thanks in advance.
John on 20181024123750 at 12:37 You could use a slicer and move the slicer to another sheet. Javier Perez on 20181025112817 at 11:28Thanks John! Microsoft should hire you to make their support pages more digestible. You made every explanation clear, breve and visually expedited. I work a lot with pivot tables and handle some hundreds of thousands rows of data. There are some options I haven’t used but reading your work some “cliks” came to my brain. Thanks again.
John on 20181025115730 at 11:57 Thanks Javier! Best of luck with your new pivot table idea implementation. John on 20181025120043 at 12:00Microsoft did give me their MVP award, and that’s good enough for me ☺️ maintaining their support pages sounds like a tedious job I wouldn’t be too keen on.
Lokesh Purushothaman on 20181028221128 at 22:11Hi John,
Excellent detailed list of pivot table functionalities with vivid examples.
I bumped into this link looking for answer to a question. Please let me know if you can help
Is it possible to not have the detail rows in the pivot table but just display the grand totals?
HI John, I have a large report vba macro. The original data set where template for report is set has many pages. But part of the process is to create reports for several other units that have much small subsets. How do I create the print range in the original report template so that each of the reports that are produced only have the correct amount and not the 2000 blank pages due to the original?
John on 20181204134110 at 13:41 I’m not sure how this relates to pivot tables? Jane on 20190131151522 at 15:15What a great resource! Thank you so much! So helpful in my quest for knowledge regarding pivot tables tips/tricks!
Denis Gerber on 20190201092920 at 09:29Very good job! Can you possibly help me with the following problem … I’ve created pivot tables based on the PowerPivot data model. The following problem: The “Include new items in manual filter” option is greyed out as soon as I place a field in the filter area. (not in rows area and not in column area!!) Have you ever had such a thing … is there another solution, except – put the field in rows or columns?
John on 20190201093236 at 09:32Yes, certain features are not available when using the data model / power pivot. Just use a slicer to filter instead.
Janice Halvorsen on 20190226212747 at 21:27Great tips about Pivot Tables. i regularly build pivot tables, and one of the more tedious formatting tasks is having to apply number formatting to each column one at a time. i often have multiple columns (like sales and gross margin) and want to apply the same number format to each column. Is there a way to apply formatting to more than 1 column at a time?
John on 20190227155425 at 15:54 No, unfortunately there’s no native functionality to do this with pivot tables. MP on 20190529172012 at 17:20Assuming you want all value columns formatted the same way: In Analyze tab under Actions, Select “Entire Pivot table”, then Select “Values”. When you apply a format, it will apply to all values in the pivot.
John on 20190529182008 at 18:20 Awesome tip! Thanks. Era G on 20190301073024 at 07:30Thanks for such a great tutorial!! I’ve surely learned so much from it!
Count unique rows in the Pivot table appeared to be a life-saver, but I’ve got some surprising results applying filters. Can’t figure out the reason.
I’m dealing with CRM data of completed projects for each manager comparing 2 years of data. So the client either returned or left. So when I apply a filter to see only left clients the number appears to be quite big, same with the returned category. Both of them must sum up to the grand total, however, the number is higher.
Data list is quite big so checking row by row will take time, could you pls help me with any lifehacks applicable here?
Do you see any reason of distinct count expending number after applying filters?
To understand why your grand total value isn’t what you’re expecting, you need to understand filter context. A bit too big of a topic for a comment. I’d recommend getting the book “Power Pivot and Power BI: The Excel User’s Guide to DAX, Power Query, Power BI & Power Pivot in Excel 2010-2016” if you want a great book that will explain context in plain English.
Michael Limas on 20190406122620 at 12:26I use pivot tables frequently and I really enjoyed your information – very well done! I have a pet peeve that I wonder if you have a way to solve. I have a pivot with two numeric values. One column is Actual and the other is Budget. I use the show values as Difference From – so I can get the difference between the Actual and Budget. However, I always end up with a blank column and I can’t see how to eliminate the blank column. My table is like this:
A = Acct
B & C are dollar values
B = Actual
C = Budget
D & E are dollar change – showing difference from budget
D – actual difference, this is the value I want
E = budget difference, but since it is based on budget this is just blank – this is the column I want to go away or not show up Any suggestion would be appreciated
Show value as difference between items in the same field, where as you want the difference between two fields. I think you want a calculated column = actual – budget.
rajendra Panshikar on 20190505005718 at 00:57Dear John,
Great & Informative, I have been working on tables since LOTUS 123, learned many tricks in handling and presenting the data, however would like to share something or may not come across on the feature, further may help to improvise
Point 1 : will it be possible for a user to select, which Column item field goes to which area of Pivot prior actually creating a Pivot, The user visualize & decide the same when he was actually at source/original data, this feature can be added at data filter option, user should be allowed click on the protected filtered sheet and select the options, further the pivot should be generated only on the selected fields, which will save the memory as well, further will show only the required field details at enable contents, when he share the data to another user, currently enable features discloses all information at source data, which I need to prevent
Point 2 : There is calculated column addition feature to value area, however I am working on a trick which will update the external adjustment Manual line item comments to Pivot table, when user runs a macro, at each new Remark/comment, the feature keeps only the last backup and refreshes the pivot with last updated Comment as Row Item, its working only, when you have standard pivot. trying to make it dynamic ! when user interchanges the Item, which possibly result all analysis quickly with user visual comment ! hope it works ! let me know if there such feature to add text item at row area instead of value !
Have a nice day
Cheers !
