Making Same Change to Multiple Worksheets (9 Examples)

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.


Download Practice Workbook

You can download the workbook used for the demonstration of the article from the download link below.


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.

9 Examples for Making Same Changes to Multiple Worksheets in Excel

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.

making the same change to multiple worksheets dataset 1

The second one is as follows.

making the same change to multiple worksheets dataset 2

And this is the one in the final sheet.

making the same change to multiple worksheets dataset 3


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.

selecting all sheet for making the same change to multiple worksheets

  • 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.

entering value in cell for making the same change to multiple worksheets


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.

selecting all sheet for making the same change to multiple worksheets

  • 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.

modifying first sheet making the same change to multiple worksheets

Now if you go to the second sheet, the content of cell B10 will change to the same too.

second sheet modified for making the same change to multiple worksheets

And the same thing will happen for the third sheet.

third sheet modified for making the same change to multiple worksheets

Read More: How to Copy a Formula in Excel with Changing Cell References


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.

inserting new row for making the same change to multiple worksheets

Excel will automatically insert a new row before the selected row. This is the first spreadsheet.

This will be the second one.

new row inserted in second sheet

And the third one will be the same too.

Read More: How to Copy a Formula across Multiple Rows in Excel (5 Ways)


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.

all sheets selected for making the same change to multiple worksheets

  • 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.

column inserted in the first sheet

This will be the second one.

And the third one will be the same too.

column inserted in the second sheet

Read More: How to Copy a Formula Down the Column in Excel(7 Methods)


Similar Readings


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.

sheets selected for making the same change to multiple worksheets

  • 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

formula in the first sheet

  • 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.

formula in the third sheet automatically inserted

Read More: How to Copy Formula to Another Sheet in Excel (4 Ways)


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.

all sheets selected making the same change to multiple worksheets

  • 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.

value deleted from the second sheet

And this will be the third one.

Read More: How to Copy Excel Sheet with Formulas to Another Workbook (5 Ways)


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.

required sheets selected for making the same change to multiple worksheets

  • 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.

first sheet filled with color

Meanwhile, this is the second sheet now.

second sheet color filled with color

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.

selected required worksheets for making the same change to multiple worksheets

  • 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.

copied and pasted in the first sheet

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.

Read More: How to Copy Exact Formula in Excel (13 Methods)


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.

formulas to be replicated for making the same change to multiple worksheets

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.

all required sheets selected for making the same change to multiple worksheets

  • 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.

making the same change to multiple worksheets

Read More: Copy Formula in Excel by Changing Only One Cell Reference


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.

For more guides like this, visit Exceldemy.com.


Related Articles

Abrar Niloy

Abrar Niloy

Hi! my name is Abrar-ur-Rahman Niloy. I have completed B.Sc. in Naval Architecture and Marine Engineering. I have found my calling, if you like, in Data Science and Machine Learning and in pursuing so, I have realized the importance of Data Analysis. And Excel is one excel-lent tool do so. I am always trying to learn everyday, and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo