Excel Pivot Table Tips & Tricks
Pivot Tables:
Pivot tables in Excel are considered as the most important tool. A pivot table allows the users to organize and summarize the selected columns of data to develop a required analysis report. Using pivot table user can do quick data analysis and it is quite easy to use as well.
In this article, I am going to write about top 10 tips and trick of Pivot Table for Advanced Excel Users.
Pivot Table Tips & Tricks:
For Pivot Source Data use Excel Table:
As the pivot table doesn’t change the database itself whenever the source data gets changed. Therefore, it is advisable to convert your data source into a table. The reason being is one of the properties of the Table is to expand itself whenever new rows are being added. This allows the pivot table to expand its source automatically by refreshing it.
To convert data source into Table:
- Select entire data
- Press Ctrl + T
- Click “OK”
Refresh Pivot Table data when opening the File:
In pivots, the user can enable the functionality of refreshing the pivot table data when the excel file is being opened. Following are the steps to enable that functionality:
- Right-click on any of the cell of the pivot table and select “Pivot Table Options”
- Select “Data” tab and tick mark “Refresh data when opening a file” option
- Click “OK”
Single Slicer for more than 1 Pivot Tables:
Different Pivot Tables can be connected together and controlled through slicers. Following are the steps to do that:
- Insert a slicer
- Do a right-click on the Slicer and select “Report Connections..”
- In the dialog box, select all the Pivot Tables and then click “OK”
Preserve Cell Formatting on Update:
One of the default functionalities of Pivot Table is, whenever the data gets updated, the custom formatting gets removed automatically. This can be resolved by following steps:
- Right-click on any of the cell of the pivot table and select “Pivot Table Options”
- Select “Layout & Format” tab and tick mark “Preserve cell formatting on update” option
- Click “OK”
Autofit Column Widths on Update:
Another default functionality of Pivot Table is, whenever the data gets updated, column widths gets autofitted automatically. This can be disabled by following steps:
- Right-click on any of the cell of the pivot table and select “Pivot Table Options”
- Select “Layout & Format” tab and untick mark “Autofit column widths on update” option
- Click “OK”
Error Values in Pivot Data Table:
Pivot Table provides an option to tackle with error values which are present in the dataset and replace those error values with the default value. Following are the steps to set a default value for all the Error values:
- Right-click on any of the cell of the pivot table and select “Pivot Table Options”
- Select “Layout & Format” tab and tick mark “For error values show” option
- Set the default error value in the adjacent box
- Click “OK”
Blank Values in Pivot Data Table:
Similarly like error values, Pivot Table provides an option to tackle with blank cells as well and replace those blank cells with the default value. Following are the steps to set a default value for all the blank cells:
- Right-click on any of the cell of the pivot table and select “Pivot Table Options”
- Select “Layout & Format” tab and tick mark “For empty cells show” option
- Set the default value in the adjacent box
- Click “OK”
Repeat All Item Labels:
In the pivot table when there are more than one items, the user can easily repeat the labels of top items. It helps to understand the structure of pivot table in the more better way.
Initial Structure:
Final Structure:
Following are the steps to do that:
- Select the Pivot Table and go to “Design” tab, which is shown at the top
- In the design tab, go to Layout ➜ Report Layout ➜ Repeat All Item Labels
Insert Blank Line after Each Item
In pivot table when there are more than one items, a user can insert a blank row after each item. It helps to avoid clutter in the pivot table.
Initial Structure:
Final Structure:
Following are the steps to do that:
- Select the Pivot Table and go to “Design” tab, which is shown at the top
- In the design tab, go to Layout ➜ Blank Rows ➜ Insert Blank Line after Each Item
Hide selected value in Pivot Table:
In the Pivot Table, besides having the filter option to filter out the data, there is also an option to hide the specific selected rows. Following are the steps to hide row in pivot table:
- Select the required rows which are needed to be hidden and then do a right click
- In the Right Click menu, go to the “Filter” option and select “Hide Selected Items” option
