Sometimes it’s quicker to use a keyboard shortcut, instead of the mouse, to accomplish a task in Excel. Here are some of the keyboard shortcuts for working with pivot tables in Excel 2007 and Excel 2003.
Excel 2007 Pivot Table Keyboard Shortcuts
| Shortcut | Action |
| Ctrl + Shift + * | Select entire pivot table (not including Report Filters) |
| Ctrl + A | Select entire pivot table (not including Report Filters) |
| Spacebar | Add or remove checkmark for selected field in PivotTable Field List |
| Alt + Shift + Right Arrow | Group selected pivot table items |
| Alt + Shift + Left Arrow | Ungroup selected pivot table 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 |
| Shift + Ctrl + = | When field heading cell selected, opens Calculated Item dialog box |
| Alt + D, P | Open the old PivotTable Wizard |
Excel 2003 Pivot Table Keyboard Shortcuts
In Pivot Table
| Shortcut | Action |
| Alt + L | Show Pivot Table dialog box |
| Alt + R | move selected field into Row area |
| Alt + D | move selected field into Data area |
| Shift + Right Arrow | Group selected pivot table items |
| Alt + Shift + Left Arrow | Ungroup selected pivot table items |
| Down Arrow | Select next item in Items List |
| Up Arrow | Select previous item in Items List |
| End | Select last visible item in List |
| Home | Select first visible item in 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 |
| Shift + Ctrl + = | When field heading cell selected, opens Calculated Item dialog box |
In Pivot Table Wizard
| Shortcut | Action |
| Alt + C | move selected field into Column area |
| Alt + R | move selected field into Row area |
| Alt + D | move selected field into Data area |
| Alt + P | move selected field into Page area |
_____________
Hi,
I’m urgently looking for a keyboard shortcut to expand/collapse fields in a Pivot table.
Can you help here?
Thx
Franz
There are no built-in shortcuts for Expand/Collapse, but you could use keys to activate the Ribbon commands.
With a pivot table field cell selected, press Alt+A to activate the Analyze tab on the Ribbon.
Then, tap the H key to Collapse
Or, tap the J key to Expand
Actually, for Expand and Collapse on the current “cell”, as if you were clicking the plus or minus sign next to an item, record two macros and put this line into it:
ActiveCell.PivotItem.ShowDetail = True (for expand)
ActiveCell.PivotItem.ShowDetail = False (for collapse)
The thing to do is to use buttons Debra Dalgleish describes below (Hide Detail and Show Detail) but right click on them and add them to your Quick Access Toolbar. If for example you add them in positions 1 for Hide and 2 for Show, then you can use Alt-1 and Alt-2 to hide and expand, which isn’t too bad.
If you really want to take it further, you can then use a program like AutoHotkey to map other keys to Alt-1 and Alt-2.
Good – this helps so far,
thx
🙂
Hi Debra,
Great shortcuts, thanks for putting these together!
I noticed something small…the article intro talks about shortcuts for 2007 and 2003, but the subheads above both begin “Excel 2003…”
Dave
Thanks Dave, it’s fixed now.
Hi,
Could you please help me in shortcut for “Change Data Source”. I want to know the excel 2003 shortcut.
Thanks,
aganesan99
Hi, If there is any short cuts for Excel 2007 Pivot Table,
My requirements is given below
Show Pivot Table dialog box
move selected field into Row area
move selected field into Data area
Group selected pivot table items
Ungroup selected pivot table items
Select next item in Items List
Select previous item in Items List
Select last visible item in List
Select first visible item in List
Open field list for active cell
Hide selected item or field
When data field selected, opens Calculated Field dialog box
When field heading cell selected, opens Calculated Item dialog box
Please help me
There is a page on the Microsoft website with Excel shortcuts for different versions.
On that page, click on the 2007 tab, to see what is available for you.
Hi
how to merge two or more pivot table .. pls explain with examples ..
hi, I want to copy the function from the previous column and apply it to the next. For example, applying the “show as percentage of column total” on large pivot with about 30 columns.
There isn’t a built-in feature or shortcut that will do that, unfortunately.
If you have a copy of my Pivot Power Premium add-in, it has a command that changes all the selected columns to a specific Show Values As setting. http://www.contextures.com/xlPivotPremAddIn.html
Otherwise, you’ll have to change each column individually, or write a macro to change them.
How do I move datas in the pivot table from right to left or vice versa? Is there any keyboard shortcuts for move and place like cut (ctrl c) and paste (ctrl v)? Please advise. Thank you.
Hi
what is the keyboard shortcut to select the Pivot table items only and not the headers