In our previous post, Demystifying Pivot Tables in Excel with Examples – Part 1 , we had a brief discussion about what is Pivot Table, the salient features, stratification using Pivot Table and some use cases.
Following the previous post, we are here with some unknown yet useful features of Pivot that can enhance our work quality on a day-to-day basis. In this post we will be looking into the Pivot Charts, Filters and Slicers, Timelines and the feature of Calculated Fields in Pivot.
Pivot Chart can be found under the analyse ribbon of PivotTable.
Tricks: Shortcut Keys for Pivot Chart- Alt + J + T + C or Alt + N + S + Z + C
Inserting a Pivot Chart:
1. Click any cell on the Pivot Table
2. Select the Pivot chart option from the analyze tab, in the Tools group.
3. Select OK and the pivot chart Appears.
Filters and Slicers in Pivot Chart:
Filters are also available in pivot chart as available in Pivot Tables. It helps in the providing a pictorial representation of the required data as per the user’s needs.
Often, once you create a Pivot table, there is a need you to expand your analysis and include more data/calculations as a part of it. If you need a new data point that can be obtained by using existing data points in the Pivot Table, you don’t need to go back and add it in the source data. Instead, you can use a Pivot Table Calculated Field to do this.
How to Add a Calculated Field?
1. From the Fields, Items & Sets option select the ‘Calculated Field’
2. A Dialogue Box ‘Insert Calculated Field’ would appear as below. Say suppose we need the tax collected on the sales.
a. Enter the name of the new field to be added in the Name Box, say GST.
b. In the Formula Box enter the calculation to be done. Here we assume the tax rate to be 18 %, so we insert the Sales field to the formula by selecting sales and clicking on the ‘Insert Field’.
c. Add the calculation “ *0.18 ” to the formula bar in order to get the GST value.
d. Select Add to insert the new field.
3. The New Calculated field would appear in the Pivot Table Fields and in the Pivot Table.
Things to Remember while using Calculated Field feature:
Pivot Table has the unique feature of identifying dates in the data and present them as Groups such as dates, Months, Quarters and Years. To make it even more advanced Excel provides the feature of Inserting timelines for a data where any such row is formatted as Date.
Features of Timeline
1. Clear Filters: You can use this button to clear all filter.
2. Time Level: From time level option you can use four different time levels. You have days, months, quarters and years to use as time level.
3. Timeline: Use the timeline to filter dates for the period you want. You can filter dates for days, months, quarters and years. You also select more than a day, month, quarter & year but only in sequence.
Say we have a raw data consisting of sales for a period of 2 years for a restaurant operating across different cities. With the help of Timeline feature, the data can be filtered out for the necessary period as required by the end user.
The following will be the default pivot table for sales summary look like without applying the Timeline feature.
Applying the Timeline feature the sales summary can be filtered out for the required time period based on days, months, quarters and year. Following are some examples
Compiled by CA Narasimhan Elangovan with inputs from Nakul Pranav, team KEN & Co. Author can be reached at [email protected] | https://www.ken-co.in.
Disclaimer : This publication contains information in summary form and is therefore intended for general guidance only. All information is provided in good faith. KEN & Co. makes no representation or warranty of any kind, express or implied, regarding the accuracy, adequacy, validity, reliability, availability, or completeness of any information provided. It is not intended to be a substitute for detailed research or the exercise of professional judgment.