Contextures

Pivot Table Shortcuts

Use these keyboard shortcuts to save time when working with pivot tables. There is a reference chart with all the shortcuts, and notes on using some of the shortcuts.

Keyboard Shortcut Notes

There is a Pivot Table Shortcut Reference Chart in the next section, and here are a few details on using those pivot table keyboard shortcuts.

Select Pivot Table Range

To select the entire pivot table body (not the report filter range), follow these steps:

  1. Select any cell in the body of the pivot table
  2. Then use one of the following shortcuts:
  • Ctrl + A
  • Ctrl + * (on the number keypad)
  • Ctrl + Shift + 8 (number at top of keyboard)

select entire pivot table

NOTE: If a cell in the Report Filter range is selected, that range will be selected, and none of the pivot table body range cells.

Create Calculated Field

To open the Calculated Field dialog box, select any field in the Values area, then press Chtr + Shift + = (equal sign)

calculated field

Create Calculated Item

To open the Calculated Item dialog box, select the Row or Column field heading where you want to add the item, then press Chtr + Shift + = (equal sign)

calculated item

NOTE: If you select a Row or Column item, instead of a heading, a calculated item will be added automatically. In the screen shot below, "Formula1" was created.

calculated item

Group Pivot Items

To group pivot items, select them on the worksheet, then press Alt + Shift + Right Arrow

The selected pivot items can be in adjacent cells, as in the screen shot below, or non-adjacent cells (select the first cell, then press the Ctrl key while selecting additional pivot item cells)

calculated item

Ungroup Pivot Items

To ungroup pivot items, select one or more groups on the worksheet, then press Alt + Shift + Left Arrow

The selected grouped pivot items can be in adjacent cell, or non-adjacent cells (select the first cell, then press the Ctrl key while selecting additional pivot item cells)

Notes:

  • Blank cells between the selected groups will be ignored.
  • If all groups in the same grouping level are ungrouped, the grouping level will disappear.
  • Otherwise, the ungrouped items are shown in the grouping level, individually.
  • For example, in this screen shot, Group1 was ungrouped, so Bars and Cookies are shown individually, along with the remaining group, Group2.

calculated item

Pivot Table Keyboard Shortcuts Reference Chart

Here is a quick reference chart for some pivot table keyboard shortcuts. For example, press Ctrl + - (minus sign) to hide the item in the selected pivot table cell.

hide the item in the selected pivot table cell

See a few notes in the above section, or click the links in the reference table Notes column below.

Shortcut Action Notes
Ctrl + Shift + * Select entire pivot table (not including Report Filters) Select Pivot Table Range
Ctrl + A Select entire pivot table (not including Report Filters) Select Pivot Table Range
Spacebar Add or remove checkmark for selected field in PivotTable Field List  
Alt + Shift + Right Arrow Group selected pivot table items Group Pivot Items
Alt + Shift + Left Arrow Ungroup selected pivot table items Ungroup Pivot Items
Down Arrow Select next item in PivotTable Field List or Items List  
Up Arrow Select previous item in PivotTable Field List or Items List  
End Select last item in PivotTable Field List or Items List  
Home Select first item in PivotTable Field List or Items List  
Alt + Down Arrow Open field list for active cell  
Ctrl + – Hide selected item or field  
Shift + Ctrl + = When data field selected, opens Calculated Field dialog box Create Calculated Field
Shift + Ctrl + = When field heading cell selected, opens Calculated Item dialog box Create Calculated Item
Alt + D, P Open the old PivotTable Wizard  

Video: Create a Pivot Table

Watch this video, to see the steps for creating a pivot table in Excel 2013 and later.

There are written instructions on the Create a Pivot Table page.

Get the Shortcut List

Get an Excel file with a list of pivot table shortcuts.

The zipped file is in xlsx format, and there are NO macros in the workbook.

 

Last updated: July 12, 2021 11:42 AM