Try this quick Excel formula to sum a cell in all the sheets except the current one

In Excel sometimes you’ll want to sum a cell value in all the sheets except the one that you’re currently working on. You might do this if you have a series of quarterly results that you want to add together to create a yearly total in the total sheet.

You can do this by typing the sheet name and cell for each cell to add but that would be very cumbersome. It will be a nuisance too if you try to select each cell in turn. Luckily Excel has a special feature that will help you do this. So, to sum all the values in, for example, cell C3 of every sheet in the current workbook except for the sheet you are currently working on, use this formula:

=SUM(‘*’!C3)

The ‘*’! part of the formula tells Excel that it should total all the sheets in the workbook except the current worksheet. When you press Enter Excel expands the formula so it will refer to the first and last sheets in the workbook. So, if you are summing that cell in a workbook that has sheets called YearTotal, Quarter1, Quarter2, Quarter3, and Quarter4 the formula will be expanded automatically so it reads:

=SUM(Quarter1:Quarter4!C3)

Be aware that if you add additional sheets to the workbook which appear inside the range of the sheets that are refered to in the formula then those additional worksheet cells will be included in the total.

If you don’t want this to be the case then make sure that any new sheets that you add appear outside the range of sheets that are included in the formula.

Helen Bradley