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!

Download the example file with the data used in this post to follow along.

Your Source Data Needs to be in Tabular Format

Data-in-Tabular-Format 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

  • The first row should contain your column headings which describes the data directly below in that column. There should be no blank column headings in your data.
  • Each row after the column headings should pertain to exactly one record in your data. For example, if your table contains customer data then each row might have the name, street address, postal code and email address for exactly one customer.

Use a Table for Your Source Data

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.

Create-Pivot-Table-Menu-Using-Range 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

  1. Select your data.
  2. Go to the Insert tab and press the Table button in the Tables section, or use the keyboard shortcut Ctrl + T.
  3. Press the OK button.
  4. With the active cell inside the table, go to the Table Tools Design tab.
  5. Change the Table Name under the Properties section and press Enter.

Create-Pivot-Table-Menu-Using-A-Table 101 Advanced Pivot Table Tips And Tricks You Need To Know

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 the pivot table and the new data will appear in your pivot table results.

Undock the PivotTable Fields Window

Undock-the-PivotTable-Field-window 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

Quickly Dock the PivotTable Fields Window

Quickly-Dock-The-PivotTable-Field-Window 101 Advanced Pivot Table Tips And Tricks You Need To Know

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).

Hide or Unhide the PivotTable Fields Window

Show-Or-Hide-The-PivotTable-Field-Window 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

Close-The-PivotTable-Fields-Window 101 Advanced Pivot Table Tips And Tricks You Need To Know

You can also close the PivotTable Fields window using the X in the upper right corner.

Show-or-Hide-Field-List-from-Right-Click 101 Advanced Pivot Table Tips And Tricks You Need To Know

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).

Change the Default Arrangement of the PivotTable Fields Window

Change-How-The-PivotTable-Fields-Window-Is-Arranged 101 Advanced Pivot Table Tips And Tricks You Need To Know

Click on the gear wheel with a downward arrow to change default appearance of the PivotTable Fields window.

PivotTable-Fields-Window-Options 101 Advanced Pivot Table Tips And Tricks You Need To KnowThere are five different available options you can select from.

  1. Field Section and Areas Section Stacked
  2. Field Section and Areas Section Side-By-Side
  3. Field Section Only
  4. Areas Section Only (2 by 2)
  5. Areas Section Only (1 by 4)

Move, Resize and Close the PivotTable Fields Window

Move-Resize-Or-Close-The-PivotTable-Fields-Window 101 Advanced Pivot Table Tips And Tricks You Need To Know

Right click on the small downward arrow to the right of the PivotTable Fields title to move, resize or close the window.

  • Move – This will allow you to undock the window and move it around the spreadsheet.
  • Size – This allows you to adjust the width and height (when undocked) of the window.
  • Close – This allows you to close the window. You can open it again from the Analyze tab > Field List command.

PivotTable and PivotChart Wizard Keyboard Shortcut

Open-PivotTable-And-PivotChart-Wizard-Keyboard-Shortcut 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

Create a PivotTable With a Keyboard Shortcut

Create-Pivot-Table-Keyboard-Shortcut 101 Advanced Pivot Table Tips And Tricks You Need To Know

Use the ribbon command keyboard shortcut Alt + N + V to quickly create a pivot table.

Show Details Behind a Pivot Table Result

Double-Click-To-See-Data-Behind-A-Value 101 Advanced Pivot Table Tips And Tricks You Need To Know

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 in your pivot table relating to that value.

Turn Off Show Details to Avoid Accidental Double Click

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 on a pivot table.

Open-the-Pivot-Table-Options 101 Advanced Pivot Table Tips And Tricks You Need To Know

Select your pivot table and go to the Analyze tab in the ribbon. Press the Options button in the PivotTable section to open the pivot table options menu.

Pivot-Table-Options-Enable-Show-Details 101 Advanced Pivot Table Tips And Tricks You Need To Know

In the PivotTable Options menu go to the Data tab and uncheck the Enable show details box to disable this feature.

Replace Blank Cells in a Pivot Table

Pivot-Table-With-Blank-Cells 101 Advanced Pivot Table Tips And Tricks You Need To Know

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 pivot table settings to display something such as a zero or some text saying “N/A” instead of a blank.

Replace-Empty-Cells-In-A-Pivot-Table-Step-1 101 Advanced Pivot Table Tips And Tricks You Need To Know

Left click anywhere in the pivot table then select PivotTable Options.

Replace-Empty-Cells-In-A-Pivot-Table-For-Empty-Cells-Show 101 Advanced Pivot Table Tips And Tricks You Need To Know

In the PivotTable Options menu

  1. Go to the Layout & Format tab.
  2. Check the For empty cells show box and enter the value you would like to show for blanks. In our example we will replace blank cells with 0.
  3. Press the OK button.

Empty-Cells-In-A-Pivot-Table-Replaced-With-Zero 101 Advanced Pivot Table Tips And Tricks You Need To Know

Now the previously blank cells in our pivot table have been replaced by zeros.

Remove Items from a Filter Using a Keyboard Shortcut

Remove-Items-From-A-Filter-Using-A-Keyboard-Shortcut 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

Add the Current Selection to the Filter

Add-The-Current-Selection-To-The-Filter 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

  1. Select your first set of items to be filtered on either manually or using the search box (with the same method as step 2 & 3).
  2. Use the Search box to search for and then select the second set of items to be filtered on.
  3. Check the box marked Add current selection to filter that will appear when using the search box.
  4. Press the OK button.
  5. Now if you view the filter, you will see both your selections from step 1 and selections from step 2 included in the filter.

Use the Select All Filter Toggle

Pivot-Table-Filter-Select-All-Button 101 Advanced Pivot Table Tips And Tricks You Need To Know

Quickly select or deselect all items in a pivot table 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.

Defer Layout Update

Defer-Layout-Update 101 Advanced Pivot Table Tips And Tricks You Need To Know

You can defer updating the pivot table while you make changes in the PivotTable Fields window. This is generally only useful if your pivot table is connected to a very large data source and you need to make many changes to the layout of the pivot table. 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.

  1. Check the Defer Layout Update box in the PivotTable Fields window.
  2. Make the changes to your pivot table layout in the Filters, Columns, Rows or Values section. Your pivot table will remain static.
  3. Press the Update button. Now your pivot table will update to reflect all the changes made.

Add or Remove Fields Using the Check Box

Use-The-Check-Box-To-Add-or-Remove-Fields-From-Your-Pivot-Table 101 Advanced Pivot Table Tips And Tricks You Need To Know

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 to your pivot table 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.

Filter Fields from the PivotTable Fields Window

Filter-From-the-PivotTable-Field-Window 101 Advanced Pivot Table Tips And Tricks You Need To Know

You can filter items in a field from the field list in the PivotTable Fields window even if the field doesn’t appear in your pivot table. Hover over the desired field and click on the small downward arrow to the right of the field name to open the filter menu.

Rename Any Pivot Table Label

Change-Any-Label-in-a-Pivot-Table 101 Advanced Pivot Table Tips And Tricks You Need To Know

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 in the pivot table.

Rename A Label With A Trailing Space

PivotTable-Field-Name-Already-Exists 101 Advanced Pivot Table Tips And Tricks You Need To Know

One thing you may want to do is change a column heading that appears as “Sum of Field Name” in the pivot table to just show “Field Name“. Unfortunately, this can’t be done, since “Field Name” will already exist 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.

Group Together Items in a Field

Group-Items-in-a-Field 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

New-Grouped-Field-Item-in-the-PivotTable-Field-List 101 Advanced Pivot Table Tips And Tricks You Need To Know

You will notice a new field in your pivot table 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 pivot table. 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.

Group Together Items in a Field Using a Keyboard Shortcut

Group-Items-in-a-Field-Using-a-Keyboard-Shortcut 101 Advanced Pivot Table Tips And Tricks You Need To Know

You can quickly group together items in a field by highlighting the items you want to group then pressing Alt + Shift + Right Arrow key.

Ungroup Grouped Items Using a Keyboard Shortcut

Ungroup-Items-in-a-Field-Using-a-Keyboard-Shortcut 101 Advanced Pivot Table Tips And Tricks You Need To Know

You can quickly ungroup grouped items by highlighting the grouped item and then pressing Alt + Shift + Left Arrow key.

Search the PivotTable Fields List

PivotTable-Fields-Search-Box 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

Give Your Pivot Table a Different Style

Choose-From-Many-Different-Pivot-Table-Visual-Styles 101 Advanced Pivot Table Tips And Tricks You Need To Know

Quickly change the style of any of your pivot tables using the preset PivotTable Styles.

Select-a-PivotTable-Style 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

PivotTable Style Options

PivotTable-Style-Options 101 Advanced Pivot Table Tips And Tricks You Need To Know

Toggle different PivotTable Style Options on or off. Go to the Design tab in the ribbon and look for the PivotTable Style Options section.

PivotTable-Style-Options-Selected 101 Advanced Pivot Table Tips And Tricks You Need To Know

Each option can be independently turned on or off to add a particular style element to your pivot table.

  1. With all options unchecked the pivot table is empty of row headers, banded rows, column headers and banded columns.
  2. Adding Row Headers.
  3. Adding Banded Rows.
  4. Adding Column Headers.
  5. Adding Banded Columns.

Refresh Your Pivot Tables

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.

Refresh-Your-Pivot-Table-Data 101 Advanced Pivot Table Tips And Tricks You Need To Know

Select a cell in your pivot table to activate the PivotTable Tools tabs.

  1. Go to the Analyze tab.
  2. Press the Refresh button.
  3. Select either Refresh or Refresh All.
    • Refresh will refresh any pivot table connected to the source data of the active pivot table.
    • Refresh All will refresh all data connections for all pivot tables in the workbook.

Right-Click-to-Refresh 101 Advanced Pivot Table Tips And Tricks You Need To Know

You can also refresh with a Right Click anywhere inside a pivot table and selecting Refresh from the menu.

Refresh with a Keyboard Shortcut

Refresh-with-Alt-F5 101 Advanced Pivot Table Tips And Tricks You Need To Know

Refresh the connection to the active pivot table’s source data by using the Alt + F5 keyboard shortcut.

Refresh All with a Keyboard Shortcut

Refresh-All-with-Ctrl-Alt-F5 101 Advanced Pivot Table Tips And Tricks You Need To Know

Refresh All data connections for all pivot tables in the workbook by using the Ctrl + Alt + F5 keyboard shortcut.

Automatically Refresh Your Pivot Tables when Opening Your Workbook

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.

Open-the-Pivot-Table-Options 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

Pivot-Table-Options-Refresh-Data-When-Opening-the-File 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

Clear All Filters

Pivot-Table-with-Multiple-Filters 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

Clear-All-Filters-from-a-Pivot-Table 101 Advanced Pivot Table Tips And Tricks You Need To Know

Select a cell in the pivot table which you want to clear filters from to activate the PivotTable Tools tabs in the ribbon.

  1. Go to the Analyze tab.
  2. Press the Clear button from the Actions section.
  3. Select Clear Filters from the menu.

Your pivot table will revert back to a completely unfiltered state showing results based on all source data.

Clear Your Entire Pivot Table

Clear-Your-Pivot-Table-to-a-Blank-State 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

  1. Go to the Analyze tab.
  2. Press the Clear button from the Actions section.
  3. Select Clear All from the menu.

Blank-Pivot-Table 101 Advanced Pivot Table Tips And Tricks You Need To Know

Your pivot table will now be in its initial blank state with all fields and filters removed.

Format Numbers in Your Pivot Table

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.

Number-Formats-in-Pivot-Tables 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

Pivot-Table-with-Currency-Format-Applied 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

Double Click to Expand or Collapse Headings

Double-Click-to-Expand-or-Collapse-Headings 101 Advanced Pivot Table Tips And Tricks You Need To Know

If your pivot table has multiple dimension fields in a row or column you can expand or collapse the outer 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.

Add or Remove Expand or Collapse buttons

Pivot-Table-with-Expand-and-Collapse-Buttons 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

Add-Expand-or-Collapse-Buttons 101 Advanced Pivot Table Tips And Tricks You Need To Know

To add these buttons, select your pivot table and go to the Analyze tab and press the +/- Buttons button in the Show section.

Automatically Create a Pivot Table for each Item in a Filter

Pivot-Table-with-Filtered-Field-Area 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

Options-Show-Report-Filter-Pages 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

Show-Report-Filter-Pages-Dialog-Box 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

New-Sheet-for-Each-Filter-Item 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

Allow Multiple Filters Per Field

Pivot-Table-by-Product-with-No-Filter 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

Pivot-Table-Options 101 Advanced Pivot Table Tips And Tricks You Need To Know

Select your pivot table and go to the Analyze tab in the ribbon and press the Options button in the PivotTable section.

Options-Allow-Multiple-Filters-Per-Field 101 Advanced Pivot Table Tips And Tricks You Need To Know

Enable multiple filters in the PivotTable Options dialog box.

  1. Go to the Totals & Filters tab.
  2. Check the Allow multiple filters per field box.
  3. Press the OK button.

Label-and-Value-Filter-Both-Engaged 101 Advanced Pivot Table Tips And Tricks You Need To Know

Now you will be able to use both Label Filter and Value Filter at the same time on one field.

Get a List of Unique Values from a Field

Get-a-List-of-Unique-Items-from-a-Field 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

Count the Occurrence of an Item in a Field

Count-the-Occurrence-of-an-Item-in-a-Field 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

Delete Your Source Data

Delete-your-Source-Data 101 Advanced Pivot Table Tips And Tricks You Need To Know

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 Ascending or Descending Order

Sort-Alphabetically-Using-the-Filter 101 Advanced Pivot Table Tips And Tricks You Need To Know

Sort the items in your pivot table 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.

Manually Sort Items

Manually-Sort-Items-Left-Click-Edge-of-the-Cell 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

Manually-Sort-Items-Drag-to-New-Position 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

Manually-Sort-Items-Result 101 Advanced Pivot Table Tips And Tricks You Need To Know

Release the item into its new position.

Sort Items According to a Corresponding Value

Sort-Ascending-or-Descending-by-Value 101 Advanced Pivot Table Tips And Tricks You Need To Know

You can sort your pivot table by ascending or descending values.

More-Sort-Options-From-the-Filter 101 Advanced Pivot Table Tips And Tricks You Need To Know

From the filter menu select the More Sort Options.

Select-to-Sort-Field-Based-on-a-Value-Field 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

Create a Custom Sort Order

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!

Create-Custom-List-for-Pivot-Table-Sort-Order 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

  1. Select NEW LIST from the Custom lists box.
  2. Enter your list of field items appearing in the order you want them to sort in your pivot table.
  3. Press the Add button to add your list.
  4. Press the Ok button.

Pivot-Table-with-New-Custom-Sort-Order 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

Insert Blank Line After Each Item

Insert-Blank-Line-After-Each-Item 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

Pivot-Table-with-Blank-Rows-After-Each-Item 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

Double Click to Open Value Field Settings

Double-Click-to-Open-Value-Field-Settings 101 Advanced Pivot Table Tips And Tricks You Need To Know

You can double right click on any column heading to open the Value Field Settings for that field.

Count Distinct Items

Add-This-to-the-Data-Model 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

Right-Click-to-Open-Value-Field-Settings 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

Value-Field-Settings-Select-Distinct-Count 101 Advanced Pivot Table Tips And Tricks You Need To Know

From the Value Field Settings select Distinct Count to summarize value field by and press the OK button.

Pivot-Table-with-Distinct-Count-of-Customer-Name 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

Hide Selected Items

Filter-Hide-Selected-Items 101 Advanced Pivot Table Tips And Tricks You Need To Know

You can hide selected items quickly without going into the filter menu (small down arrow next to the column heading).

  1. Select the items you want to hide with your filter. You can use Ctrl to select non-adjacent items. Then right click on the selected items.
  2. Select Filter from the menu.
  3. Select Hide Selected Items from the sub menu.

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.

Change the Pivot Table Layout

Report-Layout-Options-from-Design-Tab 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

  1. Show in Compact Form
  2. Show in Outline Form
  3. Show in Tabular Form

Report-Layout-Options 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

  1. Compact form will contain all the Row fields in one column in a hierarchical structure.
  2. Outline form will still have a hierarchical structure but each Row field will be in a separate column in the pivot table.
  3. Tabular form will not be in a hierarchical structure and each Row field will be in a separate column in the pivot table.

Repeat All Item Labels

Repeat-All-Item-Labels-from-Design-Tab 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

Repeat-All-Item-Labels 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

Turn Grand Totals On or Off

Pivot-Table-Grand-Totals 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

Grand-Total-Options 101 Advanced Pivot Table Tips And Tricks You Need To Know

Go to the Design tab and select the Grand Totals command from the Layout section. Select from the four option for displaying grand totals.

  1. Off for Rows and Columns (no grand totals will display)
  2. On for Rows and Columns
  3. On for Row Only
  4. On for Columns Only

Turn Subtotals On or Off

Pivot-Table-Subtotals 101 Advanced Pivot Table Tips And Tricks You Need To Know

When your pivot table has more that one dimension, you can add or remove subtotals to make results easier to understand.

  1. No subtotals results in a cleaner looking pivot table, but you lose vital information about totals across parent level field grouping.
  2. Adding subtotals below the group results in extra rows in your pivot table.
  3. Adding subtotals above the group results in the extra information but without the extra rows (with a compact layout).

Subtotal-Options 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

  1. Do Not Show Subtotals
  2. Show all Subtotals at Bottom of Group
  3. Show all Subtotals at Top of Group

Turn Off GETPIVOTDATA

GETPIVOTDATA-When-Referencing-a-Pivot-Table-Cell 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

Turn-On-or-Off-Generate-GETPIVOTDATA 101 Advanced Pivot Table Tips And Tricks You Need To Know

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!

Add a Second Field to the Values Area

Add-a-Second-Field-to-the-Values-Area 101 Advanced Pivot Table Tips And Tricks You Need To Know

You can add the same field to the Values area of your pivot table two or more times.

  1. Right click on the field you want to add to the Values area again and select Add to Values.
  2. You can also left click and drag the field into the Values area again.

Pivot-Table-with-Multiple-Duplicate-Value-Fields 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

Add Data Bars to a Pivot Table

Pivot-Table-with-Data-Bars 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

Add-Data-Bars-Select-Range 101 Advanced Pivot Table Tips And Tricks You Need To Know

Select the range in your pivot table where you’d like to add the data bars.

How-to-Add-Data-Bars 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

Add Color Scales to a Pivot Table

Pivot-Table-with-Color-Scales 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

How-to-Add-Color-Scales 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

Add Icon Sets to a Pivot Table

Pivot-Table-with-Icon-Sets 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

How-to-Add-Icon-Sets 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

Add Highlighted Cells Rules to a Pivot Table

Pivot-Table-with-Highlighted-Cells-Between-100-and-300 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

  • Numbers greater than a given value.
  • Numbers less than a given value.
  • Numbers between two given values.
  • Numbers equal to a given value.
  • Text that contains a specific string.
  • Dates that meet a given criteria.
  • Duplicate values.

How-to-Highlight-Cells-Rule 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

Add Highlighted Top or Bottom N Formatting

Pivot-Table-with-Top-3-Values-Highlighted 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

  • Top 10 items.
  • Top 10 percent.
  • Bottom 10 items.
  • Bottom 10 percent.
  • Above average.
  • Below average.

Although these options mention top and bottom 10, the number can be selected as desired.

How-to-Highlight-Top-or-Bottom-N-Cells 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

Format Numbers as Invisible Text

Pivot-Table-with-Invisible-Text 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

Format-Numbers-as-Invisible-Text 101 Advanced Pivot Table Tips And Tricks You Need To Know

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!

Prevent Column Width Changing on Update

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-Options 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

Uncheck-Autofit-Column-Widths-on-Update-from-PivotTable-Options 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

Add A Calculated Field

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!

Add-a-Calculated-Field-to-a-Pivot-Table 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

Create-your-Formula-in-the-Insert-Calculated-Field-Dialog-Box 101 Advanced Pivot Table Tips And Tricks You Need To Know

Add your calculation in the Insert Calculated Field dialog box.

  1. Give your new calculation a Name. This is the field name that will appear in the pivot table.
  2. Create your Formula. You can double right click any field in the field list to use it in your calculation.
  3. Press the Add button.
  4. Press the OK button.

Pivot-Table-with-Profit-Margin-Calculated-Field 101 Advanced Pivot Table Tips And Tricks You Need To Know

Your calculated field will appear in the PivotTable Field list and can be used to create your pivot table just like any other field.

Removing A Calculated 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.

Remove-a-Calculated-Field-from-a-Pivot-Table 101 Advanced Pivot Table Tips And Tricks You Need To Know

Delete a calculated field from the Insert Calculated Field dialog box.

  1. Use the drop down menu to select the calculated field you want to delete.
  2. Press the Delete button.
  3. Press the OK button.

The calculated field will no longer show up in your PivotTable Field list. Note, this can’t be undone!

Insert a Calculated Field with a Keyboard Shortcut

Insert-Calculated-Field-with-Ctrl-Shift- 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

Replace Errors In A Pivot Table

Pivot-Table-with-Divide-by-Zero-Errors 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

Pivot-Table-Options 101 Advanced Pivot Table Tips And Tricks You Need To Know

Select your pivot table and go to the Analyse tab and select Options in the PivotTable section.

PivotTable-Options-For-error-values-show 101 Advanced Pivot Table Tips And Tricks You Need To Know

Enable error values option.

  1. Go to the Layout & Format tab.
  2. Check the For error values show box and input a value or some text.
  3. Press the OK button.

Pivot-Table-with-Errors-Replaced-by-Zero 101 Advanced Pivot Table Tips And Tricks You Need To Know

Now your pivot table will be much more presentable.

Add Relationships To Your Pivot Tables

Create-PivotTable-Add-this-to-the-Data-Model 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

Read this post for more detail on building relationships in pivot tables.

Create a PivotChart

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.

  • Column charts
  • Line charts
  • Pie charts
  • Bar charts
  • Area charts
  • Radar charts

Insert-a-PivotChart-from-Analyze-Tab 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

PivotChart-Column-Chart 101 Advanced Pivot Table Tips And Tricks You Need To Know

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!

Insert a PivotChart with a Keyboard Shortcut

Keyboard-Shortcut-to-Add-a-PivotChart 101 Advanced Pivot Table Tips And Tricks You Need To Know

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

Filter on Top N Items

Pivot-Table-with-Top-3-Filter 101 Advanced Pivot Table Tips And Tricks You Need To Know

You can add filters to show your top or bottom N from your pivot table.

How-to-Filter-Top-or-Bottom-N-in-a-Pivot-Table 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

  1. Select to either show the Top or Bottom results from your pivot table.
  2. Select a number of items, percentage or total sum for the top or bottom criteria.
  3. Select from either Items, Percent or Sum.
    • Items – This will show the items in your field that have the highest or lowest N values.
    • Percent – This will show the items in your field where the value is in the top or bottom Nth percentile.
    • Sum – This will show the top or bottom items in your field where the sum is greater than the number entered in step 2.
  4. Select the metric in your pivot table values area to base the top or bottom results on.

Add a Value Filter for any Field

Pivot-Table-with-Greater-Than-or-Equal-to-Filter-Applied 101 Advanced Pivot Table Tips And Tricks You Need To Know

We can filter any field in the row or column area of a pivot table based on the associated value in the values area.

Add-a-Value-Filter-to-Any-Field 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

  • Filter on items where the value Equals a given value.
  • Filter on items where the value Does Not Equal a given value.
  • Filter on items Greater Than a given value.
  • Filter on items Greater Than Or Equal To a given value.
  • Filter on items Less Than a given value.
  • Filter on items Less Than Or Equal To a given value.
  • Filter on items Between two given values.
  • Filter on items Not Between two given values.

Pivot-Table-Value-Filter-Criteria-Selection-Menu 101 Advanced Pivot Table Tips And Tricks You Need To Know

Regardless of which value filter option you selected, you’ll be able to adjust it from the value filters criteria menu.

  1. Select which values field your criteria will apply to.
  2. Select the filtering option desired. This allows you to change the option you previously selected.
  3. Enter the criteria value to filter based on. If you selected a filtering option that requires two inputs, there will be two input fields here.

Increase the Row Label Indent in Compact Form Layout

Compact-Form-Pivot-Table-with-Increased-Indent 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

Open-the-Pivot-Table-Options 101 Advanced Pivot Table Tips And Tricks You Need To Know

Select your pivot table and go to the Analyze tab and select Options.

PivotTable-Options-Compact-Form-Indent-Row-Labels 101 Advanced Pivot Table Tips And Tricks You Need To Know

Go to the Layout & Format tab then adjust the character count for your indent as desired.

Add Multiple Subtotal Calculations

Add-Multiple-Subtotals-to-a-Pivot-Table 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

Open-Field-Settings-to-Add-Multiple-Subtotals 101 Advanced Pivot Table Tips And Tricks You Need To Know

Right click on the field you’re going to add different subtotals to and then select Field Settings from the menu.

Field-Settings-Menu-Custom-Subtotals 101 Advanced Pivot Table Tips And Tricks You Need To Know

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.

More Tips Coming Soon…