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

Get FREE Advanced Excel Exercises with Solutions!

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.

advanced pivot table in excel

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.

Basic Components of a PivotTable

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.

Sample dataset for creating 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.

Inserting PivotTable in Excel

  • 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.

PivotTable from table or range dialog box

  • 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.

PivotTable Fields Pane

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

Choosing fields in PivotTable Fields Pane

  • As a result, a PivotTable is created successfully.

Creating PivotTable in Excel

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.

Keyboard shortcut to create a PivotTable in Excel

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.

Sample PivotTable

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

Inserting slicer in PivotTable

  • 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.

Insert Slicer dialog box

  • 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.

Slicer in PivotTable

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.

Adding timeline in PivotTable from Insert tab

  • 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.

Insert Timelines dialog box

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

Choosing Years from timeline options

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

Result of adding timeline in PivotTable

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

Scrolling Timeline in PivotTable

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.

Selecting Number Format in PivotTable

  • 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.

Format Cells dialog box

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

Result of changing Number Format in PivotTable

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.

More sort options of PivotTable

  • 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.

Sort By Value dialog box

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

Output of Sorting items in PivotTable

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.

Custom sort option in PivotTable

  • Then, click on the File tab.

File tab of Excel

  • Now, click on Options.

Options from File tab in Excel

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

Edit custom lists in Excel options

  • 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.

Options dialog box to custom sort in PivotTable

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

Excel Options dialog box

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

Refreshing PivotTable to Custom Sort

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

Adding custom sort in PivotTable

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….

Clicking on calculated Field option from PivotTable Analyze TabClicking on calculated Field option from PivotTable Analyze Tab

  • 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.

Insert Calculated Field dialog box

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

Output of inserting calculated field in 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.

Deleting calculated field in Insert Calculated Field dialog box

  • Therefore, the calculated field has been deleted.

Final output of deleting calculated field

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.

calculating difference between 2 columns

  • 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.

turning off Grand Total

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

adding gross revenue

  • Thus Gross Revenue has been added second time.

output after adding gross revenue again

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

Show value as option

  • 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.

show value as difference from

  • As a result, the difference is calculated.

alculated difference

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

final output of calculating difference

8. Show Percentage of Grand Total

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

Dataset for calculating percentage of grandtotal

  • 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.

Selecting % of Grand Total from context menu

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

Showing values as percentage of grandtotal in PivotTable

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:

=GETPIVOTDATA("Sum of Budget",$B$6,"Genre","Action")

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.

GETPIVOTDATA function of PivotTable

  • 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.

Generate GetPivotData option from PivotTable Analyze tab

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

Turning off Generate GetPivotData

  • 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.

Output of turning off GETPIVOTDATA function

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.

Grouping items in PivotTable

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

Final output of grouping items in PivotTable

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

Ungrouping items in PivotTable

  • As a result, the items have been ungrouped.

Final output of ungrouping items in PivotTable

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.

Grouping date field in PivotTable

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

Clicking on 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.

Grouping dialog box in PivotTable

  • The dates have been successfully grouped into Months.

Grouping date field in range in PivotTable

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.

Adding filter in PivotTable

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

Selecting filtering option

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

Selecting Black and White for filtering

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

Output of filtering in PivotTable

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

Result of filtering items in PivotTable

13. Filter Top/Bottom N Values

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

Dataset for filtering top or bottom values

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

Top 10 options of Value filters

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

Top 10 filter dialog box

  • 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.

Selecting options in Top 10 Filter dialog box

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

Result of filtering top and bottom N values

  • 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.

Top 10 Filter dialog box in PivotTable

  • Thus, you can see only Bottom 5 values.

Bottom 5 values in PivotTable

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.

Dataset for refreshing values in PivotTable

14.1 From PivotTable Analyze Tab

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

Changing value in source data of PivotTable

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

Refresh from PivotTable Analyze tab

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

Refreshing PivotTable after changing value

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.

Options menu in PivotTable Analyze tab

  • 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.

PivotTable Options dialog box

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.

Dataset for refreshing after adding new row

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

Adding new row in source data

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

Refreshing after adding row in source data

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

Value is not updated after refresh

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.

Change data source in PivotTable Analyze tab

  • 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.

Move PivotTable dialog box

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

Value updated after changing source data

15. Hide/Unhide Subtotals

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

Showing subtotals in PivotTable

  • 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.

Subtotals option in Design tab of Excel

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

Result of Do not Show Subtotals command

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

Show all subtotals at top of group in PivotTable

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.

Dataset for deleting source data

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

Delete option from right-click on source data sheet

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

Show Details option for recovering source data

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

Restoring source data

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

Double-click on cell to restore source data

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

Restored source data

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.

Drill down PivotTable

  • 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.

Show Detail dialog box for drill down

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

Drilling down by double-clicking on PivotTable

  • 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.

Output of drilling down in PivotTable

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.

Changing design of PivotTableChanging design of PivotTable

  • Now click on New PivotTable Style….

New PivotTable style option

  • 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.

New PivotTable Style dialog box

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

Format Cells dialog box in PivotTable

  • After checking the Preview, click OK.

New PivotTable Style dialog box

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

Creating custom style in PivotTable

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.

Report Layout option from Design tab in PivotTable

  • The PivotTable is changed to Compact Form layout.

Show in Compact Form layout

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

Show in Tabular Form layout

  • 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.

Show in Outline Form layout

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.

Dataset for showing how to restrict column width change

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

Refreshing PivotTable

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

PivotTable Options from context menu

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

PivotTable Options dialog box

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.

Dataset for displaying items with no data

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

Field Settings option of PivotTable

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

Field Settings dialog box

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

Displaying items with no data in PivotTable

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”.

Dataset for substituting blank cellDataset for substituting blank cell

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

PivotTable Options to substitute values in blank cells

  • 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.

PivotTable options dialog box

  • Thus, the blank cells are substituted with values.

Substituting values in blank cells

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….

Conditional formatting option in Home tab

  • 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.

New Formatting Rule dialog box

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

Adding data bars in PivotTable

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.

Inserting PivotChart in PivotTable

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

Insert Chart dialog box

  • Therefore a Pivot Chart is inserted.

PivotChart in Excel

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.

Showing Field List in PivotTable

  • 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.

Dragging item to Filters in PivotTable

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

Filtering values in 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.

Filtering option in PivotTable

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

Showing report Filter pages

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

Choosing the filter type in Show Report Filter Pages dialog box

  • 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.

Created multiple PivotTables from one

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.

Keyboard Shortcut What it Does
Alt + N + V Inserts a PivotTable
Alt + N + V + T + Enter Opens PivotTable from table or range dialog box
Alt + D + P Opens the Old PivotTable Wizard
Alt + Shift + Right Arrow Key Groups the selected items of PivotTable
Alt + Shift + Left Arrow Key Ungroups the selected items of PivotTable
Ctrl + Minus (-) Hides items from the PivotTable
Alt + J + T + L Hides the Field List
Ctrl + Shift + = Creates a Calculated Field
Ctrl + A Selects the entire PivotTable
F11 Inserts Pivot Chart to New Worksheet
Alt + F1 Inserts Pivot Chart to Current Worksheet
Space bar Toggles checkboxes in Fields List

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.


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.

Mahfuza Anika Era
Mahfuza Anika Era

Hello! Welcome to my Profile. Recently I have been graduated from Bangladesh University of Engineering and Technology in Civil Engineering. Being a fresh graduate, I want to build up my skill in article writing about Microsoft Excel and VBA. I am also interested in research and development. I believe in learning something new every day and implementing my knowledge more effectively.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF