Quick, clean, and to the point

How to refresh data in a pivot table

Pivot tables do not automatically update when the source data is changed. To update a pivot table, you need to “refresh” it. When you refresh a pivot table, you are simply asking Excel to update the Pivot Table based on the latest source data.

Let's take a look.

The easiest way to update a pivot table manually is to right-click anywhere in the table and choose "Refresh" from the menu. This causes Excel to reload the source data and display the latest information in the pivot table.

If the source data has not changed since the last refresh, the pivot table will not change.

When you Refresh a pivot table, it's important to understand that the only thing changing is data running through the pivot table. However, because the pivot table is itself constructed from the source data, the table itself might also change.

To illustrate how this works, let's change some source data, and then refresh the pivot table.

The first row shows a sale to a customer in Minneapolis in 2011. Let's increatse the total sale value by $5000. When we refresh the pivot table, notice that the Minneapolis number shown in 2011 increases by $5,000 , along with the subtotal for the Midwest, and the Grand Total for all regions.

Let's change sales back to $87, and refresh again. The Pivot Table recalculates the original values.

Now let's change the City name from Minneapolis to St. Paul. When we refresh the pivot table, we see a new row for St. Paul, and the pivot table expands as needed to accommodate. Since we only changed one row for a sale in 2011, there are no sales numbers for St. Paul in 2012 or 2013.

If we change St. Paul back to Minneapolis and refresh, the pivot table will be rebuilt without an entry for St. Paul.

As you can see, refreshing a pivot table not only updates values, it also updates the structure of the table as needed to match the source data.

Dave Bruns

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.