In Excel, it is easy to refer to cells on another worksheet. If the workbook consists of several worksheets, in for example cell A1 of worksheet4 an addition can be made of the values from the A1 cells over the previous worksheets with the formula:
='Worksheetname 1'!A1 + 'Worksheetname 2'!A1 + 'Worksheetname 3'!A1
But if the file consists of many worksheets, it quickly becomes unclear, with the danger that a worksheet will be forgotten. For example, the workbook below consists of 13 worksheets:
The intention is that additions are made in the total sheet over the previous worksheets. Suppose that in cell C3 an addition has to be made over the values of C3 of the 12 previous worksheets, it can be done easily with the following formula:
All cells C3 of the worksheets from 'Jan' to 'Dec' are added to this. This works much easier and faster and with less chance of errors than by naming all worksheets separately in the formula.
If the sheet names in the formula do not contain spaces, the single quotes are automatically removed by Excel and the above formula can also be entered without the single quotes, but if a one or both sheet names contain a space then the single quotes are required.
The cell reference 'beginning sheet:ending sheet'!C3 is also called a 3D cell reference. Not all Excel formulas can be used in a 3D cell reference, but only relatively simple formulas such as SUM, COUNT, COUNT, COUNTA, AVERAGE, MIN and MAX. This cell reference contains the name of a begin sheet and an end sheet. As a result, all intermediate worksheets are automatically included. This also applies to invisible intermediate worksheets. If an intermediate worksheet is moved to a position before the beginning sheet or after the ending sheet, it is out of scope for the formula. If a worksheet is dragged between the beginning sheet and ending sheet, it will automatically fall within the scope of the formula and be included.
Questions / suggestions
Hopefully this article helped you to create formulas across multiple worksheets. If you have any questions about this topic or suggestions for improvement, please post a comment below.