If you want to add up the same cell across multiple tabs in Excel, without manually linking to the individual tabs, you can use this useful technique. First, you must include the tabs you wish to add up within two separating tabs. For example, in the above screenshot, you can see that we have added two blank tabs, named >> and <<, before and after the UK P&L and USA P&L tabs. There is nothing within these tabs – they are included purely for reference.
Once the separating tabs are made, you start with the SUM function by typing =SUM( and then click the first tab >>. Then hold shift and click the last tab <<. Once this is done, the formula bar should say =SUM(‘>>:<<‘! – at this point, you should just type in the cell you wish to add up across all tabs. The final formula in our example is =SUM(‘>>:<<‘!C) in this instance. This will add up cell C5 in both the UK P&L and USA P&L tabs.
When using this technique, you need to be sure that the various tabs between your separating cells are setup the same. For instance, if revenue in the USA P&L was on row 6 rather than row 5, this formula will add up the incorrect cells.
This is particularly useful where you have several identical sheets with the same structure. For example, you may have a company with several divisions each reporting their profit separately, where you require a group-level view aggregating each.
Alternative method for adding up cells in different tabs in Excel
The basic method would simply be to write =sum( and then click on the relevant tab, and cell within that tab. Once done, write a comma, and you will be ready to click on another tab and cell. After you are happy that you have included all cells you wish to sum, finish the formula by closing the formula. For example, our final formula would be =sum(‘UK P&L’!C5,’USA P&L’!C5). This will achieve the same output of 200.
Alternatively, you can of course write this formula manually without clicking onto the relevant tabs. The formula required will be the same i.e. =sum(‘UK P&L’!C5,’USA P&L’!C5)
This method works better where the cells are in different locations on separate worksheets.
