WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Refresh Pivot Table in Excel

Refresh Pivot Table in Excel

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

How to Refresh Pivot Table in Excel?

Below are the top 4 methods to refresh the Pivot Table in Excel

  1. Refresh Pivot Table By Changing Data Source
  2. Refresh Pivot Table using Right Click Option
  3. Auto Refresh Pivot Table Using VBA Code
  4. Refresh Pivot Table When you open the Workbook

Now let us discuss each method in detail with an example –

#1 Changing the Data Source Refresh Pivot Table

  • Step 1: I have sales data from different countries.

Refresh Pivot Table in Excel step 1

  • Step 2: Let me apply the pivot table to this data.

Refresh Pivot Table in Excel step 1-1

  • Step 3: Now, I am going back to my pivot table data and adding a few more data to the table.

Refresh Pivot Table in Excel step 1-2

I have added five lines of the data. If I go and see the table, it is not showing the updated values.

Let me audit what the range of the pivot table data is.

  • Step 4: Select the pivot table and go to Options and Change Data Source.

Refresh Pivot Table in Excel step 1-3..

  • Step 5: Once you click on Change Data Source, this will take you to the datasheet along with the below box.

Refresh Pivot Table in Excel step 1-4

Look at the range it has. It has the data range from A1 to B23, whereas my data is there from A1 to B28.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course
  • Step 6: In the same dialogue box, change the source data from A1 to B23 to A1 to B28.

Refresh Pivot Table in Excel step 1-5

  • Step 7: Now click on the OK; it will add the new data to the pivot table and refresh it.

Refresh Pivot Table in Excel step 1-6..

Refresh Pivot Table in Excel step 1-6

#2 Right Click on the Pivot Table and Refresh Pivot Table Excel

I am taking the previous data as an example.

  • Step 1: Here, I am not adding any amount of data to the list; rather, I am just changing the existing numbers.

Refresh Pivot Table in Excel step 2

  • Step 2: Here on cell B10, I have to change the value from 677434 to 750000. If I go to the pivot table, it is still showing the old values for the country France.

Refresh Pivot Table in Excel step 2-1

  • Step 3: The new value for the country France is supposed to be 1638228, but it is showing the old value only. We need to do one simple thing here, Right-click on the Refresh.

Refresh Pivot Table in Excel step 2-2gif

  • Step 4: Click on the Refresh button to update the new values.

Refresh Pivot Table in Excel step 2-3

Okay, now our Pivot Table is showing updated values.

Now the question is if I have 10-pivot tables, can I update each one of them by going to each pivot table and update.

We have the shortcut to refresh all the pivot tables in excel in one single shot. Below is the shortcut key to refresh all the pivot tables once.

Refresh Pivot Table in Excel step 2-4

We can find this option under the Data tab.

Refresh Pivot Table in Excel step 2-5

#3 Using Auto Refresh Pivot Table Excel VBA Code

Ok, we know how to refresh the pivot table to new values. Whenever we change the values, we every time we need to update manually.

If the data changes frequently and updating each and every pivot table is not that easy. We are humans, and we tend to forget at times.

To come off that danger, we have a VBA code, which can update or refresh as soon as there are any changes.

Follow this section of the article to learn the VBA code that refreshes the pivot table in excel automatically.

  • Step 1: Go to the Developer tab and click on Visual Basic.

VBA Code step 1

  • Step 2: Click on Visual Basic. Go to This Workbook and double click on that.

VBA Code step 2

  • Step 3: From the dropdown list, select Workbook.

VBA Code step 3

  • Step 4: Once you have selected the workbook, it will automatically create one macro for you. Ignore that.

VBA Code step 4

  • Step 5: From the Right-hand side drop-down, select Sheet Change. It will insert one more macro for you.

VBA Code step 5

  • Step 6: Now copy and paste the below code the macro that has been created for the second time.

VBA Code step 6

What this code will do is if there is any change in the sheet, it will refresh the pivot table in excel automatically for you.
Note: Once you copy and paste the code, you have to save the workbook as Macro-Enabled Workbook.

#4 Refresh Pivot Table When you Open the Workbook

We can refresh the pivot table while opening the workbook.

Right-Click on any of the pivot table and select the Pivot Table in the Excel option.

Table 1

Click on this option.

Go to Data and check Refresh Data when opening the file.

Table 2

This will refresh the pivot table in excel whenever you open the excel file.

Things to Remember

  • You can choose any of the above methods to refresh your pivot tables.
  • In the case of VBA code, you need to save your workbook as the macro-enabled workbook.
  • The easiest for me to refresh is the shortcut ALT + A + R +A
  • You can rename your pivot table under pivot options.
  • In VBA code, you need to mention each of the pivot table names in the case of multiple pivot tables.

Recommended Articles

This has been a guide to Refresh Pivot Table in Excel. Here we discuss how to Refresh Pivot Table using the top 4 methods in excel along with practical examples and a downloadable template. You may learn more about excel from the following articles –

  • Pivot Table From Multiple Sheets in Excel
  • Pivot Table Slicer
  • Filter in a Pivot Table
  • Pivot Table Calculated Field
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Category iconExcel Tutorials,  Excel, VBA & Power BI

Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Refresh Pivot Table Excel Template

Special Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More