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

How to make the most of a negotiation

Negotiators are made, not born. In this sponsored report, we cover strategies and tactics to help you head into 2017 ready to take on business deals, salary discussions and more.

VIDEO

Will the Affordable Care Act be repealed?

The results of the 2016 presidential election are likely to have a big impact on federal tax policy in the coming years. Eddie Adkins, CPA, a partner in the Washington National Tax Office at Grant Thornton, discusses what parts of the ACA might survive the repeal of most of the law.

QUIZ

News quiz: Scam email plagues tax professionals—again

Even as the IRS reported on success in reducing tax return identity theft in the 2016 season, the Service also warned tax professionals about yet another email phishing scam. See how much you know about recent news with this short quiz.