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.


Year-end tax planning and what’s new for 2016

Practitioners need to consider several tax planning opportunities to review with their clients before the end of the year. This report offers strategies for individuals and businesses, as well as recent federal tax law changes affecting this year’s tax returns.


News quiz: Retirement planning, tax practice, and fraud risk

Recent reports focused on a survey that gauges the worries about retirement among CPA financial planners’ clients, a suit that affects tax practitioners, and a guide that offers advice on fraud risk. See how much you know with this short quiz.


Bolster your data defenses

As you weather the dog days of summer, it’s a good time to make sure your cybersecurity structure can stand up to the heat of external and internal threats. Here are six steps to help shore up your systems.