How to make Multiple Sheet Changes in Excel so that a change made on one sheet happens on all sheets
- For updated video clips in structured Excel courses with practical example files, have a look at our MS Excel online training courses . You can even try the Free MS Excel tips and tricks course.
- To see if this video matches your skill level (see the suggested skill score below) do our free MS Excel skills assessment.
- If you are based in South Africa look at the live courses we offer in Johannesburg and Cape Town.
- - 2:42
- - Skill Level: 4
- - What's my skill level
Multiple Sheet Changes in Excel
In this segment we will show you how to make multiple sheet changes in excel.
Related Items: External Links, Find Replace Tool, Errors with External Links
In this example we have four sheets which represent different cities of the world. Each one has got a Salesman summary report, so this is the London report with the individual Sales people, the New York report and you’ll see that this is all exactly the same on each sheet. Paris and Johannesburg.
What happens if you’d like to change this, lets say you’ve got the company name wrong, how can you change it so that it happens to all of these sheets at the same time
If you are comfortable that the sections are the same, let’s say London you can see the first four rows look very similar to New York’s first four rows, what you can do is
- click on the one tab
- and then either hold down control and click on each tab that you want to change
- or alternatively click on the one tab,go to the one which encompasses all the tabs that you want
- hold shift down
- and click on it,
- and you’ll see that they turn slightly whiter,
Now what you can now do, whatever gets done on the one sheet gets done on all the sheets. So for example
- if we go in here
- and get rid of the name
- and make it let’s say YYY,
- and we push enter
- you’ll notice that if you go to the New York sheet the same has happened,
- Paris sheet has got the exact same,
- and the Johannesburg sheet has got the exact same.
While the tabs are highlighted white, anything you do here will happen to all four sheets. So let’s say we want to be more politically correct,
- we click here
- and change that to Sale person,
- click
- and again you’ll see that the changes are made everywhere,
Although this is a great feature it does have a risk. It’s very easy to work on this and then forget that you’ve highlighted all these tabs. So let us say that we decide that Johannesburg is not that well known
- and we click in here
- and we forget that we are working on other sheets and we type in South Africa
- and enter,
- When you go to the other sheets you’ll see that they all now change.
Because what you do to one you do to all. So it’s very important to realize that when you are finished making the changes that belong to all the sheets
- click out of them by clicking on another sheet