Add Cells Across Multiple Spreadsheets in Excel

Excel cells can communicate with each other in the same sheet, through various sheets, and across multiple workbooks. This can be very useful if we want to encompass the data found in different locations.

In the example below, we will show how to sum numbers from different sheets, and how to add cells across multiple spreadsheets.

Sum Across Multiple Sheets

For our example, we will create five sheets– four with the data with sales figures for different regions (North, East, West, and South) for 12 months (January till December), and one for Total:

In sheet Total, our goal is to SUM the numbers from every region, and we can do that by going to the last sheet and choosing the appropriate cell in which we want to do the calculation: cell A3 in our case. We will insert an equal sign and then go to every sheet and add cell A2 from each of them. Our formula will look like this:

We will now drag the formula from cell A3 to cell L3, and will get the sum of sales from all regions for every month:

There is also an easier way, to sum up, these figures. It is easy when we have just four sheets, but if we would have more of them, it would be pretty painful to manipulate them all. For easier calculation, we need to position ourselves to desired cell (again, to cell A3 in sheet Total) and do the following:

1. Insert “=SUM” then click on the first sheet (“North”).
2. Select and hold SHIFT and then choose the last sheet we want to include in the calculation (sheet “South”).
3. Choose the desired cell we want to sum from every sheet- cell A2.

We will end up with this formula:

And we will do the same thing as before (drag it to the end of our list), and will end up with the same results:

Add Cells Across Spreadsheets

We have shown how to add numbers from multiple sheets to one sheet, but what if we want to do the opposite- add a certain cell (empty cell, number, text, or something else) to multiple sheets?

For these purposes, we can also use the shortcut that we showed above. We first need to group the sheets to which we want to apply the changes or add a certain cell. We will do that by clicking on the first sheet we want to manipulate, and then pressing and holding the CTRL key and adding other sheets.

We can also use SHIFT instead of CTRL, especially if the sheets are adjacent one to another. Grouped sheets will be highlighted:

And we will have an indication of the grouping in the title as well:

Every action that is done to one sheet in the group, will now be reflected on every sheet in the group. For our change, we will go to the first row, select it, right-click on it and choose Insert:

Then we will write the following title of our table: “Sales result for 2021”, and use Merge & Center for formatting. This title will now be applied to every sheet that was in our group (“North”, “East”, “West”, and “South”).