Generate Duplicate Changes in Multiple Excel Worksheets

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.