Pivot tables are used by hundreds of thousands of people every day to analyze and summarize large amounts of data.
Pivot Tables are fairly easy to use, and most Excel users get the hang of it after using it a couple of times.
So if you’re a fan of Pivot Tables and use them quite often, it would be helpful to learn some common Pivot Table keyboard shortcuts that you can use to speed up your work.
In this article, I will give you some common Pivot Table shortcuts that you can learn to become even more efficient.
Excel Pivot Table Shortcuts
While I will cover each Pivot Table shortcut in detail later, in case you want a quick rundown, below is the table to show you a list of shortcuts.
| What it Does | Pivot Table Shortcut |
|---|---|
| Create a Pivot Table | ALT + N + V |
| Launch Old Pivot Table Wizard | ALT + D + P |
| Select the Entire Pivot Table | Ctrl + A |
| Toggle Checkboxes in Pivot Table Fields List | Spacebar |
| Group Selected Pivot Table Items | ALT + Shift + Right Arrow Key |
| Ungroup Selected Pivot Table Items | ALT + Shift + Left Arrow Key |
| Add a Pivot Chart to the Current worksheet | ALT + F1 |
| Add Pivot Chart to a New Sheet | F11 |
| Hide Item from the Pivot Table | Ctrl + Minus |
| Create a Calculated Item/field | Ctrl + Shift + = |
| View/Hide Field List | ALT + J + T + L |
Now let’s deep dive into each of these shortcuts, where I’ll show you how to use them with Pivot Tables.
Shortcut to Create a Pivot Table From Data
Below is the shortcut to insert a new Pivot table in a new worksheet
ALT + N + V + T + Enter
To use the keyboard shortcut:
- Select any cell in the dataset from which you want to create the Pivot Table
- Use the shortcut ALT + N + V + T + Enter (press these keys one after the other)
When you use ALT + N + V + T, it will open the ‘PivotTable from table or range’ dialog box as shown below:
And then, when you hit Enter key, it inserts a new Pivot table in a new worksheet.
Shortcut to Opening the Old Pivot Table Wizard
You can use the keyboard shortcut ALT + D + P to open the old Pivot Table Wizard (shown below).
While most people use the regular dialog box to insert the Pivot Table, many experienced/advanced Excel users still prefer the old Pivot Table Wizard.
Also read: Preparing Source Data For Pivot Table
Shortcut to Select the Entire Pivot Table
If you want to select the entire pivot table, you can use the below shortcut:
Control + A
You first need to select any cell in the Pivot Table and then use the above shortcut
Note that this shortcut would only select the Pivot Table and not the Report Filters (in case you have report filters activated in your Pivot Table)
Shortcut to Toggle Checkboxes in Pivot Table Fields List
There are checkboxes in multiple places when working with Pivot Tables (such as in the Pivot Table Field list pane or in the filter options when you click on the filter icons in the columns)
You can use the spacebar to check or uncheck these checkboxes with your keyboard (it works as a toggle)
Spacebar
Shortcut to Group/Ungroup Selected Pivot Table Items
Below is the keyboard shortcut to group two or more items in a pivot table
ALT + SHIFt + Right Arrow Key
And if you want to ungroup the already grouped items, you can use the below keyboard shortcut
ALT + SHIFt + Left Arrow Key
To use this shortcut:
- Select the items that you want to group
- Hold the ALT and the SHIFT key and then press the right arrow key
And if you want to ungroup items, select the cells that have the group, hold the ALT and the SHIFT key and then press the Left arrow key.
Also read: How to Group Dates in Pivot Tables in Excel (by Years, Months, Weeks)
Shortcut to Insert a Pivot Chart
Below is the shortcut you can use to insert the Pivot Chart in the existing worksheet where you already have the pivot table
ALT + F1
And in case you want to insert a new chart sheet that contains the Pivot Chart, you can use the below keyboard shortcut.
F11
To use these shortcuts, you first need to select any cell in the Pivot Table and then press the keyboard shortcut keys.
Shortcut to Hide Item from the Pivot Table
If you do not want to show some items in your pivot table, you can hide them using the below shortcut:
CONTROL –
To use this shortcut, first select the items that you want to hide, then hold the Control key and then press the Minus key.
Below is the dataset where I selected the ‘Multiline’ option in the Pivot Table and used the shortcut Control –
And this is what I got (where the item has been hidden).
When you use the above shortcut, Excel does not hide the record or the row. It actually filters your data set where the items that you selected before using the shortcut would be filtered out, and the remaining would be shown.
If you want to get the hidden items back, click on the filter icon that appears in the Pivot Table Field list, and check all the options.
Shortcut to Create a Calculated Item/Field
Below is the shortcut to open the dialog box to insert a new calculated field or calculated item in a Pivot Table in Excel
Ctrl + Shift + =
To insert a Calculated Field, select any of the column headers that contain the values (such as Sum of Sales or Sum of Profit), and then use the above shortcut (hold the control and the shift key and then press the equal to key)
And if you want to insert a calculated item, select the row label and then use the same shortcut.
The above shortcuts would open the calculated field or calculated item dialog box.
Also read: How to Add and Use an Excel Pivot Table Calculated Field
Shortcut to View/Hide Pivot Table Field List
Pivot Table Field list is a pane that opens on the right where you see all the fields that you can drag and make the Rows/Columns, Values, or Filters (as shown below).
Sometimes, the Pivot Table Fields pane disappears, and Excel users have no idea how to get it back.
Below is the shortcut you can use to get the pivot table field list to reappear:
ALT + J + T + L
To use the shortcut, select any cell in the Pivot Table and then press these keys one after the other (in succession)
This shortcut works as a toggle, so if you do not see your PivotTable Fields pane and you use the above shortcut, it is going to make it reappear, otherwise, it is going to hide it.
In this article, I covered the most important Pivot Table shortcuts that you can learn to be more efficient when working with it.
I hope you found this article useful!
Other Excel articles you may also like:
