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.
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.
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.
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)
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 gets replicated 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 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.