Generate Duplicate Changes in Multiple Excel Worksheets

BY STANLEY ZAROWIN

I usually set up my annual budget in one Excel workbook, and then I add 12 more sheets—one for each month of the year—so I can easily track the monthly data. Each sheet contains the same rows, columns and formulas. Only the headings on each worksheet are different, along with the raw data for each month, of course. I use this setup because I was told it would make it easy for me to keep all the worksheets in sync—that is, if I make changes in the annual budget sheet, say, that change will be duplicated in the corresponding cells in all the monthly sheets—all in one swoop. Sounds great, except I can’t figure out how to do it. Can you help?

It’s a very simple process. In this example, let’s say you want to change the 2009 Budget for Plant C (see screenshot below) from $44,000,000 to $56,700,000, and you want that change to be reflected in all 12 monthly sheets. Select the tab for the first worksheet in the series; in this case it’s Annual (see screenshot below). Hold down the Shift key as you click on the tab of the last sheet in the series in which you want the change reflected (that would be May). Notice how all the tabs change color—indicating that you’ve “linked” them.

 

 

Notice, too, that Excel also added the word [Group] to the title bar to alert you that you have a group of worksheets selected; as you’ll see, that alert is important.

 

Now make your changes to the worksheet that appears on the screen; they will now be simultaneously copied to the corresponding cells in every other linked sheet as well.

 

 

 

 

Important: When done, be sure to click on a tab of any worksheet other than the first in the range. That deselects the link with all the sheets. If you fail to do that, any subsequent changes you make will continue to be copied in all the other sheets, and that could lead to quite a mess.

SPONSORED REPORT

Keeping client information safe in an age of scams and security threats

A look at the Dirty Dozen tax scams and ways to protect taxpayer information.

TECHNOLOGY Q&A

How to create maps in Excel 2016

Microsoft Excel 2016 has two new mapping capabilities. J. Carlton Collins, CPA, demonstrates how to make masterful 2D and 3D maps in Excel 2016.

QUIZ

News quiz: IRS enforcement, a hot job, and audit value

The IRS’s 2016 Data Book, a “hot job” of particular interest at this time of year, and insight into how executive and audit committees view the insights from financial statement audits received attention recently. See how much you know with this short quiz.