All the changes made to the data are not uploaded in the pivot table unless you refresh. Here we will learn about multiple ways, wherein you can refresh the pivot table.
The most simple and common way to refresh a pivot table is by just pressing on the refresh button. 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.
- 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.
- Now, click on “Refresh”.
- The total sales in the product “Buiscuits is updated”.
- This is the simplest way to refresh the Pivot Table.
Another way of doing this is by just moving 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
In our reports, sometimes we use the formulas like NOW and TODAY which keep on updating themselves. What if we have a pivot table and analysis linked to such formulas?
We need to update the pivot table every time we open or use the pivot table. This seems more tedious. Here’s a solution. You can refresh a pivot table automatically when it opens.
- First of all, click anywhere on the Pivot Table and go to “Analyze” tab in the ribbon.
- Now click on the small drop-down button below Pivot Table.
- Then, click on Options.
- Here a window named “Pivot Table Options” will open. Here look for the Data option.
- 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
For refreshing the Pivot Table on a timer, we need to make it an OLAP (Online Analytical Processing) pivot. Sounds complicated? If yes, don’t worry. We are not going into its details. Just add the pivot to the data model to create an OLAP pivot table.
- First of all, create a Pivot Table by Insert → Pivot.
- Now, tick-mark the “Add this data to the Data Model” to make it an OLAP Pivot.
- From here, click on the Data tab in the ribbon and look for the “Queries & Connections”.
- As soon as you click on the “Queries & Connections” button a menu will open on the right side of the worksheet.
- Here, click on the connections and right-click on the connection worksheet to hit properties.
- 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. In case you change the data, you just need to click one button to refresh all your pivot tables.
Here we go!!
- To refresh all the Pivot Tables, Go to the Data tab in the ribbon.
- Under Queries & Connections, click on the small drop-down button of Refresh All.
- Now click on the Refresh All. This will refresh all the Pivot Tables.
Refresh a Pivot Table Without Changing Formatting
Until now you have been noticing that the formatting of the pivot table changes after every refresh. If you are formatting the pivot table after each refresh, then this post will ease your stress and extra work. To prevent formatting after refreshing the pivot table, follow the below steps.
- Assuming that you already have a pivot table created in your worksheet, just move your cursor anywhere inside the pivot.
- Now, right-click and hit the “Pivot Table Options”.
- Now under the Layout & Format tab, tick mark the last option i.e “Preserve cell formatting on update”.
- 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