Advanced Pivot Table Techniques in Excel and Google Sheets

Written by

Mel Jenkins

Reviewed by

Laura Tsitlidze

Last updated on June 30, 2023

This tutorial demonstrates advanced pivot table techniques in Excel and Google Sheets.

 

advancedpivot table intro

 

Click here for a briefer tutorial on creating a pivot table. Or read on to explore Excel’s more complex PivotTable features.

Pivot Table Source Data
Recommended Pivot Tables
Refresh Pivot Table Data
Group Pivot Table Fields
Pivot Table Filters
Create and Use Slicers With Pivot Tables
Format Pivot Table Values
Hide and Unhide Pivot Table Subtotals
Pivot Table Calculated Fields
Move Pivot Table
Insert Pivot Chart
Pivot Table Options
Google Sheets

Tip: Try using some shortcuts when you’re working with pivot tables.

What is “Source Data” in a Pivot Table?

The source data of a pivot table is the data that the pivot table is going to be created from. This has to be stored in the correct table structure or format to be able to create a successful pivot table. The data table has to consist of column (“field”) headers with logical names to explain the data below each column and rows of records.

Consider the spreadsheet below:

 

advancedpivot source data

 

The data is set up in columns, each with a unique field name. Each row in Excel is known as a single record, and those records start directly below the header row. The data can’t contain any blank rows or columns. With the data set up correctly, you can create the pivot table. Choose from the Recommended PivotTables or design a new pivot table with the fields you need.

Excel has a feature to recommend a pivot table automatically based on your data.

  1. Select any cell within your source data, and then in the Ribbon, go to Insert > Tables > Recommended Pivot Tables.

 

advancedpivot recommended ribbon

 

  1. Choose a pivot table from the list on the left, and then click OK.

 

advancedpivot recommended

 

  1. This creates a pivot table in a new sheet. You can then adjust it in the PivotTable Fields window. In the example above, Excel used the last column of data for row labels. This doesn’t make much sense! You can remove this field (Corn) from the row labels by dragging it across to the Values box.

 

advancedpivot move field

 

  1. You can then drag a more appropriate field (Region, Salesperson, or Sale Date) to the Rows box.

 

advancedpivot row field

 

The pivot table changes accordingly.

 

advancedpivot adjusted

 

Refresh Pivot Table Data

If your source data changes, you need to refresh your pivot table; it won’t change automatically.

In the Ribbon, go to Data > Refresh All.

 

advancedpivot ribbon refresh

 

The figures in your pivot table now match the edited source data.

 

advancedpivot refreshed

 

Group Pivot Table Fields

Group Row and Column Fields

You can group row and column fields if you have logical groups in your data.

Consider the following pivot table.

 

advancedpivot region

 

  1. In the pivot table, hold down the CTRL key, and select the field values you wish to group. Then in the Ribbon, go to Pivot Table Analyze > Group > Group Selection.

 

advancedpivot select fields

 

  1. The two row labels selected (here, Los Angeles and San Francisco) are grouped together under a new group called Group1.

 

advancedpivot groupone

 

  1. If your data is set up in a column format, you may wish to group by columns. Select the columns you wish to group on.

 

advanced pivot select columns

 

  1. Then in the Ribbon, go to Pivot Table Analyze > Group > Group Selection.

 

advanced pivot column group

 

Rename Groups

  1. You can easily rename a group. Select the cell with the label, and in the formula bar, type in a new name for the group.

 

advancedpivot rename label

 

  1. You can do this with each row group that’s created.

 

advancedpivot manually grouped

 

  1. You can also rename your column groups.

 

advancedpivot rename group

 

Group by Date

  1. If you move the Sales Date to the Row Labels section of the pivot table, notice that the pivot table automatically groups the Sales Date by Year, Quarter, and Month. The actual dates of the order are below each month for that quarter and year.

 

advancedpivot grouping

 

  1. Each group has an expand (+) icon attached to it. If you click this icon, as shown in the graphic above, it changes to a collapse () icon.

 

advancedpivot minmax

 

  1. The Rows area of PivotTable Fields reflects that as well as the Order Date (with automatic Quarters and Years) field have been created and added to the pivot table.

 

advancedpivot group dates

 

  1. You can remove any of the automatically generated date fields (years, quarters, or months), but if you remove the year, for example, then the other date fields aggregate the yearly data. In other words, all the orders for February are included in Quarter1 > February (i.e., the sum of Feb 2020, Feb 2021, and Feb 2022).
    Similarly, if you remove the Quarters field, then only months are shown – so all the orders in February, regardless of the year, are shown in February.

 

advancedpivot dates month quarter

 

  1. To change the way that the pivot table groups, select the row field in the pivot table and then, in the Ribbon, go to Pivot Table Analyze > Group > Group Selection.

 

advancedpivot ribbon groupselection

 

  1. Choose the grouping you wish to show. For example, you may just want Months and Days.

 

advancedpivot select group

 

  1. This would remove the year, though – so any orders on the 1st of May, for example, are on that day – regardless of the year of the order!

 

advancedpivot group days

 

  1. If you only include one option in the grouping, the ability to drill down is removed.

 

advancedpivot group years

 

  1. You can, however, then select the field in the pivot table, and then click again on Group Selection in the Ribbon to add other grouping options back.

 

advancedpivot regroup

 

Ungroup Data

You may wish to remove groups that have been created in your pivot table.

Click in a field in your pivot table that is part of a group and then, in the Ribbon, go to PivotTable Analyze > Group > Ungroup.

 

advancedpivot ungroup ribbon

 

The grouping for that field is removed.

 

advancedpivot ungrouped

 

Pivot Table Filters

When you add field to a pivot table, Excel automatically adds filter buttons.

 

advancedpivot filter

 

  1. Click on any of the (Region) items to filter your pivot table by one of the values found in that list.

 

advancedpivot single filter

 

  1. To clear the filter from the Region field, click on the filter button, and then click on (All). Click OK.

 

advancedpivot filter all

 

  1. To filter by more than one value, tick Select Multiple Items and then choose the items to filter for. Click OK to apply the filter to your pivot table.

 

advancedpivot multiple filter

 

Create and Use Slicers With Pivot Tables

Slicers are another way to filter a pivot table.

  1. In the Ribbon, go to PivotTable Analyze > Filter > Insert Slicer.

 

advancedpivot ribbon slicer

 

  1. Select the field you wish to insert a slicer for, and then click OK.

 

advancedpivot slicer select field

 

All of the available data options are automatically shown.

 

advancedpivot slicer

 

  1. Click on the top value (e.g., Boston), and then hold down the CTRL key to select any additional values. The pivot table updates dynamically to reflect the filter.

 

advancedpivot slicer select

 

  1. If you want to select adjacent values, click on the first value in the slicer, and then holding down the SHIFT key, click on the final value you need.

 

advancedpivot slicer concurrent select

 

  1. To remove the slicer, click on it and press the DELETE key. This also removes any filtering that the slicer applied to your pivot table.

Format Pivot Table Values

When you create a pivot table, the values are formatted as a general number format.

 

advancedpivot general format

 

To format the numbers, it is best to use the formatting options in the PivotTable Fields Values box.

  1. In the small drop down next to the field in the Values box, click Value Field Settings…

 

advancedpivot field settings

 

  1. Then click Number Format.

 

advancedpivot number format

 

  1. Choose from the Category list, and then click OK to apply the format to the relevant field.

 

advancedpivot dollar format

 

  1. Click OK again to return to the PivotTable Fields list.
    At this stage, you can also change the name of the label that is displayed in the pivot table.

 

advancedpivot custom name

 

  1. Repeat the steps above for the rest of the fields where you want to apply a number format.

 

advancedpivot renamed value headings

 

Hide and Unhide Pivot Table Subtotals

By default, there’s a Grand Total at the bottom of a pivot table; if you have grouping applied, subtotals are also added for each group.

To remove subtotals:

  1. In the Ribbon, go to Design > Layout > Subtotals.

 

advancedpivot ribbon subtotals

 

  1. Click Do Not Show Subtotals.

 

advancedpivot ribbon donotshow subtotals

 

Any subtotals are removed from the pivot table.

 

advancedpivot subtotals removed

 

You could also set the subtotals to show at the top or bottom of each group.

 

advancedpivot subtotals show button

 

  1. To show or hide the Grand Total, in the Ribbon, go to Design > Layout > Grand Totals.

 

advancedpivot ribbon grandtotals

 

  1. Choose which option you want for Grand Totals. For example, to hide all grand totals, click Off for Rows and Columns.

 

advancedpivot ribbon donotshow grandtotals

 

Pivot Table Calculated Fields

You can add calculated fields to your pivot table.

  1. In the Ribbon, go to PivotTable Analyze > Calculations > Fields, Items & Sets > Calculated Field.

 

advancedpivot ribbon calculated field

 

  1. In the Name field, type in the name for the field, and then in the Formula box, type in the formula for your new field. Click Add to have the new field included in the Fields list below.

 

advancedpivot calculated field

 

  1. Click OK to add the field to your pivot table.

 

advancedpivot show calculated field

 

Move Pivot Table

When you create a pivot table, you are given the option (if you do not use a recommended pivot table) to insert the pivot table either into a new sheet, or into the sheet where you are currently working.

If you change your mind about placement, you can always move the table after it’s been created.

  1. In the Ribbon, go to PivotTable Analyze > Actions > Move Pivot Table.

 

advanced pivot ribbon move

 

  1. Choose where you want the pivot table to be placed. In this example, move the existing pivot table to the Sales Data worksheet.

 

advancedpivot move

 

  1. Click OK to move your pivot table.

 

advancedpivot moved

 

Insert Pivot Chart

You can create a chart based on your pivot table.

  1. Click on your pivot table, and then, in the Ribbon, go to PivotTable Analyze > Pivot Chart.

 

advancedpivot ribbon chart

 

  1. In the Insert Chart dialog box, choose the kind of chart and then choose the Style. Finally, click OK to insert the chart into your worksheet.

 

advancedpivot chart insert

 

  1. A chart is placed in your worksheet. To move the chart, click in the middle of the chart so the cursor changes to a small black cross. Then drag your chart to the desired location.

 

advancedpivot chart

 

AutoMacro | Excel Automation Made Easy | Free Trial

Pivot Table Default Options

Table Options

There are a number of default settings that are set when you create a pivot table. You can change these options in PivotTable Options.

  1. Click in your pivot table and then, in the Ribbon, go to PivotTable Analyze > PivotTable > Options.

 

advancedpivot options

 

  1. Change the PivotTable Name in the first field if you need to. This could prove useful if your file has multiple pivot tables.

 

advanced pivot options layout

 

  1. Click on the tab you need, and then set the options in that tab for the pivot table.
    In the Totals & Filter tab, for example, you can switch grand totals on or off for rows and columns. Tick Allow multiple filters per field to make this the default option for your pivot table.

 

advancedpivot totalsfilters

 

Pivot Table Styles

When you create a pivot table, a default style is automatically applied. You can change the table style to alter the appearance of your pivot table.

Apply a Pivot Table Style

  1. Select the pivot table, and then in the Ribbon, go to Design > PivotTable Styles. Click the up or down arrows to show preset style options in the style box. Then click on the style you want.

 

advancedpivot styles

 

  1. Alternatively, to see more styles, click the More button (the bottom down arrow).

 

advancedpivot styles more

 

This brings up a list of available styles.

 

advancedpivot stylelist

 

  1. Choose the one you want to apply it to your pivot table.

 

advancedpivot applied style

 

Create Custom Style

You can also create a custom style.

  1. At the bottom of the list of styles, click New PivotTable Style.

 

advancedpivot new style

 

  1. In the Name field, type in the name for your style and then in the Table Element list, choose the part you wish to format. Click Format.

 

advancedpivot name style

 

  1. Adjust the font, border, and/or fill formats, and then click OK.

 

advancedpivot format styles

 

  1. Notice that in the Table Element list, the elements you have formatted are now bold. A preview of your style is shown on the right side of the box. Click OK to add your new style to the Styles list.

 

advancedpivot created style

 

  1. Click in the PivotTable Styles box and choose a custom style to apply to your pivot table.

 

advancedpivot applied custom style

 

  1. To modify or delete a custom style, right-click it and choose another style option.

 

advancedpivot delete

 

Style Options

When you apply a pivot table style, a number of default options are automatically applied, such as Row Headers and Column Headers. In the Design tab, PivotTable Style Options group, you also have Banded Rows and Banded Columns available.

 

advancedpivot style options

 

  1. To apply the row formatting, tick the Banded Rows box. Notice that the preview in PivotTable Styles shows the rows being formatted with alternating colors for all the styles, while the style that is currently applied to your pivot table formats your pivot table with these colors.

 

advancedpivot banded

 

  1. Tick the Banded Columns box to also apply the column formatting to your pivot table.
  2. You can also use the style options to remove formatting on Row Headers and Column Headers.

 

advancedpivot no row header

 

Advanced Pivot Table Techniques in Google Sheets

Pivot tables in Google Sheets are not as advanced as those in Excel. You can change the data range, and you are able to add a filter to your pivot table.

AutoMacro | Excel Automation Made Easy | Free Trial

Update or Change the Data Range

  1. Make sure your cursor is on a cell within the pivot table, and then in the Pivot table editor, click the Select data range button.

 

advanced filter gs select data range

 

  1. Click in the box with the selected range. You can then select the range of data that you wish the pivot table to be based on.

 

advanced filter gs data range

 

  1. Click OK to apply the new or updated range to the pivot table.

Add Filter

Consider the following Google Sheets pivot table.

 

advanced filter gs pivot

 

  1. In the pivot table editor, click the Add button next to Filters, and then choose the field you wish to filter by (e.g., Region).

 

advanced filter gs add filter

 

  1. Click clear to uncheck all values, and then tick the value to filter for. Click OK.

 

advanced filter gs filter

 

The chosen filter is applied. Here, only data from the Boston region is included in the table.

 

advanced filter gs filtered

AI Formula Generator

Try for Free

See all How-To Articles