You shouldn’t underestimate pivot tables in Excel. That’s because Excel’s PivotTables will deliver everything you’ll need for data analysis. From condensing rows of data to deriving sums and grouping similar columns, there is a bit of everything in pivot tables.
However, data analysis may not be as straightforward as it seems. So, to enhance your data, you can use a series of advanced Excel PivotTable functions to enhance your data, making your life a little easier.
If you are already familiar with PivotTables and are looking to go a step beyond with some advanced functions, you can check out the following advanced PivotTable functions.
Preparing Your Data Set
To understand the advanced functions, you must first prepare the initial dataset. For convenience, let’s use the following rows and columns to illustrate the advanced PivotTable functions.
You can download this data directly from the source to practice the functions listed below.
Download: Sample Superstore (Free)
How to Make a Pivot Table in Excel?
Select the data for your Excel PivotTable. To insert a pivot table, click on the Insert tab at the top, followed by the PivotTable option.
Select From Table/Range option.
Since you selected the data range before inserting the pivot, the data range is automatically updated in the pivot dialog box. You can choose the destination of the pivot table—you can place it on an existing worksheet or a new sheet.
Once you click on Ok, your pivot is ready to review. You can add the necessary row/column/value views from the field list.
Alternatively, you can automatically create pivot tables in Excel with VBA to save time and effort.
1. Slice and Dice Data with Slicers
Slicers were introduced as a part of the Microsoft Excel 2010 suite and are available in the higher MS Office versions. Microsoft continues to release new Excel functions to help users make the most of their regular data tasks.
Like some other Excel functions, even slicers are an excellent way to control your pivots via an external pivot filter.
Unlike the in-built pivot filter, you can customize slicers and display the contents at all times. Users find slicers quite effective since you can filter multiple items and display them for everyone to see.
Adding Slicers in a PivotTable
To add a slicer to your pivot table, click on your pivot table and navigate to the PivotTable Analyze tab, followed by the Insert Slicer option.
Once you click the Slicer button, you can choose one or more columns to use as a slicer. All the options you choose are shown in separate filter boxes next to the pivot for convenience. The slicer items filter the pivot table and display whatever is relevant.
A slicer is beneficial, especially when you want to control multiple pivot tables simultaneously with slicer (filter) windows.
2. Working With Calculated Fields
Calculated fields are an excellent way to add calculations to your pivot table, which are essential when working with multiple numeric columns, to calculate new values on the fly.
Instead of struggling with the PivotTable options, you can create a new column(s) within your pivot table. Whenever you make any changes to the inherent data, the changes will reflect automatically in the pivot table. However, you need to refresh the pivot table first.
How to Create a Calculated Field in Your Pivot Table
Suppose you want to calculate the total sales for each sub-category. The apparent formula is to derive the product of the sales and the quantity fields.
To perform such calculations efficiently, you can create a calculated field within your pivot table.
To create a calculated field, click on your pivot table and navigate to the PivotTable Analyze tab. Click on Fields, Items, & Sets button from the ribbon.
Click on Calculated Field from the dropdown menu. In the dialog box, within the Name column, assign a column header. Next, from the Fields list, select the column names you want to use within the formula.
Look for the Sales option. Click on the Insert Field button. The sales option is automatically added to the Formula column. Similarly, you can click Quantity, followed by the Insert Field button.
Add an asterisk (*) symbol between the two field names within the formula bar. Click on the Add button.
Within the pivot table, the newly created column is available.
Similarly, you can create multiple calculated fields within your pivot tables to enhance your calculations.
3. How to Refresh Multiple Pivot Tables
When you are dealing with complex data, there is a high possibility you will create multiple pivot tables simultaneously.
Depending on your data’s structure, you might prefer to place a few pivots on one sheet, while many others can be on the other sheets within the same workbook.
There are two ways you can refresh multiple pivot tables within your workbook. The first option is to use Excel VBA to automate the refresh process.
Alternatively, you can also do this manually. Click on any pivot table, and navigate to the PivotTable Analyze tab at the top ribbon menu. Click on the Refresh button’s dropdown menu. Select Refresh All from the list.
You will notice all the pivot tables in the workbook refresh as soon as you click this button. Any new changes will reflect in the pivot table.
You often want to group specific data points so end-users can understand the data structure better. As an expert, you probably understand the data well and how it relates to broader categories. On the contrary, end-users might often struggle to understand these groupings within pivot tables.
To ease the process, you can use the Group function to group sub-categories together and rename the group to make meaningful insights.
To create a group, press the ctrl key and select multiple items from the items list in one go. Right-click within the pivot table and click on the Group option.
Excel creates default groups, which you can edit by selecting multiple items.
Once the groups are ready, you can rename the group names per your needs.
To remove the groups, right-click in the pivot table, and select the Ungroup option.
Using Advanced Pivot Functions in Excel
Pivot table functions aren’t restricted to Microsoft Excel only. Many programming languages offer such functionality, and Python is no exception to this rule.
You can use pivot tables in Python and play around with its unique features to manipulate and analyze your data effectively. Rest assured, Python provides many features to make your work easier and faster.