Hello all. Let’s look into some advanced features / functions of Pivot table. As Pivot table can do multiple things, I have broken the advanced section in to two parts. If you haven’t gone through the Pivot table 101 (introduction) section, please check out here.
We will be using the below dataset. Revenue is nothing but unit cost (sale price) * Sale unit (number of units sold).
First, let’s look at use of Slicer. Slicer is a power tool that act as a filter for one or more pivot tables. If you have more than one pivot table, you don’t need to add filters in each and every table – rather, the slicer (once connected with all the tables) can act as a common filter. Let’s look at the below example
From the same data set, I have created 2 x pivot tables. One that shows the total sales based on the product and 2nd total sales in the region. Now, let’s filter the data based on the specific product and the region (for instance, what is the total sales for Swift in the West region). This can be done by Slicer.
Click anywhere on the pivot table -> Under PivotTable Analyze bar, click Insert Slicer -> Select Product and Region.
Right click on each of the slicer bar -> Report Connections -> Select the appropriate table names. This would make the required connections between the two tables.
That’s all – all we need is to click the product and the region (Swift and West, in our example). We get the data based on our selection
You can play around the filter with just a click on the buttons in the slicer bar to get interactive data segregation.
Next, let’s look at automated timelines within pivot table. Please note this would work only when the table has a date field that’s formatted as “Date”. Let’s segregate the revenue numbers based on each quarter in this simple table below:
We don’t have to drag any date field, instead click anywhere on the table -> under the PivotTable Analyze tab, click Insert Timeline. This would bring the Insert Timeline dialog – click on “Date”. Just click on the month for which you need the sales number. Click on the Months drop down in the timeline window to group the data into years / months / quarter / Days. All shown in the video below.
Next, let’s look at how to create multiple tables from a single pivot based on a filter value. For instance, we have the below table and we need to create 4 different tables one for each quarter.
First, let’s drag the “Date” field to the filter section of the PivotTable Fields. Click anywhere in the table -> Under the PivotTable Analyze tab, click on the Pivot table options -> Show Report Filter Pages -> Click Date and hit Ok. This will create separate sheets with each quarter sales table. All shown below
Let’s look at how to change the layout options. We will be working on the below pivot table with product and sub-type as variant and the respective sales values.
Here, we have the variants and the product in the same column – this doesn’t come handy if you want to copy this table for further use. Let’s bring them in separate columns. Click anywhere on the table -> Design Tab -> Report Layout -> Show in Outline form. Result will be
The data currently has the subtotals in the top. You have the option to remove the subtotals or to bring the subtotal at bottom. Click anywhere on the table -> Design tab -> Subtotals -> Choose “Do not show subtotals” or ” Show all subtotals at bottom of group” for turning off and changing positions respectively.
We still have few more things pivot table can do for us. I shall cover those in Part 2 of Pivot table advanced. Please let me know your feedback / thoughts on this topic. Thanks for reading.