Excel: Refreshpivot tables

Excel refresh:YOUR OBJECTIVE

When you change data in the Excel source table, any pivot table derived from that source table will not be updated automatically. For that reason, you will sometimes want to refresh your pivot table manually.

This learning module “ Excel refresh” shows you how to manually update a pivot table.

Excel: Refresh pivot tables HOW DOES IT WORK?

It is common for data in an Excel table to be modified or that more data be added to the table. But if you generated a pivot table on the basis of the data in such a table, you should know such changes are not automatically reflected in the pivot table’s field names, data displays or calculations. To obtain correct results in the PivotTable when your source table changes, you must refresh your pivot table manually.

Excelrefreshmanual

It is not in the least unusual that changes be made to a source table that serves as the basis for a pivot table. When that happens, however, you will undoubtedly want to update, or refresh, your pivot table. To do so, first activate the pivot table by clicking anywhere on it. The PivotTable Tools context tab, with its subordinated Options and Design tabs, appears. Now go to the Options tab. There, in the Data group, click the excel Refresh button. The changes made to the source table will then be reflected in the pivot table. In the following illustration, you can see that value of an order from Banner Car Parts was modified from $372.34 to $572.34. Because you subsequently activated the pivot table and refreshed it, the pivot table, as you can see in the illustration, changed to reflect the new amount.

Excel refresh: HINT

Refreshing any one pivot table in your workbook causes all pivot tables in your workbook to be refreshed at the same time. This means, if you created several pivot tables based on a source table, they will be updated together.

Excel refresh automaticwhen opening a file

If you like, you can also specify that a pivot table is to be refreshed whenever you open the Excel file in which it resides. To enable this function, go first to the Options tab under PivotTable Tools. Then, click the  button in the PivotTable group. The PivotTable Options dialog box will appear. On its Data tab, activate the third option: Refresh data when opening the file.

Apply your option and and close the dialog box by clicking  . Now whenever you open the file, the pivot table will be refreshed.

Excel Redefine the source range

When you created your pivot table, you had to specify the range of cells in the source table on which the pivot table is based.

If you add more data – to the end of your customer orders table, for example – these new cells will not automatically be included in the existing pivot table. In case you modify the data source of the pivot table, you should must subsequently extend the range of cells that the pivot table ‘sees’. To do this, go first to the Options tab under PivotTable Tools. Then, in the Data group, click the button . This opens the Change PivotTable Data Source dialog box where the source table or source table and relevant range of cells is displayed.

If the displayed source table and data range are correct, click  . If they are incorrect or incomplete, however, go to the source table and use your keyboard or mouse to select the new data range. Confirm your selection by clicking((Bild5)) . The pivot table now takes into account the new, extended range.

Did you like it? Then share it or sign up!

You can share this article with your friends or sign up for our newsletter for further information.

Leave a comment

Connect with Facebook

*

Kein Banner zum Anzeigen