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