top of page
Search

# 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

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:

bottom of page