How to Use Advanced Pivot Table in Excel (25 Tips & Techniques)

In this article, I have illustrated 25 powerful techniques and features of PivotTable. I have mostly covered advanced levels of Pivot Table in Excel. If you have learned the basics of PivotTable properly, this article will take you one step closer to mastering them in Excel.

Advanced PivotTables in Excel significantly increase data analysis capabilities. It makes the work more efficient and insightful. These techniques include creating calculated fields, calculated items, custom formulas, adding slicers or timelines, etc. Moreover, PivotTables can be used to create charts and graphs which present data in a more attractive way.

I have described some essential techniques of advanced PivotTable in simple ways. Read through the article to learn more about PivotTable.

Download Practice Workbook

You are recommended to download this workbook for practice.

PivotTable: Basic Things

This section is especially helpful for beginner users of Excel. Specifically, if you have heard the name PivotTable, but don’t know the dynamic uses of it, even the process of creating it in Excel.

Basically, PivotTable is a dynamic data analysis tool in Microsoft Excel. It helps users to summarize, organize, and gain insights from large datasets quickly and efficiently. It can transform raw data into a more meaningful and compact format.

Basic Components of a PivotTable

• Data Source: The data source is the original dataset from which the PivotTable will be created. It is essential to have well-organized data with column headers, as the headers will be used to define the fields in the PivotTable.
• Field List: Field List will appear on the right side of your screen when you create a pivot table. This includes all the fields that are available in the dataset you used. You can simply drag and drop the fields to the desired areas.

You can show or hide the Field List by selecting and deselecting the Field List option from the PivotTable Analyze tab.

• Rows and Columns: In a PivotTable, you can drag and drop fields from the data source into the “Rows” and “Columns” areas. These fields will determine how the data is organized and displayed in the PivotTable.
• Values: The “Values” area contains the numerical data you want to summarize or analyze. You can choose various summary functions, such as sum, count, average, minimum, maximum, etc., to perform calculations on the data.
• Filters: The “Filters” area allows you to add fields that act as filters. By selecting or deselecting filter options, you can update the PivotTable results.

What Is the Use of PivotTable?

• PivotTables are particularly useful when dealing with large datasets. Because they provide a quick and efficient way to extract meaningful information. There is no need for complex formulas or manual data manipulation.
• PivotTable summarizes large datasets by displaying data in a compact and understandable format. You can perform operations like summing, averaging, counting, finding maximum or minimum values, etc.
• Advanced level of PivotTable in Excel helps users to explore data trends, patterns, and relationships. It is easier to gain insights and make data-driven decisions.
• PivotTable provides various filtering options. It allows you to focus on specific subsets of data. This filtering helps in drilling down into relevant information. Moreover, it excludes data that is not necessary for your analysis.
• One of the significant advantages of PivotTable is their flexibility. Users can quickly change the arrangement of rows and columns to view the data in different forms.
• When your data changes, PivotTable updates with just a few clicks. This dynamic nature of advanced Pivot Table in Excel saves time and effort needed to manually update complex formulas.
• PivotTable can create instant charts and graphs based on the summarized data. It enhances the visual representation.

How to Create a Pivot Table in Excel

Let’s learn how to create a Pivot Table initially. Then I will describe the advanced level of Pivot Table in Excel.

This is a large dataset, which I will use to create a PivotTable.

 1. Select Table/Range Option

• To begin, select any cell of the dataset.
• Then open the Insert Tab >> select PivotTable >> click on From Table/Range.

• PivotTable from table or range dialog box will open up.
• The Table/Range will be automatically set as you clicked the cell of the dataset previously.
• Then, if you want to get the PivotTable in a New Worksheet, click on it and press OK.

• Now, you have to choose fields to add to the report.
• You can simply drag the fields and place it under Filters, Columns, Rows and Values.

• I have selected Country and Title as Rows, Gross Revenue and Budget as Values and Genre as Filters.

• As a result, a PivotTable is created successfully.

2. Apply Keyboard Shortcut

• To create a PivotTable by applying keyboard shortcut, press Alt + N + V + T.
• Hence, the PivotTable from table or range dialog box will appear. Now, do the same things as I did in the earlier method.

Benefits of Using Advanced Techniques in Excel Pivot Table

• Using advanced techniques of Pivot Table in Excel can significantly enhance your data analysis capabilities. This makes your work more efficient and insightful.
• Advanced techniques allow you to perform more complex data analysis tasks, such as creating calculated fields, calculated items, and custom formulas. These functionalities enable you to extract deeper insights from your data.
• PivotTable offers basic summary functions like sum, average, count, etc. However, advanced techniques allow you to create custom calculations using formulas. This helps you to perform specific calculations for your analysis.
• With advanced techniques of Pivot Table in Excel, you can build more sophisticated data models in PivotTables. You can combine multiple data sources, use Power Query to shape and transform data, and create relationships between tables.
• Advanced PivotTable features help you to create dynamic reports that update automatically when the source data changes.
• Slicers and timelines are advanced filtering tools that provide an interactive way to filter data within Pivot Tables in Excel.
• PivotTables can be used to create charts and graphs. It helps you present your data in a more intuitive manner.
• Advanced techniques in PivotTables enable you to explore your data at different levels. You can drill down into details to learn more about specific data points. This is useful for analysis.
• PivotTables can automatically group date and time data into various intervals, such as months, quarters, or years. This simplifies time-based analysis and provides a clearer view.
• Once you become proficient in using the advanced techniques of Pivot Table in Excel, you can save a lot of time and effort while performing data analysis tasks. This efficiency will help you to focus on results and make data-driven decisions.

Advanced Pivot Table in Excel: 25 Tips & Techniques

I will demonstrate 25 advanced techniques of Pivot Table in Excel. These techniques will not only make your Excel experience better but also save your time and effort.

1. Use Slicers to Filter Data with Single Click

Following is the PivotTable which I will use to demonstrate the advanced tricks of using PivotTable in Excel.

• First, click on any cell of the PivotTable.
• Then, click on the Insert tab >> select Slicer.

• The Insert Slicers dialog box will pop-up.
• Now, select the field based on which you want to filter the PivotTable.
• Here, I have selected Country for my table.
• Then press the OK button.

• Therefore, a Slicer is added beside the PivotTable. By selecting different countries, you can filter the PivotTable
• The most amazing part is that you can select multiple countries as filters.

2. Add Timeline to Filter Date/Time with Aesthetically Pleasing

In my dataset, there are releasing dates of movies starting from the year 1920 to 2015. If I want to filter my data based on the releasing years. Timeline is an advanced feature of Pivot Table in Excel which filter data based on time. I can do it by adding timeline. Here are the quick steps:

• To add a Timeline, first select any cell of the PivotTable.
• Click on the Insert tab >> select Timeline.

• The Insert Timelines dialog box will open.
• As my dataset has only one field with time values, which is Release Date, it is showing that.
• Lastly, press the OK button.

• A timeline will be added to the PivotTable.
• Now, from the dropdown, select how you want to show the timeline. I have selected YEARS.

• Now, you can select a year and filter the values for that particular year.

• You can also select multiple years in the Timeline, and the values will change according to your selected timeline.

3. Change Number Format

Do you know that you can customize the number format inside the PivotTable? Believe me, I didn’t know it!

• Initially, right-click on any cell of the column for which you want to change the Number Format.
• Then, from the options, select Number Format.

• The Format Cells dialog box will appear.
• Here, I have selected Accounting as Category and set the Decimal places to 0. You have to select the appropriate Category for your Field.
• Lastly, click on OK.

• Therefore, you can see that the Number Format has changed.

4. Sort Items from the Context Menu

• First, right-click on any cell of the column for which you want to sort the Items.
• Then, select Sort >> click on More Sort Options.

• In the Sort By Value dialog box, select Sort options and Sort direction. I have selected Smallest to Largest and Top to Bottom
• Then click on OK.

• Consequently, the table is sorted according to the Sum of Gross Revenue column’s Smallest to Largest value.

5. Custom Sort Items

In the following PivotTable, the items of the column Row Labels are sorted alphabetically. But if you want to sort the items according to your preference, you can do this with some simple steps.

• To begin, write the items according to your custom sort order in a column of the same worksheet.

• Then, click on the File tab.

• Now, click on Options.

• At this moment, Excel Options dialog box will appear.
• Select Advanced >> from General >> click on Edit Custom Lists….

• Thus, the Options dialog box will open.
• Give the cell reference of the custom sort list (written previously in the worksheet) in the Import list from cells You can also write the items manually under the New entries box.
• Then press Import >> OK.

• Hit the OK button when the Excel Options dialog box appears.

• Right-click on any cell of the column which you want to sort.
• Then, click on Refresh.

• As a result, the items of the Row Labels column have been custom sorted.

6. Create/Remove Calculated Field

Creating calculated field is an advanced feature of Pivot Table in Excel. It is such a smart technique in many ways. If you want to calculate different parameters without writing complex formulas, you can try this tip.

6.1 Create Calculated Field

• Firstly, click on any cell of the PivotTable.
• Then select PivotTable Analyze tab >> Calculations >> Fields, Items, & Cells >> Calculated Field….

• A dialog box named Insert Calculated Field will appear.
• Now, set a relevant name for the calculated field. I set it to Gross Profit.
• Then, from Fields, select the fields you want to use for the formula and press Insert Field. I have calculated the Gross Profit by subtracting the Budget from the Gross Revenue.
• After checking your formula, press the OK button.

• Therefore, a new column is inserted to the existing PivotTable.

6.2 Remove Calculated Field

• To remove the Calculated Field, open the Insert Calculated Field dialog box by following the same steps of creating calculated Field.
• Firstly, click on the dropdown. Then choose the field you want to delete.
• Lastly, press Delete >> click OK.

• Therefore, the calculated field has been deleted.

7. Show Difference Between two Columns

You can easily calculate the difference between two columns in PivotTable without writing any formula. Follow the quick steps of doing this:

In this dataset, there are Gross Revenue for the years “2014” and “2015”. I will show how to calculate the difference of Gross Revenue for these two years.

• Click on any cell of the PivotTable.
• Then select Design >> Grand Totals >> Off for Rows and Columns.
• As I don’t need grand totals for showing difference, I turned it off.

• Then, add the Gross Revenue to Values for the second time. It will be used later to show difference.

• Thus Gross Revenue has been added second time.

• Now, right-click on any cell of that newly added Sum of Gross Revenue2 column.
• Select Show Value As >> Difference From….

• Show Values As dialog box will appear. Select Years (Release Date) as the Base Field.
• And in Base Item, select previous as I want to know the difference from the previous column.

• As a result, the difference is calculated.

• Lastly, edit the name of the column as “Difference” and hide the unnecessary column.

8. Show Percentage of Grand Total

Here, I want to determine the Total Reviews as Percentage of Grand Total. Follow the steps.

• To begin, right-click on any cell of the column that you want to show as Percentage of Grand Total.
• Secondly, click on Show Values As >> then % of Grand Total.

• As a result, Sum of Total Reviews is shown as Percentage of the Grand Total.

9. Turn off GETPIVOTDATA Formula

The GETPIVOTDATA function retrieves data from a pivot table by referencing specific values within the pivot table. Unlike regular cell references, it extracts data directly from the source data. Moreover, it extracts data from a pivot table based on the criteria you specify.

Suppose, you want to reference a cell value from PivotTable. I want to write the value of cell D7 in cell E7 just by writing the formula “=D7”. But after doing this, the GETPIVOTDATA formula is still showing in cell E7. The formula is:

Keeping the GETPIVOTDATA formula is problematic while creating dynamic dashboards If it exists, the data are not updated correctly. Following are some difficulties users face while keeping GETPIVOTDATA on.

• GETPIVOTDATA is not ideal for dynamic analysis where users need to change the data criteria frequently. Each time the criteria change, the GETPIVOTDATA function must be updated manually.
• If you modify the layout or structure of the PivotTable like changing the layout, the GETPIVOTDATA formulas may break. It will cause errors in the worksheet.
• It may not work well with calculated fields and items in the PivotTable. This will also lead to incorrect results or errors.
• GETPIVOTDATA often uses hard-coded references to cells in the formula. It gives error when you want to use cell references or other dynamic formulas.

To avoid these problems, you can turn off the GETPIVOTDATA formula.

• First, click on any cell of the column for which you want to turn off the GETPIVOTDATA option.
• Then select the PivotTable Analyze tab >> from Options >> click on Generate GetPivotData.

• After clicking, the tick mark should be gone just before the Generate GetPivotData option.

• Now, if you write the formula “=D7” in cell E7, it will show only the value of cell D7. This is because you have turned off GETPIVOTDATA option.
• You can turn it on again by clicking on the Generate GetPivotData option again.

10. Group/Ungroup Items Under a Field

• Initially, select the items you want to put into a group.
• Then, right-click.
• Lastly, select the Group option from the Context Menu.

• Therefore, the selected items are now under Group1. You can edit this Group name according to your preference.

• To ungroup the items, right-click on Group1.
• Then, click on the Ungroup option.

• As a result, the items have been ungrouped.

11. Group Date Field

There is a column namely Released Date, represented by Quarters. Now, you want to group this as Months. Grouping Date Fields is really useful for analyzing the dataset.

• First, right-click on any cell of the Quarters.
• Then, select the Group option from context menu.

• Thus, the Grouping dialog box will open.
• The starting and ending dates will be set automatically according to your dataset. You can also change this if you need.
• In the By box, select your preferred options. I have chosen Months.
• Lastly, select the OK option.

• The dates have been successfully grouped into Months.

12. Creating Report Filter

• Initially, click on any cell of the PivotTable. The PivotTable Fields pane will be shown.
• Drag and drop the field under Filters based on which you want to filter the values of the table.

• A filter option will appear just above the table.
• Now, click on the drop-down.

• Select the option you want to see in the PivotTable and press OK.

• As a result, only the values for Black and White are shown.

• Now, select Color and the values will be filtered for Color.

13. Filter Top/Bottom N Values

Suppose I want to filter the Top/Bottom items based on the Sum of Gross Revenue.

• Firstly, click on the drop-down beside the Row Labels.
• Secondly, select Value Filters >> choose the Top 10 option.

• The Top 10 Filter dialog box will appear.
• Now, select the drop-down and choose Top for showing Top values.

• Then select the number. As I want to see the top 12 values, I have selected 12.
• Choose Items >> from by, select Sum of Gross Revenue >> press OK.

• As a result, you can see the Top 12 Sum of Gross Revenue with the Genre.

• In case, you want to show bottom values, select Bottom from the dropdown.
• I have chosen Bottom 5 items by Sum of Gross Revenue.
• Press OK.

• Thus, you can see only Bottom 5 values.

14. Refresh Data

When you update/add any value, the source data of the PivotTable also changes. But to do this, you have to refresh the table.

Suppose, in this PivotTable, the Sum of Duration for the Genre “Crime” is “29558”. If I make changes to any value under this Genre, the PivotTable need to be updated.

14.1 From PivotTable Analyze Tab

• Firstly, change the value from “110” to “11000” to visualize the change easily.

• Then, click on the PivotTable Analyze tab >> select Refresh command >> click on the Refresh option under it.

• Consequently, the Sum of Duration of Crime has been updated to “40448”.

14.2 From PivotTable Options

• To begin, select any cell of the PivotTable.
• Click on PivotTable Analyze tab >> choose PivotTable dropdown >> under the Options dropdown, click on Options.

• As a consequence, the PivotTable Options dialog box will appear.
• Now, select Data >> check the Refresh data when opening the file option >> click on the OK button.
• By doing this, the values will get refreshed every time while opening the file.

14.3 Refresh Pivot Table When New Column/Row is Added

Here, in this dataset you can see that the Sum of Duration of “Action” movie is “101711”. If I insert a new data in the Source Data of “Action” movie, the PivotTable should get updated.

• To begin, insert a new row/column of information to the Data Source of the PivotTable.

• Now, right-click on any cell of the PivotTable >> click on Refresh from context menu.

• As you can see the PivotTable is not updated after clicking on Refresh. The “Action” movie still has “101711” min duration.

To resolve this issue, follow the steps:

• Click on any cell of the PiovtTable.
• Then select the PivotTable Analyze tab >> Change Data Source >> select Change Data Source.

• Move PivotTable dialog box will appear.
• This time, select the whole table including the row which is added newly on the Table/Range box.
• Finally, click OK.

• Therefore, you can see that the data is updated in the PivotTable.

15. Hide/Unhide Subtotals

Generally, in a PivotTable, the subtotals are shown. In some situations, you might need to hide these subtotals.

• First and foremost, click on any cell of the Sum of Duration column.
• Then select the Design tab >> click on Subtotals >> from the options, choose Do not Show Subtotals.

• Therefore, you can see that the subtotals are hidden.

• If you want to Unhide this, select the option Show all Subtotals at Top of Group under Subtotals.

16. Delete Source Data and Restore It with a Double-click

To reduce the file size, sometimes you need to delete the Source Data of the PivotTable. Luckily, deleting the Source Data won’t affect the table. Following is the PivotTable and I will show how to delete the Source Data of this table.

• Right-click on the sheet where the source data is stored.
• Now, select the Delete option, and you’ve deleted it.

• If you want to restore the source data, right-click on any cell of the PivotTable.
• Then, select Show Details.

• Thus, the data is restored in a Table form.

• Alternatively, you can just double-click on the output of Grand Total cell.

• As a result, the source data is restored again.

17. Drill Down Pivot Table

Drilling Down in PivotTable is a very useful feature to show the details of the summarized table.

• Initially, double-click on the item you want to drill down.

• Hence, the Show Detail dialog box will appear.
• Now, choose the field containing the detail you want to show.
• I want to show the details about Country. So, I selected Country.
• Then, press OK.

• Therefore, the items will have a plus (+) sign to the left.
• Now, double-click on any item.

• It will drill down on the names of the countries which have released movies in Animation Genre. It drills down on the values as well.

18. Create Different Styles in Pivot Table

• First, click on any cell in the PivotTable.
• Select the Design tab.
• Then click on the drop-down icon of PivotTable Styles.

• Now click on New PivotTable Style….

• The New PivotTable Style dialog box will appear.
• Give a Name to your custom PivotTable Style.
• Select the element which you want to Format from the Table Element. I have selected the Header Row.
• Then click on Format.

• Now, you can format the cells according to your choice. I have just changed the Fill color.
• Check the Sample and press OK.

• After checking the Preview, click OK.

• So, you can see your created PivotTable Style in PivotTable Styles command as Custom.

19. Change Layout of Pivot Table

• Click on any cell of the PivotTable.
• Then select the Design tab >> click on Report Layout >> select the layout you want to show. I have selected Show in Compact Form.

• The PivotTable is changed to Compact Form layout.

• If you select Show in Tabular Form, it will look like the following.

• If you choose Show in Outline Form, the output will be like the image given below.
• Choose a layout that goes well with your table.

20. Restrict Column Width Change after Refresh

In PivotTable, if you adjust the Column widths according to your needs, after refreshing the table, the width will automatically be changed to autofit columns. Sometimes this will affect the outlook of your table. So it’s better to restrict column width after refresh in this case.

In the following image, I have increased the column width to make this table easily readable.

Now, if I click on the Refresh button, it will automatically Autofit the columns.

• To prevent this, right-click on any cell of the PivotTable.
• Now, click on PivotTable Options….

• Now, in the PivotTable Options dialog box, select the Layout & Format option >> uncheck the box of Autofits column widths on update >> click OK.

21. Display Items with No Data

Some items in the PivotTable have no data. PivotTable hides the field names of those data. But by following the steps below, you can display the items with no data.

In the following dataset, there are some hidden items that don’t have data.

• Right-click on any cell of the PivotTable.
• Click on Field Settings.

• The Field Settings dialog box will open.
• Select the Layout & Print option >> click on Show items with no data >> press OK.

• Therefore, the items which don’t have data are also displayed.

22.  Substitute Blank Cells in Pivot Table

In PivotTable, you can substitute any blank cell with a value. If you want to give additional information about the blank cells, you can follow this technique.

Following is a dataset and there are many blank cells, it means this country didn’t release any movie under “Action” Genre. So I can give this information by substituting the blank cell with a value “No Release”.

• Click on any cell of the PivotTable.
• Then select the PivotTable Options from the context menu.

• In PivotTable Options dialog box, click on Layput & Format.
• Write the text you want to substitute with the blank values in the For empty cells show box.
• Press OK.

• Thus, the blank cells are substituted with values.

23. Attach Data Bars in Pivot Table

You can attach data bars to PivotTable. Adding data bars will make your PivotTable attractive and easy to understand. Follow these simple steps to do this.

• Click on any cell of the PivotTable.
• Then select the Home tab >> Conditional Formatting >> click on Data Bars >> then More Rules….

• New Formatting Rule dialog box will appear.
• Now, select All cells showing “Sum of Gross Revenue” values.
• Then click on the Show Bar Only option if you want to show the bars only.
• Choose the Color and check the preview.
• Press OK.

• As a consequence, data bars are added to the selected cells.

24. Create Pivot Chart

Adding Pivot Chart in your PivotTable will enhance the readability of your worksheet. You can create a Pivot Chart from Pivot Table very easily.

• First, click on any cell of the PivotTable.
• Then, click on Insert tab >> PivotChart >> select PivotChart.

• Insert Chart dialog box will appear.
• Select the Chart type you want to insert. I have inserted Pie
• Now, click on OK.

• Therefore a Pivot Chart is inserted.

25. Create Multiple Pivot Tables

In my dataset there are “Black and White” and “Color” these two types of movies. Now, I want to create a PivotTable which will contain only “Black and White” movies and also a PivotTable which will contain only “Color” movies.

I can create these two PivotTables from one. Isn’t it amazing? Follow the steps:

• Initially, click on any cell of the PiivotTable.
• Now, click on the PivotTable Analyze
• Then, select Show >> click on Field List.

• Thus, PivotTable Fields pane will open.
• Drag the field to Filters based on which you want to create multiple PivotTables. I have dragged the Color/B&W field here.

• Therefore, the Filter option is inserted in the existing PivotTable.

• The existing PivotTable is in a sheet named “Multiple Pivot Tables” here. Now I will create two separate PivotTables based on the filter options.

• Click on any cell of the PivotTable.
• Then select PivotTable Analyze >> PivotTable >> Options >> click on Show Report Filter Pages….

• Select the Filter item from the Show Report Filter Pages dialog box.
• Lastly, press OK.

• As a result, you can see another two PivotTables have been inserted based on the filter options. The name of the worksheets is based on the filter options.

Apply Keyboard Shortcuts to Enhance Productivity with Pivot Table

Applying keyboard shortcuts in Excel is always handy. It saves out time and effort. There are some PivotTable related keyboard shortcuts. Learning these shortcuts will surely increase your efficiency while working with PivotTable. Let’s review this table to know all the shortcuts at a glance.

Things to Remember

• Select the appropriate data range for your PivotTable. Be careful not to include unnecessary rows or columns.
• If your data source changes or updates regularly, remember to refresh your PivotTable.
• When working with a large dataset, use clear and descriptive field names.
• Select the relevant summary function (e.g., sum, average, count) based on the type of data you want to analyze.

Frequently Asked Questions

1. What’s the differences between a pivot table and a pivot chart?

A pivot table is a tool for data analysis and summarization, while a pivot chart is a graphical representation of the data within a pivot table. Pivot charts help you visualize trends and patterns more easily by converting your data into different types of graphs.

2. Are there any limitations to advanced pivot tables?

While advanced pivot tables are powerful, they might struggle with very large datasets or complex calculations, which could slow down performance. Additionally, pivot tables may not handle certain types of data transformations or customizations as easily as specialized data analysis tools.

Conclusion

In this article, I have shown 25 amazing tips and techniques of advanced PivotTable in Excel. I have tried to describe each method in a detailed way with the necessary images. I hope you can implement these advanced methods in your Excel worksheet effectively. PivotTables are always helpful and easy to use. So, learn those techniques to increase your efficiency. If you have any questions, please leave a comment.