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.