Pivot Table Update

Update Pivot Table in Excel

Pivot Tables are great for analyzing large amounts of data, and it will help us in a great time in telling the story behind the data. However, there is a limitation with a pivot table, and that limitation is if there are any changes in the existing data or if there is any addition or deletion of the data, then the pivot table cannot show the reports instantly, so it requires user’s intervention to show updated results. Updating the pivot table to show results are possible by refreshing the pivot tableRefreshing The Pivot TableTo refresh pivot tables, you may use the following methods - refresh pivot table by changing data source, refresh pivot table using right click option, auto-refresh pivot table using VBA Code, refresh pivot table when you open the workbook.read more, but this refresh is a manual process; however, with a shortcut key also we can update the pivot table, and in this article, we will show you how to update the pivot tables.

You can download this Pivot Table Update Excel Template here – Pivot Table Update Excel Template

How to Update Pivot Table?

Steps to update pivot table are as follows –

  1. For example, look at the below data in an excel worksheet.

    Pivot-Table-Update-Example-1

  2. We will summarize this report by adding a pivot table, so let’s insert a pivot table first.

    Pivot-Table-Update-Example-1-2

  3. Our Pivot table looks like, as shown below:

    Pivot Table Update Example 1-1

  4. Ok, now we will go to the main data and will change one of the sales numbers.

    Pivot Table Update Example 1-3

  5. In cell C5 current value is 3801 and will change to 5000 now.

    Pivot Table Update Example 1-4

  6. If you come back to the excel pivot table and see the result for “Mechanical” remains the same as the old one is 29393.

    Pivot Table Update Example 1-5
    So this is the problem with a pivot table.

  7. To update the report to show updated results, right-click on any of the cells inside the pivot table and choose the option of “Refresh.”

    Pivot Table Update Example 1-6

  8. Upon refreshing the report, we can see updated results now.

    Pivot Table Update Example 1-7
    Now, look at the result of the pivot table; before refreshing, we had a value of 29393 for the product “Mechanical,” and after refreshing, it is showing as 30952.
    So like this, we can refresh the pivot table if there are any changes in the existing data set.

Update Pivot Table If Any Addition to the Existing Data Set

We have seen how we can refresh the pivot table if the data numbers of the existing data set change, but this is not the same if their addition to the existing data set.

For an example of the above existing data set, we will add five more lines of data.

Pivot Table Update Example 2

If you go and refresh the pivot table, it won’t take the additional data into consideration.

Pivot Table Update Example 2-1

This is because while creating the pivot table, it has taken the data range reference of A1:C20, and since we have added data after row number 20, it won’t take the data range automatically.

To view the data range reference in the pivot table sheet, select any one of the cells of the pivot table; it will open up two more tabs in the ribbon as “Analyze & Design,” respectively. Under the “Analyze” tab, click on the “Change Data Source” option.

Change Data Source

Now this will take us to the datasheet with the already selected data range.

Pivot Table Update Example 2-2

As we can see above, for the existing pivot table, it has taken the data range reference as A1:C20; since we have added the new data after the row number 20, even after refreshing the pivot table, it won’t show the newly updated result.

So we need to select the new data range in the above window, so select the new data range from A1:C25.

Pivot Table Update Example 2-3

Click on “Ok,” and our pivot table will show the updated results.

Pivot Table Update Example 2-4

As we can see above, after changing the data range, our pivot table showing the new results, so now anything happens to the range of cells from A1:C25 will be reflected upon refreshing.

Auto Data Range for Pivot Table with Excel Tables

As we have seen above, we needed to change the data range manually in case of additional data but using excel tables; we can make the data range automatically picked for a pivot table.

First, convert the data range to an excel table by pressing Ctrl + T.

Pivot Table Update Example 3

Click on ok, and our data range will be converted to “Excel Table.”

Excel Table Example 3-1

Now create a pivot table by selecting the excel table range.

new table Example 3-2

Now go to data and add a few lines of data after the last row of the existing data range.

New Data Added Example 3-3

Now just go back and refresh the pivot table to get the result.

Pivot Table Update Example 3-4

Like this, we can use different techniques to update the pivot table in excel.

Update Multiple Pivot Table Results using Shortcut Keys

Imagine 10 to 20 pivot tables. Can you update the pivot table by going to each and every pivot table and hit the refresh button???

It is highly time-consuming; instead, we can rely on a single shortcut key that can update all the pivot tables in the workbook.

Shortcut to Update Pivot table

This will refresh all the pivot tables in the workbook.

Things to Remember

Recommended Articles

This has been a guide to Pivot Table Update. Here we discuss how to update the pivot table to show updated results (Auto Update, Shortcut Key). You may learn more about excel from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>