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

When working with excel, at times we require to use the same data/ formula in multiple sheets in Excel. Instead of doing it the boring way, i.e. manually going to each sheet and making the changes, Is there a simple way to use the data/ formula in multiple sheets. In this post, let us see how to work on multiple sheets at once.

Problem

I have 6 sheets in an excel work book pertaining to 6 months (Jan-Jun) in a year showing performance data of sales persons for different products and different regions. The header and the column name remains same in each sheet. Now I need the same header and 1^st column to reflect in all the 6 sheets.

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

How to work on multiple sheets at once

Step 1: Group the sheets whcih you want to work on. You can do this 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 here)

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

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 reflects across other sheets if they are not ungrouped)

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 note is that all sheets that are grouped should have the data in the same pattern or size. Else the new data entered will replace the existing data.

Download the sample file

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

Suggested Read : In earlier posts we have seen few tips while working with multiple sheets/ workbooks. Refer to the below posts in case if you missed out on these.

• Jump or Go to a Specific sheet directly in a workbook
• Opening the same instance of Excel file in multiple windows.
• Closing multiple files (from Excel 2013).

Hope you find this Excel Tip useful

Don’t forget to share this post with your friends.