Excel Hack

Pivot table formula shortcut

When you use pivot tables to aggregate a database, you need to update the pivot tables when you change the values in the database. This article describes how to use a shortcut key to update a pivot table. This shortcut key also applies the update to formulas that are created by referencing values in the pivot table.

Shortcut for updating a pivot table

To update a specific pivot table

Windows
Alt + F5

Mac
None

Update all pivot tables in the book

Windows
Ctrl + Alt + F5

Mac
None

Update pivot tables with shortcut keys

Use the shortcut keys to update the pivot table. In this section, we will use the pivot table that summarizes the number of times each country has won the Tour de France.

Total Time: 1 minute

Enter a formula

Enter a formula

Enter the formula for finding the number of times only Western European countries have won in D4.

Select the row you want to delete

Select the row you want to delete

Return to the database. To delete a part of the database, select the cell of the row you want to delete.

Select entire row

Select entire row

Press Shift + Enter to select the entire row.

Delete data

Delete data

Press Alt + H + D + D to delete the selected row.

Return to pivot table

Return to pivot table

Return to the pivot table. Changes to the database will not be automatically reflected in the pivot table.

Update the pivot table

Update the pivot table

Select a cell (ex A4) in the pivot table and press Alt + F5 to refresh the pivot table. if you select the D4 cell, you will see that the formula has not changed, only the values. If you want to update all the pivot tables in the book, press Ctrl + Alt + F5.

Comments

Please send us your comments on this article.

0 comments
Inline Feedbacks
View all comments