Excel Tip: Making the same change to multiple worksheets

Sometimes we build an Excel spreadsheet that has a number of worksheets (or tabs) that are the same layout.

No matter how well we have designed our spreadsheet, there are times when we need to make updates. This can be further improvements or changes to reflect changing requirements.

This can be a time-taking task if the changes are to be made to multiple worksheets. Fortunately, Excel has a way to do this.

First of all, I think it is worth mentioning that some of the reasons we have multiple worksheets are due to bad spreadsheet design. For example, having a sheet for each month is not usually the best way to handle most applications. In most situations it is far better to have a sheet that contains all of the data and a report sheet that allows you to report that data for any month. For my advice on laying out data, take a look at this earlier post.

Assuming that you have decided that you do need multiple sheets (or that you already have them and are not going to spend your time changing a spreadsheet that works!), then you can make Excel apply the changes to each of the sheets that you want it to.

First of all, click on the tab name of the first of the sheets that you want to edit (as you normally would to make that the active sheet). Then, hold down the Ctrl key while you select the other sheets you want to edit too.

You should now see that the backgrounds of the tab names of the selected sheets are all white.

While these multiple sheets are selected in this way, any change you make to one of the sheets will be applied to them all.

The really important thing to remember though, is what you need to do when you've made the changes:

Right-click on the tab name of one of the selected sheets and click "Ungroup Sheets"

If you don't do this, then you can imagine the chaos you can cause when you type anything into one of the sheets and it changes them all!

This can be really useful, as long as you remember that last step!



If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".