Excel Pivot Table Shortcuts That Save Time

A handful of pivot table shortcuts in Excel cover almost everything you do with a pivot: create it, refresh it, look at the rows behind a number, group items and open the right menu without hunting through the ribbon. This guide lists the ones worth learning, explains what each does, and notes what works on a Mac. It's for anyone who builds pivots at work or has a pivot task in a job test.

If you're new to pivots, start with how to build your first pivot table and come back here once the basics feel easy.

The pivot table shortcuts to learn first#

These are the shortcuts that save the most time. Most are Windows-only because they use the Alt key; the Mac column gives the quickest alternative.

"Alt, N, V" means press and release each key in turn. These are KeyTips: the letters Excel shows on the ribbon when you press Alt. The full list of Excel keys is in the Excel keyboard shortcuts cheat sheet.

How to create a pivot table with the keyboard#

To create a pivot from the keyboard, click any cell in your data and press Alt, N, V. In recent Microsoft 365 versions the PivotTable button has a small menu, so press T for From Table/Range. The Create PivotTable box opens with your data range already filled in. Press Enter to put the pivot on a new sheet.

The older sequence Alt, D, P still works on Windows. It opens the classic PivotTable and PivotChart Wizard from very old Excel versions. Some people prefer it because it can combine several ranges, but most of the time Alt, N, V is all you need.

Tip: turn your data into a table first with Ctrl+T (Mac: ⌘T). The pivot then picks up new rows on the next refresh, and you never need to change the source range.

How to refresh a pivot table fast#

A pivot table does not update by itself when the data changes. Press Alt+F5 to refresh the pivot you're in, or Ctrl+Alt+F5 to refresh every pivot and data connection in the workbook.

On a Mac, right-click the pivot and choose Refresh, or use PivotTable Analyze > Refresh. Data > Refresh All updates everything at once.

If new rows still don't show after a refresh, the pivot's source range probably stops above them. The guide on refreshing a pivot table and fixing missing rows covers Change Data Source and the setting that refreshes pivots each time the file opens.

Drill down: double-click to see the rows behind a number#

Double-click any value in a pivot to see where it came from. Excel adds a new sheet with every source row that makes up that number. This is called Show Details, or drilling down, and it works the same on Windows and Mac.

Use it to check a strange total, to find which orders make up a region's sales, or to answer "which rows are these?" in a meeting. The new sheet is a copy, so delete it when you're done. Changing it does not change the pivot or the source.

If double-clicking does nothing, someone has turned the feature off. Go to PivotTable Analyze > Options > Data and tick Enable show details.

Group, hide and filter items without the mouse#

Grouping and hiding items are the shortcuts that feel like magic the first time.

• Group items. Select two or more row labels, such as three product names, and press Alt+Shift+Right arrow. Excel puts them into a new group called Group1. Click the label and type a better name.
• Group dates. Click one date in the Rows area and press Alt+Shift+Right arrow. The Grouping box opens. Pick Months, Quarters or Years and press Enter.
• Ungroup. Select the group and press Alt+Shift+Left arrow.
• Hide an item. Select a row label, such as a region you don't need, and press Ctrl+Minus. Excel filters it out. To bring it back, open the filter arrow and tick it again.
• Open the filter menu. Select the Row Labels header and press Alt+Down arrow. Use the arrow keys to sort or tick items.

For filters that other people can click, add slicers. The guide to filtering pivot tables with slicers shows how to set them up.

Rename fields and open menus quickly#

Two small habits make a pivot look finished in seconds.

Type over a header to rename it. Click a cell such as Sum of Sales, type Total sales and press Enter. You can't use the exact name of a source column, like Sales, because Excel says the field name already exists. Add a space at the end, Sales , if you really want that name.

Use the right-click menu from the keyboard. Press Shift+F10 (or the Menu key) on any pivot cell. From there you can reach Refresh, Sort, Value Field Settings, Show Values As and Expand/Collapse. On a Mac, Control-click the cell.

To show or hide the PivotTable Fields pane on Windows, press Alt, J, T, L while a pivot cell is selected, or right-click and choose Show Field List.

One more tip: format numbers through Value Field Settings > Number Format, not Ctrl+1. Formatting applied with Ctrl+1 can disappear when the pivot changes shape.

Check a pivot number with a formula#

When a pivot number matters, check it once with a formula. SUMIFS adds up the rows that meet every condition, so it should match the pivot cell exactly.

If the pivot shows 2,150 where West meets Laptop, this formula should agree:

=SUMIFS(C2:C7,A2:A7,"West",B2:B7,"Laptop")

The result is 2150 (1,200 + 950). If the numbers differ, the pivot probably needs a refresh, or the source range misses some rows. Interviewers like this check, and it often comes up in pivot table interview questions. For a deeper look at when a formula beats a pivot, see pivot table vs SUMIFS.

Practise it#

Shortcuts stick when your fingers learn them on a real pivot. Build your first pivot table in a live spreadsheet, then move on to subtotals, report filters and % of total in Pivot tables 2. Each step checks your work instantly. When you're ready, try the data analyst Excel test to use pivots under time pressure.