5 Useful Features of Excel Pivot Tables



For data analytics, I use tools like Power BI, Tableau, and Python. However, MS Excel is still easily my go-to tool for quick analysis. And, the pivot table proves handy in this aspect. This article covers the top 5 features I find useful.

Grouping

Grouping is another form of “Divide & Rule”. Instead of analyzing complete data, we divide it into different groups and then analyze performance in parts. The pivot table makes this job easy. We can group number and date fields


Sample Data file


It is a three-step process:


Step 1: Drag the required number field to Row field

For this example, we create groups for Order Quantity. Add Order Quantity to Row section:



Step 2: Create Group

Right-click on any cell > Group



Step 3: Adjust the group setting

By default, the pivot table takes the minimum and maximum values as the starting and endpoints, respectively.








We can adjust this, as per requirement. Click Ok.


Pivot table creates a group on Order quantity, with each category length 10 units







Now, we can use it as a category field to analyze results:











Or, we can use it as a filter:



Another common grouping used is a grouping of a date field by Year, Quarter, and Month.











Date field grouped













Value Field Settings

Value Field Settings comes with a plethora of options that can improve efficiency significantly.


By default, when we drag a text/category field under Values, it shows applies count. In case of a number field, the pivot table applies sum aggregation:


We can use aggregations like minimum, maximum, average, standard deviation (for population & sample), variance (for population & sample), count number.

Just drag the number field under Values > Click on the Options drop-down > Value Field Settings


Summarize Values By > select the required calculation




Value Field Settings offers other in-built calculations like % of column or row, running total, rank, and more.

Please refer to the following article on more details on Value Field Settings

Top & Bottom N filter

Filtering with top & bottom rules comes handy in a pivot table. Consider the following design:


To display only top & bottom 3 products based on Ship Mode selection, we implement the following steps:

Click on the Drop-down option on Pivot table > Value Filters > Top 10



Update the setting box accordingly:


In case, we have multiple value fields, select the field on which we want to filter.

For Bottom 3, change the option from Top to Bottom:


Using a slicer, we can create a dynamic report with zero formulas.


Slicers & Timelines

Introduced with Office 2013, Slicers are a modern way of filtering a pivot table. It is more convenient to use and super-easy to add. Usually, we choose categorical data as a slicer. In this case, we are adding Ship Mode & Order Priority


How to add slicer in a pivot table?


Right-click on the field name > Add as Slicer


Alternatively, click on the pivot table we want to add slicer > Pivotable Analyze > Insert Slicer


It presents a list of all the fields in the table. Select the fields for which we want to add the slicer:


















How to insert a Timeline in a pivot table?


It is identical to inserting a slicer.


Select the pivot table > PivotTable Analyze > Insert Timeline


Select the required date field














Timeline gives the option for Year, Quarter & Month


Tip: You can use Slicer setting to update the interaction settings:

Select the slicer > Slicer Option > Report Connection


Use the checkbox to connect or disconnect multiple pivot tables with the current slicer.


Power Pivot

Introduced with Excel 2013, Power Pivot is a robust tool. It is a little different from the usual pivot tables, as it works with relational data tables and has its language for calculation(DAX). When combined with Power Query, it provides an excellent capability of advanced data modeling in Excel. Following articles covers a few essential aspects of Power Pivot:


Data Modeling with Power Pivot


@imVivRan

Drop Me a Line, Let Me Know What You Think

 

contact@vivran.in

+91 9871-641-146

Join WhatsApp group: BI Simplified

MS EXCEL || POWER BI || POWER APPS || POWER QUERY || TABLEAU || DATA ANALYTICS || TRAINING