Home > Pivot > Create > Shortcuts

Pivot Table Shortcuts

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

hide the item in the selected pivot table cell

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 including 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 keyboard shortcuts, to select all the body cells:
    • 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

Create Pivot Chart

To quickly create an Excel pivot chart, on the same sheet as the pivot table, or on a separate sheet, follow these steps:

  • Select any cell in the pivot table
  • On the keyboard, press either one of these shortcuts:
    • To create a pivot chart on a separate chart sheet, press the F11 key
    • To insert a pivot chart embedded on the pivot table sheet, press the Alt key, then tap the F1 key

This 1-minute video shows the steps for creating a pivot chart with both of these keyboard shortcuts

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.

Pivot Table Mouse Shortcuts

In addition to all the pivot table keyboard shortcuts, there are a few mouse shortcuts that can save you time too.

Shortcut to Expand and Collapse

After you set up a pivot table, you can use the plus and minus buttons to show or hide the pivot table details. These are called the Expand and Collapse buttons.

expand and collapse buttons

Another way to show or hide details is use your mouse scroll wheel -- I find that easier than trying to click on a tiny button.

To try this mouse wheel shortcut for pivot table details, follow these steps:

  • In a pivot table, point to a cell in the Row or Column area.
  • To expand, press Shift and scroll UP with the mouse wheel
  • To collapse, press Shift and scroll DOWN with the mouse wheel

TIP: When collapsing, point to a cell near the top of the pivot table, so the pointer doesn’t end up outside of the pivot table range.

To see what happens in different parts of the pivot table, when you expand and collapse, see details on my pivot table blog.

Open Field Settings Dialog Box

When you're setting up a pivot table, you sometimes need to open the Field Settings dialog box, to change the field layout and print settings, or the subtotals and filters options.

To quickly open the field settings dialog box, use this mouse shortcut:

  • With your mouse, point to the outer border of a pivot table row or column heading
    • This shortcut does not work for pivot table value fields
  • When the pointer changes to a thick black arrow, double-click, to open the Field Settings dialog box.

double-click when pointer changes to thick black arrow

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: February 4, 2024 3:36 PM