Add Same Cells in Multiple Worksheets at One Go

Have you faced with the situation wherein you have data in multiple worksheets and you want to add the same cells in multiple worksheets at one go in Excel? This trick would help you to add the same cells in multiple worksheets in Excel.

Let us take one example to understand this better.

Suppose you have an Excel workbook which contains for product wise sales data in different regions in different worksheets as shown in the screenshot below:

North Zone Sales Figures (in Worksheet Name “North Zone”) :

East Zone Sales Figures (in Worksheet Name “East Zone”) :

West Zone Sales Figures (in Worksheet Name “West Zone”) :

South Zone Sales Figure (in Worksheet Name “South Zone”) :

Now you want to add the sum of the product sales just below the sales figure (in cell B7) in each of the worksheets.

Add Same Cells in Multiple Worksheets

One way is to manually insert the =SUM() formula in each of the worksheets one by one.

But this would be quite a time-consuming task and will be irritating in the case where there are hundreds of worksheets.

Today we would learn how to do this activity quickly in Excel.

This is a two-step procedure. In the first step, we need to select all the required worksheets (for each Zone) at one go in order to group them together and then we would insert the =SUM() formula.

Let us now start to unlock this trick.

Grouping the Multiple Worksheets

In order to group multiple worksheets at one click, follow the below shortcut instruction.

Press and hold the Ctrl key. Then click on the worksheet tabs to be grouped.

In the present case, as we want to group the first four worksheets, press the “Ctrl” key on your keyboard and then click on the individual worksheets (North Zone, East Zone, West Zone, and South Zone).

The grouped worksheets would look like this.

Insert =SUM() formula to Add Same Cells in Worksheets

As we have grouped the required worksheets, therefore, any change done in a cell of one worksheet would also change that particular cell in the other grouped worksheets.

It means that anything that you would do with cell B7 in a worksheet would also get replicated in the cell B7 of the other worksheets in the group.

Taking this into consideration, select the cell B7 and enter the formula =SUM(B2:B6)

As soon as you press enter, go to the worksheet “East Zone”. You would notice that the excel automatically added the sum formula in the cell B7 of worksheet “East Zone”.

Similarly, you can check the other grouped worksheets (West Zone and South Zone). Excel has automatically added the sum formula in each of the worksheets and calculated the result of respective product sales.

Now, make sure that you ungroup the worksheets, otherwise, any changes made in one of the ungrouped worksheets would automatically get replicated in other worksheets of the group.

Ungroup the Worksheets 

To ungroup the worksheets in Excel, just click on any other worksheet tab (which is not grouped) or you can also insert a new worksheet tab.

This brings us to the end of the blog.