A pivot table is one of the most utilized features help to slice and dice data in Excel. With the help of advanced pivot table techniques, you can create powerful reports and perform data analysis.
There is a lot to a Pivot table. Read through the article to know the Advanced Pivot table tricks. This will make your actions easier, faster and more efficient.
Value and Percentage
By default, Excel summarizes your values either by Summing or Counting the items. Let’s say you have a pivot report presenting Sector-wise count of people. You want to know the percentage of headcount in each sector.
To change the type of calculation, Follow the steps mentioned below;
Click on any cell within the Value (numeric) column.
Right-click and select Show Values As >> % of Grand Total
Alternatively, you can select the option Value Field Settings >> Show Values As >> % of Grand Total.
You can also make use of Summarize Values By
Both the options are available once you right-click while within the Pivot Table.
Group & Ungroup
One of the most used features in Pivot is the capability of combining items into groups. You can group items based on Rows or Columns.
NOTE: Grouping options are different for numeric values and text. Below is a representation depicting how to group.
Simply stay on the column or row which you wish to group > Right Click >> Group.
After this, a dialog box will appear as bellow.
Fill in details as required and your output will be grouped based on the entered parameters.
Following are common parameters for grouping
- Group by Date: Grouping when done with dates can be immensely powerful. Within a few second it can provide insights based on Annual, Quarter, Month, etc. Right-click on any cell within the Date column, then select Group and you can create a summarize as per requirements. Into days/months / quarters/years
- Numeric value: Simply right-click and select group. You can fill in details for starting, ending and size and group into segments.
- Text: Text values can be grouped by selecting the items on the report
You can also rename the groups, to do so click on the cell and edit it from the formula bar below the Ribbon. Right-click and select ungroup to ungroup the grouping.
Top 10 Values
This is a brilliant feature and a crucial one in Advanced Pivot Table.
Click on the Dropdown option from the first cell in your pivot.
Then select Value Filter >> Top 10.
Alternatively, click on any cell within the first column of the pivot and then select Filter >> Top 10…
To know more on How to Sort and Filter data in Pivot Table click here.
Filter Two Pivot with One Slicer
Slicer operates exactly like a Filter but is more interactive. You can filter a pivot table or chart by adding a slicer on any field. Did you know that you can link slicer to more than one pivot table? Yes, this is one of the best use of having knowledge on the advanced Pivot table.
To link multiple pivot tables to same slicer follow the below steps:
- Right-click on the slicer and select “Report connections”
- Check the box next to pivot tables that you want to connect.
Pivot Table enables calculation like count, sum, average and median from our data. But what about distinct counts?
You can make use of data model feature in Pivot tables to get distinct count.
The steps are as follows:-
- Select your data >> insert >> pivot table.
- On that window box, click on the option “Add to data model”.
- Click OK.
- Add the field for which you want to make a distinct count.
- Go to value field settings and select summarize by Distinct count.
This is an important benefit which most fail to make use of while learning the Advanced Pivot Table.
Interactive Pivot Charts
The beauty of being skilled with Advanced pivot table knowledge enables you to create a chart from the pivot within clicks. Follow the steps to create a pivot chart.
Stay within the Pivot table then click on PivotTable Analyze from the ribbon and click on the Pivot Chart. Below is a representation of a Pivot chart.
Notice the Sector Option which enables you to filter. This makes it interactive and different from the normal charts. Moreover, to make it even more attractive you can add a slicer or timeline from the PivotTable Analyze tab,
REMEMBER : Your data must not have blanks in place of headers.
There is more to Advanced Pivot Table, like Dax Functions, power pivot, calculative field etc. The best way to learn is to get your hands dirty.
New to Pivot Table gain an overview and know how to create a pivot table.
If you enjoyed these tricks on Advanced Pivot Table you will enjoy the video session on Pivot Tables too. To know more click here.