How to Refresh a Pivot Table in Excel

All the changes made to the data are not uploaded to the pivot table unless you refresh it. Here, we will learn about multiple ways to refresh the pivot table.

Refresh a Pivot Table in Excel (Manually)

Pressing the refresh button is the most simple and common way to refresh a pivot table.

Let’s understand with a simple example.

Here, we have the sales data for 9 months and a simple Pivot Table with the product and its sales quantity.

Now, let’s make a few changes to the data. Since our pivot table has product and sales, we will change the sales number of a product.

Let’s change the sales of “Biscuits” in April, Row 4, from top to 1230 instead of 123 and refresh the Pivot Table.

1. To start with, place your cursor on the Pivot Table and right-click. Here you will get a few options as in the below screenshot.
   
2. Now, click on “Refresh”.
   
3. The total sales in the product “Buiscuits is updated”.
4. This is the simplest way to refresh the Pivot Table.

Another way to do this is to move the cursor anywhere on the pivot table to activate the “Analyze”  tab in the ribbon.

Here, look for the Refresh button.

Now, click on the small drop-down arrow and click on Refresh.

Note: For multiple pivot tables, click on Refresh All. (see this: refresh all pivot tables at once)

Automatically Refresh the Pivot Table When the File Opens

Our reports sometimes use formulas like NOW and TODAY, which keep updating themselves.

What if we have a pivot table and analysis linked to such formulas?

We must update the pivot table every time we open or use it.

This seems more tedious. Here’s a solution. You can refresh a pivot table automatically when it opens.

1. First of all, click anywhere on the Pivot Table and go to “Analyze” tab in the ribbon.
   
2. Now click on the small drop-down button below Pivot Table.
   
3. Then, click on Options.
   
4. Here a window named “Pivot Table Options” will open. Here look for the Data option.
   
5. After this tick mark the box in front of “Refresh Data when opening the file” and click OK.
   

Note: You can also right-click the pivot table and click on pivot table options to get the dialogue box.

Automatically On a Timer

We need to make the pivot table an OLAP (Online Analytical Processing) pivot to refresh it on a timer.

Sounds complicated? If yes, don’t worry. We are not going into its details. Add the pivot to the data model to create an OLAP table.

1. First of all, create a Pivot Table by Insert → Pivot.
2. Now, tick-mark the “Add this data to the Data Model” to make it an OLAP Pivot.
   
3. From here, click on the Data tab in the ribbon and look for the “Queries & Connections”.
   
4. As soon as you click on the “Queries & Connections” button a menu will open on the right side of the worksheet.
5. Here, click on the connections and right-click on the connection worksheet to hit properties.
   
6. Now in the “Connection Properties” window, tick mark the “Refresh Every” option under the “Usage” category and select the period you want to refresh the Pivot Table.
   

Hooray!! We are done. Now, the pivotable is set to refresh automatically.

Refresh Multiple Pivots

Refreshing multiple pivots is as easy as refreshing a single pivot table. This works magic when you have multiple pivots connected with one source data.

If you change the data, you must click one button to refresh all your pivot tables.

Here we go!!

1. To refresh all the Pivot Tables, Go to the Data tab in the ribbon.
2. Under Queries & Connections, click on the small drop-down button of Refresh All.
   
3. Now click on the Refresh All. This will refresh all the Pivot Tables.
   

Refresh a Pivot Table Without Changing Formatting

You have noticed that the formatting of the pivot table changes after every refresh.

If you format the pivot table after each refresh, this post will ease your stress and extra work.

To prevent formatting after refreshing the pivot table, follow the below steps.

1. Assuming that you already have a pivot table created in your worksheet, just move your cursor anywhere inside the pivot.
2. Now, right-click and hit the “Pivot Table Options”.
   
3. Now under the Layout & Format tab, tick mark the last option i.e “Preserve cell formatting on update”.
   
4. In the end, click OK.

Now, you will notice that the formatting of the pivot table does not change after refreshing.

More on Pivot Table

Sort a Pivot Table | Refresh a Pivot Table | Pivot Table Keyboard Shortcuts |  Delete a Pivot Table | Pivot Table Formatting |Move a Pivot Table | Filter a Pivot Table | Count Unique Values in a Pivot Table | Change Pivot Table Data Source| Add or Remove Grand Total in a Pivot Table | Add Ranks in Pivot Table | Insert Calculation in Pivot Table  | Automatically Update a Pivot Table | Running Total in a Pivot Table | Conditional Formatting to a Pivot Table | Pivot Table from Multiple Worksheets | Group Dates in a Pivot Table | Connect a Slicer with Multiple Pivot Tables | Pivot Table Timeline