Top Hotkeys to Accelerate Your Pivot Table!


Pivot tables are one of the most powerful tools in Microsoft Excel for summarizing data so it’s helpful to learn a few shortcuts to work quickly with them.


1. Create a pivot table from the selected data

Select the data set and press Alt > N > V (this is a sequential shortcut so press Alt then N then V). A dialog box will appear with options to create a pivot table. In 2010, you’ll need to press Alt > N > V > T. You can normally select a cell in the data set as long as there are no blank rows or columns and Excel will highlight the entire data set.

2. Group Selected Pivot Table Items

Alt + Shift + Right Arrow

Let say you have a pivot table with months and you want to group the first six or last six months. All you need to do it select those six cells and use this shortcut key simply.


3. Ungroup Selected Pivot Table Items

Alt + Shift + Left Arrow

Just like you can create a group of items, this shortcut helps you ungroup those items from the group.


4. Hide Selected Item or Field

Ctrl + –

To use this shortcut key you need to select a cell from the value field column and when you press this shortcut key, it opens the Calculated Filed window.


6.Open Old Pivot Table Wizard

Alt + D and P

In this keyboard shortcut, you need to press the keys subsequently.


7. Open Field List for the Active Cell

Alt + Down Arrow

This key opens the field list.


8. Insert a Pivot Chart from a Pivot Table

Alt + F1

To use this keyboard shortcut, you need to select a cell from the pivot table. This key inserts a pivot chart into the existing sheet.


F11

And, if you want to inserts a pivot into a new worksheet then you need to use the above key only.


9.Refresh the current pivot table

Select a cell in the pivot table and press Alt + F5.


10. Refresh all pivot tables in the workbook

Press Ctrl + Alt + F5.


11. Create a pivot chart on the same sheet

Select a cell in a pivot table and press Alt + F1 to create a pivot chart on the current worksheet based on the current pivot table.


12.Create a pivot chart on a new sheet

Select a cell in a pivot table and press F11 to create a pivot chart on a new sheet based on the current pivot table.


13.Open the PivotTable and PivotChart Wizard

To open the legacy PivotTable and PivotChart Wizard (which contains other options such as Consolidate), press Alt > D > P (Alt then D then P). The legacy dialog box will appear.



Keep Learning. Keep Thriving!

Regards,

Mohammed Alfan










39 views0 comments