Top 31 Pivot Table Tips & Tricks for Advanced Excel Users

No doubt, the pivot table is the single most important tool which can help you to become an advanced Excel user.

When it comes to data analysis, quick and effective reporting or presenting summarized data nothing can beat a pivot table.

The best thing is, it is powerful and flexible.

Even when you compare formulas and pivot table, you will find that pivot tables are easy to use and manage.

…here’s the idea:

If you want to take your skills to a whole next level you need to get better with pivot tables.

For this, the best way is to have list of tips and trick which you can learn.

Pivot table Tips and Tricks

And here for you, here is a list of useful tips and tricks which you can learn to make best out of this amazing tool.

let's get started…

1. Use Table for Source Data


2. Single Slicer for all the Pivot Tables

Follow these steps.

  • First of all, insert a slicer.
  • Right click on the slicer and select "Report Connections".
  • From the dialog box, select all the pivots and click OK.

Now you can simply filter all the pivot tables with a single slicer.


3. External Workbook as a Source

  • In create pivot table dialog box, select “Use an external data source”.
  • After that, go to Connection tab and click on "Browse for more".
  • Locate the file which you want to use and select it.
  • Click OK.
  • Now select the sheet in which you have data.
  • Click OK (Twice).

Now you have a blank pivot table with all the field options from the external source.


4. Refresh Data While Opening a File

  • Click on any of the cells in the pivot table.
  • Right click and click “Pivot Table Options”.
  • Go to “Data” tab and tick mark “Refresh data when opening a file”.
  • Click OK.

Quick note: If you have multiple pivot tables with the same data source then all those will also get updated every time when you open a file.


5. Refresh Data After a Specific Time Interval

If you want to update your pivot table automatically after a specific interval then this tip is for you.

...here's how to do it.

  • First of all, while creating a pivot table, in “Create Pivot Table” window, tick mark “Add this data to data model”.
  • After that, once you create a pivot table, select any of the cells, and go to "Analyze Tab".
  • In Analyze Tab, Data ➜ Change Data Source ➜ Connection Properties.
  • Now in “Connection Properties”, in usage tab, tick mark “Refresh Every" and enter minutes.
  • Click OK.

Now after that specific period which you entered your pivot will automatically refreshed.


6. Preserve Cell Formatting

While updating a pivot table, there is a problem which we always face, it removes your custom formatting.

Let’s say you have highlighted a cell with a color and when you refresh it, Excel will remove that color.

But we can solve this problem using this simple option.

  • First of all, right click on your pivot table and open pivot table options.
  • Now, in “Layout & Format”, tick mark “Preserve cell formatting on update”.
  • In the end, click OK.

Now whenever your refresh your pivot, you will not loose your custom formatting.


7. Disable Auto Width Update

One more thing which most people face on refreshing a pivot table is auto-fit of column width but you can also disable it.

Use these simple steps.

  • First of all, right click on your pivot table and open pivot table options.
  • Now, in “Layout & Format”, tick mark “AutoFit Column Width on Update”.
  • Click OK.

Now your columns will remain same in width every time you refresh your pivot.


8. Replace Error Values

Sometimes, when you have errors in your source data they reflect in the same way in the pivot and this is not a good thing at all.

The better way is to replace those errors with a meaningful value.

Below are the steps to follow:

  • First of all, right click on your pivot table and open pivot table options.
  • Now in “Layout & Format”, tick mark “For error value show” and enter the value in the input box.
  • Click OK.

Now for all the errors you will have the value you have specified.


9. Replace Blank Cells

Let’s say you have a pivot for sales data and there are some cells which are blank.

For a person who is not aware why these cells are blank can question you about this. So, it’s better to replace it with a meaningful word.

...simple steps you need to follow for this.

  • First of all, "right click" on your pivot table and open pivot table options.
  • Now in “Layout & Format”, tick mark “For empty cells show” and enter the value in the input box.
  • Click OK.

Now for all the errors you will have the value you have specified.


10. Repeat Item Labels

When you use more than one items in a pivot table you can simply repeat labels for the top items.

It makes it easy to understand the structure of the pivot table and here are the steps to follow:

  • Select the pivot table and go to “Design tab”.
  • In the design tab, go to Layout ➜ Report Layout ➜ Repeat All Item Labels.

11. Add a Blank Row After each Item

Now let’s say you have a large pivot table with multiple items.

Here you can insert a blank row after each item so that there would be no clutter in the pivot.

Check out this steps to follow:

  • Select the pivot table and go to Design tab.
  • In the design tab, go to Layout -> Blank Rows -> Insert Blank Line after Each Item.

12. Drag and Drop Items

When you have a long list of items in your pivot you can arrange all those items in a custom order by just drag and drop.

13. Creating Many Pivot Tables from One

Suppose you have created a pivot table from month wise sales data and you have used products as a report filter.

With "Show Report Filter Pages" option, you can create multiple worksheets with pivot table for each product.

Let's say if you have 10 products in a pivot filter you can create 10 different worksheets with a single click.

Use these steps:

  • Select your pivot and go to analyze tab.
  • In analyze tab, go to pivot table -> Options -> Show Report Filter Pages

Now you have four pivot tables in four separate worksheets.

14. Hide/Unhide Subtotals

When you add a pivot table with more than one item field you will get subtotals for the main field.

But sometimes there is no need to show subtotals. In that situation, you can hide them using following steps:

  • Click on the pivot table and go to analyze tab.
  • In Analyze tab, go to Layout -> Subtotals -> Do not show subtotals.

15. Hide/Unhide Grand Total

Just like subtotals you can also hide and unhide grand totals and below are the simple steps to do that.

  • Click on the pivot table and go to analyze tab.
  • In Analyze tab, go to Layout -> Subtotals -> Do not show subtotals.

16. Hide Selection

Let’s say you want to hide a specific row(s) from your pivot. So, instead of filter that particular row you can use an easy way.

  • First of all, select that section or row from the pivot.
  • After that, right click on it.
  • In the right-click menu, go to Filter -> Hide Selected Items.

17. Turn OFF GetPivotData

There is a situation where you need to refer to a cell in a pivot.

But, there could be a problem because when you refer to a cell in a pivot Excel automatically use GetPivotData function for reference.

The best thing is, you can disable it and here are the steps:

  • Go to File Tab -> Options.
  • In options, go to Formulas -> Working with Formulas -> untick “Use GetPivotData functions for PivotTable reference”.

You can also use a VBA code for this as well.

Sub deactivateGetPivotData()
Application.GenerateGetPivotData = False
End Sub

18. Filter Top 10 Values

A pivot table is a report in itself and the best thing is it’s dynamic. there are a lot of options to make best out of it.

Filtering top 10 values is one of them. Use these steps:

  • Open filter from the top of the items column.
  • Go to Value Filter -> Top 10.

19. Highlight Top 10 Values

Instead of filtering, you can highlight top 10 values from a pivot table. For this, you need to use conditional formatting.

Steps are below:

  • Select any cell from the value column in your pivot.
  • Go to Home tab -> Styles -> Conditional Formatting.
  • Now in conditional formatting, go to Top/Bottom Rules -> Top 10 Items.
  • Select the color from the window you have got.
  • And click OK.

20. Group Dates in a Pivot Table

Use below steps:

  • First of all, you need to insert a date as a row item in your pivot table.
  • Right click on the pivot table, and select “Group…”.

21. Calculation in a Pivot Table

To become an advanced pivot table user you should learn to create a calculated field and item in a pivot table.

​Let’s say in the below pivot table, you need to create a new data by field multiplying the present data field with 10.

22. Running Total Column in a Pivot Table

Let’s say you have a pivot table month wise sale. Now, you want to insert a running total in your pivot table to show a complete growth of sales in the entire month.

Here are the steps:

  • Right click on it & click “Value Field Setting”.
  • From “Show Values As” drop-down list, select “Running Total In”.
  • Click OK.

23. Add Ranks in a Pivot Table

  • Go to “Show Values as” tab and select “Rank Largest To Smallest”.
  • Click OK.

24. Create a Percentage Share

Imagine you have pivot table for product wise sale. And now, you want to calculate percentage share of all products in the total sales.

Steps to use:

  • First of all, insert the same data field twice in the pivot.
  • After that for the second field, right-click on it and open “Value Field Settings”.
  • Go to “Show Values as” tab and select “% of Grand Total”.
  • Click OK.

25. Insert a Timeline

Here are the steps to insert a timeline with a pivot table.

  • Select any of the cells from your pivot table.
  • Go to Pivot Table Tools ➜ Analyze ➜ Filter ➜ Insert Timeline.
  • From the pop-up box, select the date column and click OK.

26. Get a List of Unique Values

If you have duplicate values in your date then you can use a pivot table to get a list of unique values.

  • First of all, you need to insert a pivot table and then add the column where you have duplicate values as a row field.
  • After that, copy that row field from the pivot and paste it as values.
  • Now the list you have as values is a list of unique values.

27. Reduces the size of a pivot table report

To reduce the size of a pivot table you can try any of the following methods.

28. Convert a Summary Table into a Raw Data

  1. First of all, open old pivot table wizard by using keyboard shortcut key ALT-D P.
  2. Select “Multiple Consolidation Ranges”.
  3. After that, click on “Create a single Page field for me”.
  4. And then pick up the entire range of data and click on the “Add Insert the Pivot Table” on the New Sheet.
  5. Click "Finish".

Once you insert a pivot with this method, click on the grand total cell to get the entire data as a raw data.

29. VBA To Create a Pivot Table

Macro Codes To Create A Pivot Table

30. Top Pivot Table Shortcuts

Below are some of the important keyboard shortcuts which you can use save time while working on pivot tables. 

  1. Alt + N + V: To create a pivot table.
  2. Alt + Shift + Right Arrow: Group selected pivot table items.
  3. Alt + Shift + Left Arrow: Ungroup selected pivot table items.
  4. Ctrl + – : Hide selected item or field.
  5. Shift + Ctrl + = : Open calculated field/item window.
  6. Alt + D, P: Open old pivot table wizard.
  7. Alt + Down Arrow: Open field list for active cell.

2018-05-07T07:09:58+00:00
>