4 Advanced PivotTable Functions for the Best Data Analysis in Microsoft Excel
Free to use
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.
Sign up forfree
Forgot your password?
Create an account
*Required: 8 chars, 1 capital letter, 1 number
By continuing, you agree to thePrivacy PolicyandTerms of Use.You also agree to receive our newsletters, you can opt-out any time.
If you are already familiar with PivotTables and are looking to go a step beyond with some advanced functions, you could 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 theInserttab at the top, followed by thePivotTableoption.
SelectFrom Table/Rangeoption.
Since you selected the data range before inserting the pivot, the data range is automatically updated in the pivot dialog box. you may choose the destination of the pivot table—you can place it on an existing worksheet or a new sheet.
Once you click onOk, your pivot is ready to review. You can add the necessary row/column/value views from the field list.
Alternatively, you canautomatically create pivot tables in Excel with VBAto 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 releasenew Excel functionsto 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 thePivotTable Analyzetab, followed by theInsert Sliceroption.
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, it’s possible to create a calculated field within your pivot table.
To create a calculated field, click on your pivot table and navigate to thePivotTable Analyzetab. Click onFields, Items, & Setsbutton from the ribbon.
Click onCalculated Fieldfrom the dropdown menu. In the dialog box, within theNamecolumn, assign a column header. Next, from theFieldslist, select the column names you want to use within the formula.
Look for theSalesoption. Click on theInsert Fieldbutton. The sales option is automatically added to theFormulacolumn. Similarly, you can clickQuantity, followed by theInsert Fieldbutton.
Add anasterisk(*) symbol between the two field names within the formula bar. Click on theAddbutton.
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 thePivotTable Analyzetab at the top ribbon menu. Click on theRefreshbutton’s dropdown menu. SelectRefresh Allfrom 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.
4. Group Related Fields Together
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 theGroupfunction to group sub-categories together and rename the group to make meaningful insights.
To create a group, press thectrlkey andselect multiple itemsfrom the items list in one go. Right-click within the pivot table and click on theGroupoption.
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 theUngroupoption.
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.
Go beyond Microsoft Excel and learn how to create pivot tablets with Python and just a few lines of code.
My iPhone does it all, but I still need my dumb phone.
The fix was buried in one tiny toggle.
OneDrive is one of the best, but it has a catch.
Now, I actually finish the books I start.
You don’t need to fork out for expensive hardware to run an AI on your PC.
