How to Create and Use PivotTables in Microsoft Excel (Part 2) [Guide]

In Part 1 of this guide, you learned how to prep your data and then create and build your PivotTable. In Part 2, you will learn how to aggregate your data, format your PivotTable, and use Slicers and Timelines.

Aggregated Data

The fields that are added into the Values area are automatically aggregated. Which function is used for the aggregation depends on what the data type of the field is. If the data type is numeric, then the default aggregation is Sum. If the data type is text or date, then the default aggregation is Count. You can always change what function you want to use by clicking on the drop-down of the field in the Values area and selecting Value Field Settings.

Then choose the function.

NOTE: If you want multiple aggregations for the same field (SUM & AVERAGE) you can drag a second copy of the field into the Values area and then change the function.

Sorting & Filtering the Data

There are several ways to sort and filter the PivotTable. One option is to use the drop-down arrow that automatically appears when you add a field into the Rows area OR in the Filters area.

Another option is to use the drop-down arrow in the PivotTable Fields display box.

Formatting PivotTables

There are also several options available to format the PivotTable to change the appearance for better readability. One way is to use the Design ribbon (you must click INSIDE the pivot table to see the Design ribbon). You can customize how Subtotals and Grand Totals appear, you can change the Report Layout, you can customize how blank rows appear, and you can change the style of the table.

You can also change the column headers in the PivotTable by selecting the cell (for example: Row Labels) and change the text in the formula bar.

In this example, we changed the style to Light Blue Medium, and we changed the columns headers.

Using Slicers and Timelines

Slicers and timelines are interactive elements that allow you to filter the data quickly. They are only available in Tables and PivotTables. Slicers are used on various data fields and Timelines are used on date fields only and enable you to filter by time periods.

To insert a slicer:

1. Place your cursor within the Table or PivotTable
2. Click on Insert --> Slicer
3. Select the fields that you want to create slicers for

In this example, we created a slicer for SALESORDERID and PRODUCTID. We then moved and resized the slicers.

You can then click on the buttons in the slicer to “slice” or filter the data. You can use Ctrl+click to select multiple buttons which will then run and OR statement (For example: BX-1012 OR BX-1013). Each slicer also has a clear slicer button in the top right corner so you can easily clear the filter and show all the data. You can remove a slicer from the screen by right+clicking on the title of the slicer and selecting the Remove option.

To insert a timeline:

1. Place your cursor within the Table or PivotTable
2. Click on Insert --> Timeline
3. Select the date fields that you want to create a timeline for
   

In this example, we created a timeline for the CREATEDAT field.

You can then click on a time to filter by that (for example: SEP). You can also change the periods to Years, Quarters, Months, or Days.

Refreshing the Data

Pivot tables will need to be refreshed whenever the data is changed or updated. You can easily refresh it by right+clicking anywhere in the PivotTable and selecting
Refresh. You can also update ALL pivot tables by using the Refresh All button found underneath the Refresh button on the PivotTable Analyze ribbon.