Here are some quick keyboard shortcuts in Excel to help you survive in the pivot table jungle.
Keyboard shortcut to Sort Fields and Items in a Pivot Table
Thought that you would have to drag the field or the item across the pivot table using the mouse. Hold on – you can use the keyboard too.
Simply type the field or the item name (that you would like to move) over an existing field. Viola. The new field moves to where you typed its name and the older field moves to the next position. So no more precision mouse maneuvers to drag and place the field exactly where you want – this one does the job for you.
Shortcut to Hide Fields and Items in a Pivot Table
Select the field and press ctrl –. Works for pivot fields (row, column and page), pivot items and the even the subtotals. So now you don’t have to go to the field headers and use the drop-down to filter the pivot table – just use ‘crtl -‘. You can even hide an entire field (page, row or column) and not just a part of it using this shortcut. And in Pivot Tables in Excel 2003, the field subtotals and the the grand totals for row or columns can also be hidden using this. Pretty useful huh !
… And now how to unhide the Fields and Items
So now you know how to hide items using the keyboard. How do you turn them on. Elementary – dear watson. Just type the name of the field or the pivot item over an existing one.
Using the keyboard to Add New Fields and Items in a Pivot Table
Select a field header and type the name of the new field over it. The older field header shifts to the right and the new pivot field takes its place. (This one doesn’t appear to work with Excel 2010 though)
Group Items in a Pivot Table
Select a group of items in a pivot table and then use the right click button to group them. You can create your own customized groups in this manner. To change the name, just type a new more descriptive name over the deault one assigned by excel.
Insert Calculated Field in a Pivot Table
Select any cell in the data field and presss SHIFT + CTRL + = (Shift key then Ctrl key then =). The insert calculated field dialog box will open up.
Insert Calculated Item in a Pivot Table
Select any pivot field header (the label for the pivot field) and presss SHIFT + CTRL + = (Shift key then Ctrl key then =). The insert calculated item dialog box will open up.
And now here’s the bonus – a list of more than 125 keyboard shortcuts in Excel
Great tip!! I had no idea you could do this.
December 4th, 2009 at 12:42 am@Michael – Thanks.
December 4th, 2009 at 5:38 amThank you. I can understand all of your examples. I have been trying to understand arrays for a long time and your explanation made it so easy.
October 6th, 2010 at 11:26 pmNice tips, cheers.
Loving the “Viola” exclamation. Don’t know if it was intentional but it made me chuckle.
November 8th, 2010 at 8:18 amThis is a wonderful site, needing some brushing up on Excel and this site is very useful and simple to follow. Keep up the good work.
August 19th, 2014 at 7:07 am