Abrar-ur-Rahman Niloy
Sometimes it is necessary to make changes to multiple worksheets in a workbook at a time. For example, you may need to move or format particular cells in a certain way for many sheets. If you have a few sheets and a small number of actions to perform you can do that manually. But for a workbook with many sheets to change it is very daunting. Even for a few sheets, if the task is too many, the actions become repetitive and dull. Fortunately, there is a way to make the same change every time to multiple worksheets in Excel that makes the work more fun. This article will cover all of that with the procedure and examples.
How to Make Same Changes to Multiple Worksheets in Excel
The general idea to make the same changes to multiple worksheets in Excel is to select the sheets you want to change. Then make changes to a single worksheet while all of them are selected.
- First, you have to select the sheets you want to make changes to at the same time.
- You can do that by pressing Ctrl on your keyboard and then clicking the sheets’ names individually from the bottom-left corner of the workbook. This method is helpful if there are sheets not included in between selections.
- You can also select the first worksheet and while holding Shift, click on another sheet’s name. It will select all of the sheets in between. This method is helpful if you have to select all of the spreadsheets in a range.
- You can cancel the selection by clicking on a sheet name you did not select. Or selecting another sheet that is not active if you selected all of the sheets in the workbook.
- Once you have selected all the sheets you need, you just now have to make the changes to one spreadsheet. Excel will automatically make the same changes to all of the worksheets that you selected previously.
Making the Same Changes to Multiple Worksheets in Excel: 9 Examples
Although the general idea of making the same change to multiple worksheets in Excel is the same, you can not perform any task in this way. Unfortunately, up to the latest version of Excel, there are some limitations to the changes you can make while multiple worksheets are selected. We are using Microsoft Excel 365 for the demonstrations here. As for the things you can not do for multiple worksheets at a time, the ribbons will be grayed out for the tasks. Nevertheless, here are some examples that will clarify the process we described earlier.
For every demonstration, we are using the same dataset with different values for three different spreadsheets. This is the one in the first spreadsheet.
The second one is as follows.
And this is the one in the final sheet.
1. Entering Same Data into Multiple Worksheets
In the first example, let’s add a piece of data to multiple worksheets at a time. We will follow the same method described earlier. Follow these steps to see the detailed method.
Steps:
- First, select the sheets you want to make changes to. You can do that by either pressing Ctrl or Shift on your keyboard. Then clicking on the sheet names.
- Now go to the cell you want to enter a value. Here, we are selecting cell B10.
- Then enter the value in the cell.
That’s it. It is all you have to do to enter the same data in multiple worksheets. As you can see from the figure above that, this belonged to the first spreadsheet. But if you go to the other ones, you will notice Excel already made the same changes to them.
This is the second spreadsheet now.
And this is the third one.
2. Modifying Same Cells to Multiple Worksheets at Once
Similar to entering the same data into multiple worksheets, you can also modify the same cell at the same time for multiple worksheets. For that, follow these steps.
Steps:
- First, select the sheets you want to make changes to. You can do that by either pressing Ctrl or Shift on your keyboard. Then clicking on the sheet names.
- Then select the cell you want to modify. Similar to the previous example, we are selecting cell B10 for this.
- Now press F2 to go into the edit mode of the cell.
- After that, enter the value you want to put in instead of the previous one.
Now if you go to the second sheet, the content of cell B10 will change to the same too.
And the same thing will happen for the third sheet.
3. Entering Rows into Multiple Worksheets
For the next example of making the same change to multiple worksheets, we are showing entering rows into multiple worksheets at the same time. We will be following the same method. But we will enter a new row at the end instead.
Steps:
- First, select the sheets you want to make changes to. You can do that by either pressing Ctrl or Shift on your keyboard. Then clicking on the sheet names.
- Now right-click on the row header on the left of the spreadsheet before which you want to enter a new row.
- Then select Insert from the context menu.
Excel will automatically insert a new row before the selected row. This is the first spreadsheet.
This will be the second one.
And the third one will be the same too.
4. Entering Columns into Multiple Worksheets
Now we will be entering new columns into multiple worksheets at the same time. Follow these steps for more details.
Steps:
- First, select the sheets you want to make changes to. You can do that by either pressing Ctrl or Shift on your keyboard. Then clicking on the sheet names.
- Now right-click on the column header on the top of the spreadsheet before which you want to enter a new column.
- Then select Insert from the context menu.
Excel will automatically insert a new column before the selected column. This is the first spreadsheet.
This will be the second one.
And the third one will be the same too.
5. Using Formula to Multiple Worksheets
Now in our next example, let’s demonstrate using a formula for multiple worksheets. For that, you have to select the sheets and enter a formula in one of the cells. Excel will copy it over for multiple spreadsheets.
Steps:
- First, select the sheets you want to make changes to. You can do that by either pressing Ctrl or Shift on your keyboard. Then clicking on the sheet names.
- Now select the cell you want to insert the formula. Here, we are selecting cell C11 for the demonstration.
- After that, enter the formula. We are entering the following formula.
=C9+C10
- Now press Enter.
The figure above is the one on the first spreadsheet. The second one will be automatic as follows.
If you see the third spreadsheet, it will contain the same formula too.
6. Deleting Values from Multiple Worksheets
We can delete values from multiple worksheets at the same time too. For that, you need to select the sheets we want to delete from first. Then just delete the content of the cell using your preferred method.
- First, select the sheets you want to make changes to. You can do that by either pressing Ctrl or Shift on your keyboard. Then clicking on the sheet names.
- Then select the cell, you want to delete. Here, we are choosing cell B7 for the demonstration.
- Now press Delete.
This will delete the cell content from all of the spreadsheets. This is the first spreadsheet after the steps.
This is the second one now.
And this will be the third one.
7. Formatting Multiple Worksheets at Once
In this example, we will show you how we can format one sheet and make the same change to multiple worksheets at the same time. The procedure is almost the same.
Follow these steps for a detailed demonstration.
Steps:
- First, select the sheets you want to make changes to. You can do that by either pressing Ctrl or Shift on your keyboard. Then clicking on the sheet names.
- Now select the cell you want to modify. Here, we are picking cell B11 for the demonstration.
- Then add the modification. We are choosing to fill the cell with a color. For that, we have selected Fill Color feature from the Font group of the Home tab.
This will fill up the color of the spreadsheet. The first sheet will be like this.
Meanwhile, this is the second sheet now.
The third sheet will also change accordingly.
8. Copying Data from Multiple Worksheets
In our next example, we are going to demonstrate copying and pasting data to multiple worksheets at the same time.
Although the main idea is the same, follow these steps to see the details.
Steps:
- First, select the sheets you want to make changes to. You can do that by either pressing Ctrl or Shift on your keyboard. Then clicking on the sheet names.
- Then select the cells you want to copy and paste them.
- We are copying the range D10:F10 and pasting them in D11:F11.
That is all you need to do. This will be the first spreadsheet after that.
Now take a look at the other spreadsheets. This is the second one.
And this will be the third one.
As you can see we are making the same change to multiple worksheets in this method.
9. Autofill Cells to Multiple Worksheets
Finally, we are going to demonstrate using the autofill feature in multiple worksheets at the same time. In the final column of our spreadsheets, there was a formula involved.
We are going to replicate this formula using the autofill feature for cell G11. For that, we are going to follow these steps.
Steps:
- First of all, select the sheets you want to make changes to. You can do that by either pressing Ctrl or Shift on your keyboard. Then clicking on the sheet names.
- Then select cell G10 and click and drag the fill handle icon down to the next cell.
From the first sheet, we can see that the cell is filled up with the formula with relevant references.
If we look at the second worksheet now, we will notice that the same thing has already happened.
And the third sheet follows the same pattern too as we included it in the selection.
Download Practice Workbook
You can download the workbook used for the demonstration of the article from the download link below.
Conclusion
These were the method and examples of making the same change to multiple worksheets in Excel. Hopefully, you have grasped the concept and can apply the changes to your cases. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know in the comments below.
