Look Who it Was Random video

Excel - Mastering Pivot Tables: Update Multiple Pivot Tables with One Change - Episode 961

2,017 views · Published 2 March 2009 · 4:48 · Indexed 1 November 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial:  Mastering Pivot Tables: Update Multiple Pivot Tables with One Change.

Welcome back to the MrExcel netcast! In today's episode, we're going to tackle a common problem faced by many data analysts - how to analyze massive amounts of data efficiently. We'll be using pivot tables to solve this problem, so let's dive right in!

This question was sent in by Jason from Kentucky, and I have to give credit to my co-author on the pivot table data crunching books, Mike Alexander, for this answer. In fact, Mike and I will be hosting a three-day data analyst boot camp in Chicago on April 28th, 29th, and 30th of this year, so if you're interested in learning more about Excel and Access, be sure to check it out!

Jason's question is about having multiple worksheets with pivot tables, all based on the same data set. He wants to be able to change the date on one sheet and have it automatically update the page fields on the other sheets. To solve this, we'll be recording a tiny macro and then editing it to fit our needs.

To start, we'll go to View Macros and record a macro called "fix pivot table". We'll then go to each sheet and select the desired date from the list in the page area of the pivot table. This will also give us the pivot table name for each sheet, which we'll need later on. Once we've recorded all the changes, we'll stop the macro and go to Visual Basic to edit it.

In the macro, we'll see a line of code using the "current page" property. We'll modify this line to refer to the value we selected on the first sheet, instead of hard-coding it to be a specific date. We'll then copy and paste this line for the other sheets, making sure to change the sheet and pivot table names accordingly. We'll also remove any unnecessary lines of code recorded by the macro.

But we don't want to have to run the macro every time we make a change, so we'll copy the lines of code and paste them into the first sheet's code, under the "pivot table update" section. This will ensure that the other pivot tables are updated every time we make a change to the first one.

And there you have it! Thanks to Mike Alexander's cool tip, we can now easily update multiple pivot tables with just one change. This is a very common problem faced by data analysts, so I hope this solution helps you in your work. Thanks for watching, and be sure to tune in next time for another netcast from MrExcel!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Jason has five pivot tables based on the same data. All five have a page field holding the month. After changing the month on the first pivot table, he would like the other four pivot tables to also change. Episode 961 shows Mike Alexander's trick for solving this problem.

This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Table of Contents:
(00:00) Introduction
(00:14) Question from Jason
(00:33) Recording a Macro
(01:01) Editing the Macro
(01:43) Modifying the Macro
(02:43) Copying and Pasting the Code
(03:12) Updating the Pivot Tables
(03:49) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Changing page fields in pivot table
Chicago data analyst boot camp
Excel and Access seminar
Excel and Access training in April
Modifying macros in Excel VBA
Pivot table data analysis
Pivot table macro
Recording macros in Excel
Tips and tricks for Excel pivot tables
Updating multiple pivot tables with one change

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152252/

More from this channel