Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.

Worth $20, Absolutely Free

Pivot Table Tips & Tricks

No doubt, Pivot Tables are 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 about pivot tables is they are powerful and flexible.

​Even when you compare formulas and pivot table, you find, pivot tables are easy to use and manage. So, if you want to take your skills to a whole next level you need to get better in pivot tables.

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

So, let's get started.

1. Use Table for Source Data

Million Dollar Tip

Before you create a pivot table, you should convert your source data into a table. A table expands itself whenever you add new data into it.

This will allow your pivot to expand its source automatically by just refreshing it. So there is no need to update source range again and again. 

Three steps:

  1. Select your entire data or any of the cells.
  2. Press shortcut key Ctrl + T.
  3. Click OK.

It will convert your data into a table. And, now you can insert a pivot table with this data.

2. Single Slicer for all the Pivot Tables

Super Useful for Dashboards

Sometimes when you have multiple pivot tables, it’s hard to control all of them. But, if you connect a single slicer with them, you can control with no efforts.

Easy 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 filter all the pivot tables with a single slicer.

3. External Workbook as a Source

I Love This Tip

Let’s say, you want to create a pivot from a workbook which is in a different folder and you don’t want to add data from that workbook into your current sheet.

Then, you can link that file as a source without adding data into the current file.

Steps:

  • In create pivot table dialog box, select “Use an external data source”.
  • After that, Choose Connection -> 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 and you can create a pivot with it.

4. Refresh Data While Opening a File

One Time Set Up

This is a one-time setup you can do to make your pivot table refresh every time when you open your file. With this, you will get the real updated pivot table every time.

Here how you can do this:

  • 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

Super Advance Tip

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

Follow these steps:

  • 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 -> Conncection 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

Big Problem, Small Solution.

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 when you refresh it, Excel will remove that color.

Use these steps:

  • 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

A Big Time Saver

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

Here are the 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

No More Errors

Sometimes, when you have errors in your source data they reflect in the same way in the pivot. And, that’s not a good thing at all.

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

Below are the steps:

  • 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

Blank Cells < Meaningful Value

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

Less Confusion

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.

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

Make Everything Easily Understandable

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 & Drop Items

Yes, Just Drag & Drop

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

Separate Report with a Single Click

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

Simple Report

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

Like a Cross Tab Data

Just like subtotals you can also hide and unhide grand totals. 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

Instantly

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

No Distractions

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 should disable it. 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

==> a list of 101 useful macro codes examples

18. Filter top 10 values

Instant Insights

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.
  • This will give you a window to select the options to filter top 10 values (You can also filter bottom values).
  • After that, click OK.

It will instantly filter only top 10 values for the item where you have applied the filter.

19. Highlight Top 10 Values

Using Conditional Formatting in a Pivot Table

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

A Super Time Saver

Just imagine, you want to create a month wise pivot table but you have dates in your data. In this situation, you need to add an extra column for months.

But the best way is to create using grouping dates methods in the pivot table. using this method you don’t need to add a helper column.

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…”.
  • Select “Month” from by section and click OK.

It will group all the dates into months. Here is a complete guide to grouping dates in a pivot table.

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.

​In this situation, instead of creating a separate column in a pivot table you can insert a calculated item.

==> a complete guide to creating a calculated item and field in a pivot table.

22. Running Total

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.

Learn more about adding a running total in a pivot table.

23. Add Ranks

Ranking gives you a better way to compare things with each other. and, to insert a rank column in a pivot table you can use following steps:

  • 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 “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

If your data is more about date and time then you can insert a timeline filter with your pivot table. It will help you to filter dates in a better way.

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.

  1. First of all, you need to insert a pivot table and then add the column where you have duplicate values as a row field.
  2. After that, copy that row field from the pivot and paste it as values.
  3. 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.

  1. Copy-Paste instead of creating from scratch: Sometimes when you need to create a more than one pivot table from the same data source, the best way is to copy-paste a first pivot and them make changes in it. In this way, you will have a single pivot cache for all the pivot tables for same data source.
  2. Delete Source Data: If you have a pivot table and a source data in a single workbook, you can delete the source data before sending that file to someone. When you create a pivot Excel stores entire source data in a cache. And, if someone needs source data he/she can get it by clicking on the grand total cell of the pivot table.
  3. Duplicate Cache Finder: If you already have a lot of pivot tables in your workbook, you can use the DuplicateCacheFinder to find all the duplicate pivot caches. And then you can delete all those duplicate caches and reduce the size of your workbook.

28. Convert a summary table into a raw data

You can also use a pivot table to unpivot a data table. Here are the steps:

pivot table tricks to unpivot data with a pivot table

Source: http://www.goodly.co.in/how-to-re-arrange-data-for-a-pivot-table/

  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

You can also use a macro code to create a pivot table. You need a single click to when you need to insert it.

Macro Codes To Create A Pivot Table

Check out this step by step guide for this.

30. Top Pivot Table Shortcuts

  1. Alt + N + V: To create a pivot table.
  2. Alt + Shift + Right Arrow: Group selected pivot table items.
  3. Alt + Shift + Left Arrow: Un-Group 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.