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

PivotTable: Basic Things

PivotTable is a powerful data analysis tool in Microsoft Excel. It allows users to quickly summarize, organize, and gain insights from large datasets. By transforming raw data into a more meaningful and compact format, PivotTables enable efficient analysis without the need for complex formulas or manual data manipulation. They are especially useful when dealing with extensive datasets, providing a user-friendly way to extract valuable information and identify trends, patterns, and outliers. If you’ve mastered the basics of PivotTables, exploring advanced techniques can further enhance your data analysis capabilities.

Basic Components of a PivotTable

  • Data Source: The data source serves as the foundation for creating a PivotTable. It originates from the original dataset and should be well-organized, complete with column headers. These headers play a crucial role in defining the fields within the PivotTable.
  • Field List: When you create a PivotTable, the Field List appears on the right side of your screen. It includes all the available fields from your dataset. You can easily drag and drop these fields into the desired areas within the PivotTable.
    • To access or hide the Field List, navigate to the “PivotTable Analyze” tab and select or deselect the corresponding option.
  • Rows and Columns: In a PivotTable, you can arrange fields from the data source into the “Rows” and “Columns” areas. These selections determine how the data is organized and displayed in the final table.
  • Values: The “Values” area contains numerical data that you want to summarize or analyze. You can apply various summary functions (such as sum, count, average, minimum, maximum, etc.) to perform calculations on this data.
  • Filters: The “Filters” area allows you to add fields that act as filters. By selecting or deselecting filter options, you can dynamically update the results displayed in the PivotTable.

Basic Components of a PivotTable

How to Create a Pivot Table in Excel

The below large dataset will be used to create a PivotTable.

Sample dataset for creating PivotTable

  • Select Table/Range Option:
    • Begin by opening your Excel workbook containing the dataset you want to analyze.
    • Click on any cell within the dataset to ensure it’s selected.
    • Navigate to the Insert tab in the Excel ribbon.
    • Choose PivotTable and then click on From Table/Range.

Inserting PivotTable in Excel

    • The PivotTable from table or range dialog box will appear.
    • The Table/Range field will automatically be set based on the cell you clicked earlier.
    • If you want the PivotTable to appear in a new worksheet, select that option and click OK.

PivotTable from table or range dialog box

  • Choose Fields for the PivotTable:
    • Now you need to select the fields (columns) from your dataset to include in the PivotTable.
    • The Field List will appear on the right side of your screen.
    • You can drag and drop fields into the following areas:
      • Rows: Determines how data is organized vertically.
      • Columns: Determines how data is organized horizontally.
      • Values: Contains the numerical data you want to summarize (e.g., sum, average, count, etc.).
      • Filters: Allows you to add fields that act as filters for your PivotTable.

PivotTable Fields Pane

  • Example Selection:
    • Let’s say you’ve chosen the following fields:
      • Rows: Country and Title
      • Values: Gross Revenue and Budget
      • Filters: Genre

Choosing fields in PivotTable Fields Pane

    • By arranging these fields, you’ve successfully created a PivotTable that summarizes and analyzes your data.

Creating PivotTable in Excel

  • Apply Keyboard Shortcut:
    • Alternatively, you can create a PivotTable using a keyboard shortcut:
      • Press Alt + N + V + T.
      • The “PivotTable from table or range” dialog box will appear.
      • Follow the same steps as described earlier.

Keyboard shortcut to create a PivotTable in Excel

Remember, PivotTables are incredibly versatile and can help you gain valuable insights from your data.

Benefits of Using Advanced Techniques in Excel Pivot Table

Using advanced techniques in Excel Pivot Tables can significantly enhance your data analysis capabilities, making your work more efficient and insightful. Let’s explore the advantages:

  1. Complex Data Analysis:
    • Advanced techniques allow you to perform more complex tasks, such as creating calculated fields, calculated items, and custom formulas. These functionalities enable you to extract deeper insights from your data beyond basic summary functions (e.g., sum, average, count).
  2. Sophisticated Data Models:
    • With advanced Pivot Table techniques, you can build sophisticated data models. This includes combining multiple data sources, using Power Query for data shaping and transformation, and establishing relationships between tables.
  3. Dynamic Reports:
    • Advanced features enable you to create dynamic reports that automatically update when the source data changes. This ensures your analysis remains up-to-date without manual adjustments.
  4. Interactive Filtering:
    • Slicers and timelines are powerful filtering tools within Pivot Tables. They provide an interactive way to filter data, allowing you to explore different aspects of your dataset effortlessly.
  5. Visual Representation:
    • PivotTables can be used to create charts and graphs. Visualizing your data in this way helps present complex information more intuitively to stakeholders.
  6. Data Exploration:
    • Advanced techniques allow you to explore data at different levels. You can drill down into specific details to gain deeper insights, which is valuable for thorough analysis.
  7. Time-Based Analysis:
    • PivotTables can automatically group date and time data into intervals (e.g., months, quarters, years). This simplifies time-based analysis and provides a clearer view of trends over time.
  8. Efficiency and Data-Driven Decisions:
    • As you become proficient with advanced Pivot Table techniques, you’ll save time and effort during data analysis. This efficiency allows you to focus on interpreting results and making informed decisions based on data.

25 Tips & Techniques when using Advanced Pivot Tables

1. Use Slicers for Effortless Data Filtering

  • Scenario: You have a PivotTable, and you want to filter data quickly with a single click.

Sample PivotTable

  • Solution:
    • Click on any cell within your PivotTable.
    • Navigate to the Insert tab.
    • Select Slicer.

Inserting slicer in PivotTable

    • In the Insert Slicers dialog box, choose the field (e.g., Country) by which you want to filter your PivotTable.
    • Click OK.

Insert Slicer dialog box

    • A slicer will appear next to your PivotTable. You can now select different countries to filter the data.
    • The best part? You can select multiple countries simultaneously as filters.

Slicer in PivotTable

2. Enhance Data Visualization with Timelines

  • Scenario: You’re working with a dataset containing movie release dates spanning from 1920 to 2015. You want to filter data based on release years.
  • Solution:
    • Select any cell within your PivotTable.
    • Go to the Insert tab.
    • Choose Timeline.

Adding timeline in PivotTable from Insert tab

    • In the Insert Timelines dialog box, you’ll see the available time-related field (e.g., Release Date).
    • Click OK.

Insert Timelines dialog box

    • A timeline will be added to your PivotTable.
    • From the dropdown, select the desired time interval (e.g., YEARS).

Choosing Years from timeline options

    • Now, you can easily filter data by selecting specific years from the timeline.

Result of adding timeline in PivotTable

    • Bonus: You can even select multiple years, and the PivotTable values will adjust accordingly.

Scrolling Timeline in PivotTable

3. Customize Number Format in a PivotTable

Did you know that you can tailor the number format within a PivotTable? It’s a handy feature! Here’s how you can do it:

  • Right-click on any cell in the column for which you want to change the number format.
  • From the context menu, select Number Format.

Selecting Number Format in PivotTable

  • The Format Cells dialog box will appear.
  • Choose an appropriate category (e.g., Accounting) and set the desired number of decimal places (e.g., 0).
  • Click OK.

Format Cells dialog box

Now you’ll see that the number format has been updated.

Result of changing Number Format in PivotTable

4. Sort Items Using the Context Menu

Sorting items in a PivotTable is essential for better analysis. Follow these steps:

  • Right-click on any cell in the column you want to sort.
  • Select Sort and then choose More Sort Options.

More sort options of PivotTable

  • In the Sort By Value dialog box, specify your sorting preferences (e.g., Smallest to Largest and Top to Bottom).
  • Click OK.

Sort By Value dialog box

Your table will now be sorted based on the sum of the Gross Revenue column.

Output of Sorting items in PivotTable

5. Custom Sort Items

Sometimes, you may want to sort PivotTable items according to your own order. Here’s how:

  • Create a custom sort order by listing the items in a separate column within the same worksheet.

Custom sort option in PivotTable

  • Click on the File tab.

File tab of Excel

  • Go to Options.

Options from File tab in Excel

  • In the Excel Options dialog box, select Advanced and click on Edit Custom Lists.

Edit custom lists in Excel options

  • Specify the cell reference of your custom sort list (or manually enter the items).
  • Press Import and then click OK.

Options dialog box to custom sort in PivotTable

  • Press OK when the Excel Options dialog box appears.

Excel Options dialog box

  • Refresh the PivotTable by right-clicking on any cell in the column you want to sort.

Refreshing PivotTable to Custom Sort

Now the items in the Row Labels column will be custom-sorted according to your preference.

Adding custom sort in PivotTable

6. Create or Remove a Calculated Field in a PicotTable

Creating a calculated field is an advanced feature in Excel’s PivotTable. It’s a clever technique that allows you to compute various parameters without writing complex formulas. Here’s how you can create or remove a calculated field:

6.1 Create a Calculated Field:

  • Click on any cell within the PivotTable.
  • Go to the PivotTable Analyze tab.
  • Under Calculations, select Fields, Items, & Cells, and then choose Calculated Field.

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

  • The Insert Calculated Field dialog box will appear.
  • Provide a relevant name for your calculated field (e.g., Gross Profit).
  • From the available fields, select the ones you want to use in your formula and click Insert Field. For example, you can calculate Gross Profit by subtracting the Budget from the Gross Revenue.
  • Review your formula and click OK.

Insert Calculated Field dialog box

  • A new column will be added to your existing PivotTable with the calculated values.

Output of inserting calculated field in PivotTable

6.2 Remove a Calculated Field

  • To remove a calculated field, follow the same steps as when creating one.
  • Open the Insert Calculated Field dialog box.
  • Click the dropdown menu and choose the field you want to delete.
  • Finally, press Delete and then click OK.

Deleting calculated field in Insert Calculated Field dialog box

Now you can manage your calculated fields efficiently!

Final output of deleting calculated field

7. Calculate the Difference Between Two Columns

You can easily compute the difference between two columns in a PivotTable without writing any formulas. Follow these quick steps:

  • In your dataset, you have Gross Revenue for the years “2014” and “2015.” Let’s calculate the difference in Gross Revenue between these two years.

calculating difference between 2 columns

  • Click on any cell within the PivotTable.
  • Go to the Design tab.
  • Under Grand Totals, select Off for both Rows and Columns. We don’t need grand totals for this calculation.

turning off Grand Total

  • Now, add the Gross Revenue to the Values area a second time. We’ll use this duplicate field to show the difference.

adding gross revenue

  • You’ll see that Gross Revenue has been added a second time.

output after adding gross revenue again

  • Right-click on any cell in the newly added “Sum of Gross Revenue2” column.
  • Select Show Value As and then choose Difference From…

Show value as option

  • The Show Values As dialog box will appear. Set Years (Release Date) as the Base Field.
  • In the Base Item dropdown, select previous because we want to calculate the difference from the previous column.

show value as difference from

  • The difference will now be calculated.

alculated difference

  • Finally, edit the name of the column to Difference and hide any unnecessary columns.

final output of calculating difference

8. Show Percentage of Grand Total

Now let’s determine the Total Reviews as a percentage of the grand total. Follow these steps:

Dataset for calculating percentage of grandtotal

  • Right-click on any cell in the column you want to display as a Percentage of the Grand Total.
  • Click on Show Values As and then choose % of Grand Total.

Selecting % of Grand Total from context menu

You’ll see that the Sum of Total Reviews is now shown as a percentage of the overall grand total.

Showing values as percentage of grandtotal in PivotTable

9. Disabling the GETPIVOTDATA Formula

The GETPIVOTDATA function retrieves data from a pivot table by referencing specific values within that table. Unlike regular cell references, it directly extracts data from the source data. Suppose you want to reference a cell value from a PivotTable. For example, you want to display the value of cell D7 in cell E7 by simply writing the formula “=D7.” However, after doing this, the GETPIVOTDATA formula still appears in cell E7. The formula looks like this:

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

Keeping the GETPIVOTDATA formula can be problematic, especially when creating dynamic dashboards. If it remains active, the data won’t update correctly. Here are some difficulties users face when using GETPIVOTDATA:

  1. Not Ideal for Dynamic Analysis:
    • When users frequently change data criteria, GETPIVOTDATA becomes cumbersome. Each time the criteria change, the function must be manually updated.
  2. Layout and Structure Changes:
    • If you modify the layout or structure of the PivotTable (e.g., changing the layout), GETPIVOTDATA formulas may break, causing errors in the worksheet.
  3. Issues with Calculated Fields and Items:
    • GETPIVOTDATA may not work well with calculated fields and items in the PivotTable, leading to incorrect results or errors.
  4. Hard-Coded References:
    • GETPIVOTDATA often uses hard-coded cell references in the formula. This can be problematic 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

  • Click on any cell in the column for which you want to disable GETPIVOTDATA.
  • Go to the PivotTable Analyze tab.
  • Under Options, click on Generate GetPivotData.

Generate GetPivotData option from PivotTable Analyze tab

  • The checkmark should disappear next to the Generate GetPivotData option.

Turning off Generate GetPivotData

  • Now, if you write the formula “=D7” in cell E7, it will display only the value of cell D7. This is because you’ve turned off the GETPIVOTDATA option.

Output of turning off GETPIVOTDATA function

Remember that you can always turn it back on by clicking the Generate GetPivotData option again.

10. Grouping and Ungrouping Items Under a Field

Grouping items in a PivotTable allows you to organize and summarize data effectively. Here’s how you can group and ungroup items:

  • Grouping Items:
    • First, select the items you want to group together.
    • Right-click on the selection.
    • From the context menu, choose the Group option.

Grouping items in PivotTable

  • The selected items will now be grouped under a default name (e.g., Group1). You can edit this group name according to your preference.

Final output of grouping items in PivotTable

  • Ungrouping Items:
    • To ungroup the items, right-click on the group name (e.g., Group1).
    • Select the Ungroup option.

Ungrouping items in PivotTable

  • The items will be ungrouped.

Final output of ungrouping items in PivotTable

11. Grouping a Date Field

Grouping date fields is useful for analyzing time-based data. Let’s say you have a column called “Released Date” represented by quarters, but you want to group it by months. Follow these steps:

Grouping date field in PivotTable

  • Right-click on any cell within the Quarters column.
  • Select the Group option from the context menu.

Clicking on Group option from context menu

  • The Grouping dialog box will open.
  • The starting and ending dates will be set automatically based on your dataset, but you can adjust them if needed.
  • In the By box, choose your preferred grouping (e.g., Months).
  • Click OK.

Grouping dialog box in PivotTable

  • The dates will now be successfully grouped into Months.

Grouping date field in range in PivotTable

12. Creating a Report Filter

A report filter allows you to filter data in your PivotTable based on specific criteria. Here’s how you can create one:

  • Click on any cell within the PivotTable.
  • The PivotTable Fields pane will appear.
  • Drag and drop the field that you want to use as a filter into the Filters area.

Adding filter in PivotTable

  • A filter option will appear just above the table.
  • Click on the drop-down arrow.

Selecting filtering option

  • Select the option you want to see in the PivotTable (e.g., Black and White) and press OK.

Selecting Black and White for filtering

  • Now only the values corresponding to your chosen filter will be displayed.

Output of filtering in PivotTable

  • You can further refine the filter by selecting other options (e.g., Color).

Result of filtering items in PivotTable

13. Filter Top/Bottom N Values

Suppose you want to filter the top or bottom items based on the sum of gross revenue in your PivotTable.

Follow these steps:

Dataset for filtering top or bottom values

  • Click on the drop-down arrow next to the Row Labels.
  • Select Value Filters and then choose the Top 10 option.

Top 10 options of Value filters

  • The Top 10 Filter dialog box will appear.
  • From the drop-down, choose Top to show the top values.

Top 10 filter dialog box

  • Select the number of items you want to see. For example, if you want to see the top 12 values, select 12.
  • Under By, choose Sum of Gross Revenue and press OK.

Selecting options in Top 10 Filter dialog box

  • As a result, you’ll see the top 12 sum of gross revenue values along with their corresponding genres.

Result of filtering top and bottom N values

  • If you want to show bottom values instead, select Bottom from the dropdown. For example, you can choose the bottom 5 items by sum of gross revenue.
  • Press OK.

Top 10 Filter dialog box in PivotTable

Bottom 5 values in PivotTable

14. Refresh Data

When you update or add any value, the source data of the PivotTable changes. To reflect these changes, you need to refresh the table. Let’s say the sum of duration for the genre Crime in your PivotTable is currently “29558.” If you make changes to any value under this genre, the PivotTable needs to be updated.

Dataset for refreshing values in PivotTable

14.1 From the PivotTable Analyze Tab

  • Change the value from 110 to 11000 (or any other value) to visualize the change.

Changing value in source data of PivotTable

  • Click on the PivotTable Analyze tab.
  • Select the Refresh command and click on the Refresh option.

Refresh from PivotTable Analyze tab

  • The sum of duration for the Crime genre will be updated to the new value (e.g., 40448).

Refreshing PivotTable after changing value

14.2 From PivotTable Options

  • Select any cell within the PivotTable.
  • Click on the PivotTable Analyze tab.
  • Choose the PivotTable dropdown and then click on Options.

Options menu in PivotTable Analyze tab

  • In the PivotTable Options dialog box, go to the Data section.
  • Check the Refresh data when opening the file option.
  • Click OK.
  • Now, the values will automatically refresh every time you open the file.

PivotTable Options dialog box

14.3 Refresh Pivot Table When New Column/Row is Added

In your dataset, you can see that the sum of duration for the “Action” movie is “101711.” If you insert new data into the source data of the “Action” movie, the PivotTable should be updated accordingly.

Follow these steps:

Dataset for refreshing after adding new row

  • Insert a new row or column of information into the data source of the PivotTable.

Adding new row in source data

  • Right-click on any cell within the PivotTable.
  • From the context menu, click on Refresh.

Refreshing after adding row in source data

  • However, you’ll notice that the PivotTable doesn’t update after clicking “Refresh.” The “Action” movie still shows a duration of “101711” minutes.

Value is not updated after refresh

To resolve this issue, follow these additional steps:

  • Click on any cell within the PivotTable.
  • Select the PivotTable Analyze tab.
  • Choose Change Data Source and then select Change Data Source again.

Change data source in PivotTable Analyze tab

  • The Move PivotTable dialog box will appear.
  • This time, select the entire table, including the newly added row, in the “Table/Range” box.
  • Finally, click OK.

Move PivotTable dialog box

  • As a result, you’ll see that the data is now updated in the PivotTable.

Value updated after changing source data

15. Hide/Unhide Subtotals

In a PivotTable, subtotals are typically shown. However, there are situations where you might need to hide these subtotals. Follow these steps:

Showing subtotals in PivotTable

  • First, click on any cell within the Sum of Duration column.
  • Then, select the Design tab.
  • Click on Subtotals and choose the option Do not Show Subtotals.

Subtotals option in Design tab of Excel

  • As a result, the subtotals will be hidden.

Result of Do not Show Subtotals command

  • If you want to Unhide them, 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

Sometimes, to reduce file size, you may need to delete the source data of a PivotTable. Fortunately, deleting the source data won’t affect the table itself. Here’s how to do it:

Dataset for deleting source data

  • Right-click on the sheet where the source data is stored.
  • Select the Delete option to remove it.

Delete option from right-click on source data sheet

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

Show Details option for recovering source data

  • The data will be restored in a table form.

Restoring source data

  • Alternatively, you can double-click on the output of the Grand Total cell to restore the source data.

Double-click on cell to restore source data

Restored source data

17. Drill Down Pivot Table

Drilling down in a PivotTable is a useful feature to show detailed information from a summarized table. Follow these steps:

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

Drill down PivotTable

  • The Show Detail dialog box will appear.
  • Choose the field that contains the detail you want to see. For example, if you want to see details by country, select Country.
  • Press OK.

Show Detail dialog box for drill down

  • The items will now have a plus (+) sign next to them.
  • Double-click on any item to drill down further.

Drilling down by double-clicking on PivotTable

  • It will show the names of countries that have released movies in the Animation genre, along with their corresponding values.

Output of drilling down in PivotTable

18. Create Different Styles in Pivot Table

  • Start by clicking on any cell within the PivotTable.
  • Next, select the Design tab.
  • Click on the drop-down icon for PivotTable Styles.

Changing design of PivotTableChanging design of PivotTable

  • Now, choose New PivotTable Style…

New PivotTable style option

  • The New PivotTable Style dialog box will appear.
  • Give your custom PivotTable style a name.
  • Select the element you want to format from the Table Element options. For example, I’ve chosen the Header Row.
  • Click on Format.

New PivotTable Style dialog box

  • Customize the cell formatting according to your preference. In my case, I’ve changed the Fill color.
  • Check the Sample and press OK.

Format Cells dialog box in PivotTable

  • After reviewing the Preview, click OK.

New PivotTable Style dialog box

  • You’ll now see your created PivotTable style listed in the PivotTable Styles command as Custom.

Creating custom style in PivotTable

19. Change Layout of Pivot Table

  • Click on any cell within the PivotTable.
  • Then, select the Design tab.
  • Click on Report Layout and choose the layout you want to display. I’ve selected Show in Compact Form.

Report Layout option from Design tab in PivotTable

  • The PivotTable will now appear in the Compact Form layout.

Show in Compact Form layout

  • If you choose Show in Tabular Form, it will look different.

Show in Tabular Form layout

  • Similarly, selecting Show in Outline Form will produce a different output.
  • Choose a layout that best suits your table.

Show in Outline Form layout

20. Restrict Column Width Change after Refresh

In a PivotTable, adjusting column widths according to your needs is common. However, after refreshing the table, the column widths automatically adjust to autofit the content. Unfortunately, this can sometimes affect the overall appearance of your table. To prevent this:

  1. In the following image, I’ve increased the column width to improve readability.

Dataset for showing how to restrict column width change

  • Now, if I click on the Refresh button, the columns will autofit again.

Refreshing PivotTable

  • To restrict column width changes after refresh, right-click on any cell within the PivotTable.
  • Click on PivotTable Options.

PivotTable Options from context menu

  • In the PivotTable Options dialog box, select the Layout & Format option.
  • Uncheck the box that says Autofit column widths on update.
  • Click OK.

PivotTable Options dialog box

21. Display Items with No Data

In a PivotTable, some items may have no data associated with them. By default, the PivotTable hides the field names for these data-less items. However, you can display them using the following steps:

Dataset for displaying items with no data

  • In your dataset, there are hidden items that lack data.
  • Right-click on any cell within the PivotTable.
  • Click on Field Settings.

Field Settings option of PivotTable

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

Field Settings dialog box

  • As a result, the items that previously had no data will now be displayed.

Displaying items with no data in PivotTable

22.  Substitute Blank Cells in Pivot Table

In a PivotTable, you can replace any blank cell with a value. If you want to provide additional information about these blank cells, follow this technique:

  • Consider the dataset where there are many blank cells. For example, a country didn’t release any movies under the “Action” genre.

Dataset for substituting blank cellDataset for substituting blank cell

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

PivotTable Options to substitute values in blank cells

  • In the PivotTable Options dialog box, click on Layout & Format.
  • Write the text you want to substitute for the blank values in the For empty cells show box (e.g., “No Release”).
  • Press OK.

PivotTable options dialog box

  • The blank cells will now be substituted with the specified values.

Substituting values in blank cells

23. Attach Data Bars in Pivot Table

You can enhance your PivotTable by adding data bars. These bars provide a visual representation of data and make the table more attractive and easier to understand. Follow these steps:

  • Click on any cell within the PivotTable.
  • Then, select the Home tab.
  • Go to Conditional Formatting and click on Data Bars, then choose More Rules…

Conditional formatting option in Home tab

  • The New Formatting Rule dialog box will appear.
  • Select All cells showing ‘Sum of Gross Revenue’ values.
  • Click on the Show Bar Only option if you want to display only the bars.
  • Choose a color and check the preview.
  • Press OK.

New Formatting Rule dialog box

  • As a result, data bars will be added to the selected cells.

Adding data bars in PivotTable

24. Create a Pivot Chart

Adding a Pivot Chart to your PivotTable can enhance the readability of your worksheet. Follow these steps to create a Pivot Chart from a Pivot Table:

  • Click on any cell within the PivotTable.
  • Go to the Insert tab.
  • Click on PivotChart and select the desired chart type.

Inserting PivotChart in PivotTable

  • The Insert Chart dialog box will appear.
  • Choose the chart type you want (for example, Pie chart).
  • Click OK to insert the Pivot Chart.

Insert Chart dialog box

  • A Pivot Chart is inserted.

PivotChart in Excel

25. Create Multiple Pivot Tables

Suppose you have a dataset with two types of movies: Black and White and Color. You want to create separate PivotTables for each movie type. Here’s how you can do it:

  • Click on any cell within the PivotTable.
  • Go to the PivotTable Analyze tab.
  • Click on Show and then select Field List.

Showing Field List in PivotTable

  • The PivotTable Fields pane will open.
  • Drag the field (e.g., Color/B&W) to the Filters area.

Dragging item to Filters in PivotTable

  • This will insert a filter option into the existing PivotTable.

Filtering values in PivotTable

  • The existing PivotTable is in a sheet named Multiple Pivot Tables.
  • Now create two separate PivotTables based on the filter options:

Filtering option in PivotTable

    • Click on any cell within the PivotTable.
    • Go to 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.
    • Press OK.

Choosing the filter type in Show Report Filter Pages dialog box

  • 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

Using keyboard shortcuts in Excel is always helpful. Here are some PivotTable-related keyboard shortcuts that can save you time and effort:

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: When creating a PivotTable, make sure to choose the relevant data range. Avoid including unnecessary rows or columns.
  • Regularly Refresh Your PivotTable: If your data source changes or updates frequently, remember to refresh your PivotTable to reflect the latest data.
  • Use Clear and Descriptive Field Names: When working with a large dataset, use field names that are easy to understand and describe the data accurately.
  • Choose the Right Summary Function: Depending on the type of data you want to analyze (e.g., numeric values, counts, averages), select the appropriate summary function for your PivotTable.

Frequently Asked Questions

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

  • Pivot Table is a data analysis tool that summarizes and aggregates data based on specific criteria (rows, columns, values).
  • Pivot Chart is a graphical representation of the data within a Pivot Table. It helps visualize trends and patterns by converting data into different types of graphs (e.g., bar charts, line charts, pie charts).

2. Are there any limitations to advanced pivot tables?

While advanced Pivot Tables are powerful, they may face limitations:

  • Handling very large datasets could impact performance.
  • Complex calculations might slow down processing.
  • Customizations may be less flexible compared to specialized data analysis tools. Remember these tips and insights to make the most of your PivotTable experience!

Download Practice Workbook

You can download the practice workbook from here:

Get FREE Advanced Excel Exercises with Solutions!

Mahfuza Anika Era
Mahfuza Anika Era

Mahfuza Anika Era graduated from the Bangladesh University of Engineering and Technology in Civil Engineering. She has been with ExcelDemy for almost a year, where he has written nearly 30 articles and reviewed many. She has also worked on the ExcelDemy Forum and solved 50+ user problems. Currently, she is working as a team leader for ExcelDemy. Her role is to guide his team to write reader-friendly content. Her interests are Advanced Excel, Data Analysis, Charts & Dashboards,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF