Top 10 Excel Pivot Table Keyboard Shortcuts
Using programs like Microsoft Excel, pivot tables are handy for highlighting key data. They let you “pivot,” changing data direction for fresh insights. By grouping similar data and applying operations like total or average, pivot tables summarize large datasets efficiently. However, dealing with massive data can be tedious. Knowing keyboard shortcuts becomes valuable, speeding up common tasks and saving time. These shortcuts enhance work speed and reduce the likelihood of mistakes, common in manual processes. Tools also minimize human errors like choosing the wrong item or pushing the wrong button, improving overall efficiency and accuracy in data handling.
Excel Pivot Table Shortcuts
Shortcuts |
Action |
---|---|
Alt + N + V + T + Enter |
Create a PivotTable from selected data |
Alt + Shift + F10 |
Opens the context menu for the selected cell in the PivotTable |
Up/Down/Left/Right arrows |
Navigate within the context menu |
Enter |
Selects the highlighted option in the context menu |
Tab |
Switch between the PivotTable fields list and the data area |
Alt + Down Arrow |
Opens the dropdown menu for a cell with options to filter data |
Up/Down arrows |
Select options within the dropdown menu |
Enter |
Apply the selected filter |
Esc |
Close the dropdown menu without applying any filter |
Ctrl + Tab |
Switch between worksheets |
Ctrl + Alt + F5 |
Refresh all PivotTables in the workbook Refresh all PivotTables in the workbook |
Shortcut to Create a Pivot Table From Data
You can quickly make a Pivot Table in Excel with the data you choose by pressing this keyboard button.
Step 1: Open the Excel spreadsheet
Click on the Windows start icon on the down left corner of the screen. Now click on Microsoft Excel to open the spreadsheet.
Step 2: Select the Data
Right-click on your mouse to select the cells that contain the data.
Step 3: Press Alt, N, V, T.
After selecting the cells press Alt, N, V, T.
Step 4: A pivot table is built into a new worksheet.
Excel will automatically build a Pivot Table from your chosen data in a new worksheet.
Shortcut to Open the Old Pivot Table
This method can be used to get to the “PivotTable Field” text box that was in earlier versions of Excel.
Step 1: Open the Excel spreadsheet that contains the Pivot table
Open the excel spreadsheet where you’ve created the Pivot table.
Step 2: Left-Click to select Pivot Table
Left-click inside the existing data to select the Pivot Table.
Step 3: Press the Alt then JT, P, O.
Press Alt, then JT, P, O on your keyboard.
Step 4: The “PivotTable Field” dialog box will open
The ‘PivotTable Field’ dialog box will open to adjust PivotTable layout and parameters.
Step 5: Select the old pivotTable
From the dialogue box select the pivot table you want to open.
Shortcut to Select the Entire Pivot Table
With Ctrl + A, you may select all the data and headers in a PivotTable without leaving the table.
Select an entire pivot table (including report filters):
Step 1: Open your PivotTable-containing Excel sheet.
Open your Excel workbook containing the PivotTable.
Step 2: Click inside the PivotTable.
Select a cell inside the pivotTable
Step 3: Press Ctrl + A button.
Press Ctrl then press A button.
Select an entire pivot table (not including report filters):
Step 1: Open your PivotTable-containing Excel sheet.
Open your Excel workbook containing the PivotTable.
Step 2: Click inside the PivotTable.
Select a cell inside the pivotTable
Step 3: Press Ctrl + Shift + ‘*’, or Ctrl + ‘*’
Ctrl + Shift + * or Ctrl + * selects the current area, which normally excludes report filters.
Shortcut to Toggle Checkboxes in Pivot Table Fields List
Step 1: Open Excel workbook with the PivotTable.
Open the PivotTable in your Excel worksheet.
Step 2: Click inside the PivotTable
Click anywhere inside the PivotTable to activate it.
Step 3: Press Alt, JT, O, D to open the PivotTable Field List.
Press Alt, JT, O, and D to open the PivotTable Field List if it’s not already there.
Step 4: Use keyboard arrows to choose the checkbox field.
Go to the field with the checkbox to toggle using the keyboard arrow keys.
Step 5: Select field, press the Spacebar
Select the field and press the Spacebar on your keyboard.
The PivotTable-included or excluded field will be indicated by the checkbox beside it.
Shortcut to Create a Calculated Item/Field
This shortcut opens the “Insert Calculated Field” dialogue box, where users may add PivotTable calculated items or fields. You may use PivotTable’s computed fields to calculate existing data.
Step 1: Open Excel workbook PivotTable.
Open the excel workbook with PivotTable.
Step 2: Click Pivot Table to insert the calculated field.
Click on the Pivot Table anywhere.
Step 3: Press Alt key and click Alt + JT + J keys in succession
Press Press Alt + JT + I keys then The “Insert Calculated Field” dialog box will appear.
Step 4: Enter a “name” and “Formula” for your calculated field
Enter a name like “Total Sales Per Unit Price” in the dialog box’s “Name” field. Input the formula to calculate total sales per unit price in the provided space.
Step 5: Click “OK”
Click “OK” to insert the calculated field.
Shortcut to View/Hide Pivot Table Field List
This keyboard shortcut displays or hides the PivotTable Field List. Users may simply edit the PivotTable by adding, deleting, and rearranging fields in this panel. A simple method to return to the pivot table’s list is as follows:
Step 1: Open your workbook.
Open your excel workbook and click anywhere in the cells that contain data in the pivot table.
Step 2: Press Alt key then press J, T,l , and keys
Press Alt key then press J, T,l and D keys on your keyboard.
Step 3: Release the Alt key.
The PivotTable Field List will either appear if it was hidden or disappear if it was visible.
Shortcut to Refresh the current pivot table
A data refresh is a computer shortcut that brings all changes made to the source data into the current PivotTable.
Step 1: Open the Microsoft Excel workbook
Open the Microsoft Excel spreadsheet that consists of the PivotTable.
Step 2: Click at the Alt and F5 key.
Enter the formula Alt+F5.
Step 3: Stop pressing the Alt key.
The PivotTable will be refreshed, extracting the latest data from the connected source.
Shortcut to Create a pivot chart on the same sheet/new sheet
Quickly create a pivot chart using the current PivotTable using these shortcuts. You may put the pivot chart on a new sheet or the pivot table.
Creating a Pivot Chart on the Same Sheet
Step 1: Open Excel with PivotTable.
Open your Excel workbook containing the PivotTable you want to create the Pivot Chart from.
Step 2: Click the PivotTable
Click anywhere inside the PivotTable that you want to visualize with the Pivot Chart.
Step 3: Press Alt key.
Long press the Alt key on your keyboard.
Step 4: While holding the Alt key JT,C
Press J,T and C Then S key on your keyboard. The Pivot Chart and PivotTable will be made on the same sheet.
Creating a Pivot Chart on a New Sheet
Step 1: Open Excel with PivotTable.
Open your Excel workbook containing the PivotTable.
Step 2: Click the PivotTable
Click anywhere inside the PivotTable.
Step 3: Press Alt key.
Long press the Alt key on your keyboard.
Step 4: Hold Alt key then press N+ V+ N keys.
Press N, then V and in last press N after the Alt key.
Shortcut to Group /Ungroup selected pivot table items
Grouping Selected Pivot Table Items:
Step 1: Open your Excel workbook
Go to the Microsoft office and select MS Excel from the list.
Step 2: Select the data
Click and drag to select the cells data that you want to group.
Step 3: Press ALT + SHIFt + Right Arrow Key
Click the ALT key in the keyboard then press shift and Right arrow button.
Step 4: Release the Alt key.
Release the Alt key. The selected items will be grouped into the specified interval
Ungrouping Selected Pivot Table Items
Step 1: Open your Excel workbook
Go to the Microsoft office and select MS Excel from the list.
Step 2: Select the data
Click and drag to select the cells data that you want to group.
Step 3: Press ALT + SHIFT + Left arrow button
Press and hold the ALT then SHIFt and then press Left Arrow keys in succession.
Step 4: Release the Alt key.
The selected items will be ungrouped, returning to their original state.
Shortcut to Hide Item from the Pivot Table
This keyboard shortcut makes the selected item invisible in the PivotTable..
Step 1: Click the PivotTable.
Click anywhere in the pivot table.
Step 2: Click on the cell or column.
Select the cell or column in the excel.
Step 3: Press Ctrl + (minus sign) the selected cell will be hidden.
The selected cell will be hidden after pressing CTRL and minus sign.
Also Read
Conclusion
In short, knowing Excel pivot table shortcuts helps you work faster and smarter. For example, pressing Alt + N, V, T quickly creates a table, while Ctrl + – hides items. These shortcuts save time and make tasks easier. By learning them, you can navigate and manage pivot tables more efficiently, getting your work done quicker. These shortcuts may greatly improve your data management in Excel, therefore it’s worth your effort to master them if you use the programme often.
Pivot Table Keyboard Shortcuts – FAQs
How do I list the top 10 in a PivotTable?
- Click the PivotTable to activate it.
- Locate the field you want to filter by in the Rows or Columns section. Click the dropdown arrow next to its field header.
- From the dropdown menu, navigate to “Value Filters” and then select “Top 10”.
- A “Top 10 Filter” window will appear. Here, you can customize the filter:
- Click “OK” to apply the filter.
What is Alt F5 in PivotTable?
If you press Alt + F5 on your keyboard in Microsoft Excel, the data in a PivotTable will be updated. To add changes to the PivotTable, press Alt + F5. If the source data has changed since the last restart, Excel will do so.
What are the 20 shortcut keys in Excel?
- Ctrl + N: It creates a new workbook.
- Ctrl + O: Open an existing workbook.
- Ctrl + S: Save the active workbook.
- Ctrl + P: Print the active sheet.
- Ctrl + Z: Undo the last action.
- Ctrl + Y: Redo the last action.
- Ctrl + X: Cut selected cells.
- Ctrl + C: Copy selected cells.
- Ctrl + V: Paste copied or cut cells.
- Ctrl + F: Use the “Find” dialogue box to find content..
- Ctrl + H: open the “Replace” box.
- Ctrl + A: Select the entire worksheet or the current region..
- Ctrl + Home: Get back to the top of the spreadsheet..
- Ctrl + End: Step to the worksheet’s last data cell.
- Ctrl + Arrow Keys (Up, Down, Left, Right): Get to the edge of the present data area..
- Ctrl + Page Up / Page Down: Change worksheet tabs..
- Ctrl + Shift + L: Enable or disable the filter for the specified range.
- Ctrl + Shift + Arrow Keys: Expand the selection to the data region’s edge..
Contact Us