Doing the same action across Multiple Sheets at once – Excel Tip

When working with multiple sheets in an Excel workbook, we need the same data/ action/ formula needs to be reflected across multiple sheets in Excel.

Instead of doing it the boring way, i.e. going to each sheet manually and making the changes there is a simple way to do it. Let us see how to do it.

Problem:

I have 6 sheets in an excel work book pertaining to 6 months (Jan-Jun) of a year showing performance data of sales persons for different products and different regions.

Working with multiple sheets in Excel

The header and the column name remains same in each sheet. Now I need the same header (in the image below) and 1st column to reflect in all the 6 sheets.

Working with multiple sheets in excel

  • One way of doing it is the donkey work. Preparing one sheet and copying it to all the sheets manually.
  • The other way is the smart way. “Working on Multiple sheets at once.”

See the below steps and images to understand how to work across multiple sheets.

Step 1: Select all the sheets. This can be done by holding the Shift and clicking the sheets which you need. In case if you want select all sheets instead of few right click on any of the sheet names and choose Select all Sheets Option. (The selected sheets gets highlighted).

Alternatively, you can also use the page navigation shortcuts CTRL+PGUP or CTRL+PGDN along with Shift to toggle between the sheets. (Learn more Shortcuts)

Working with multiple sheets in excel

Step 2: Enter the data in the cells which you want to in the active sheet.

Working with multiple sheets in excel

Step 3: Now right click on any of the sheets and select “ungroup sheets” option. (Do not forget to ungroup as the action on one sheet gets replicated across other sheets if they are not ungrouped)

Working with multiple sheets in excel

Step 4: Click on any of the sheets and you can see the same data reflecting in all the selected sheets.

The same steps can be follow to enter a formula in multiple sheets.

The only thing to be noted is that all sheets that are grouped should have the data in the same pattern or size. Else the existing data will be replaced by the newly entered data.

Click to Download the sample file

Challenge: In the sample file above calculate the Total Sales by each sales person month wise using the same method

Hope you find this Excel Tip useful.

Please do share your opinion in the comments and Don’t forget to share this post with your friends.

One Reply to “Doing the same action across Multiple Sheets at once – Excel Tip”

Leave a Reply