Get the complete Excel tutorial here
Creating 3D Formulas
It is helpful to be able to create a single formula that calculates data gathered from multiple worksheets. These types of formulas are called 3D formulas. They calculate information from multiple worksheets and show the result in a selected formula cell.
When you use 3D formulas, you must also expand your knowledge of formula syntax. You will be using additional punctuation marks when you write these formulas in order to tell Excel specifically which cells from which worksheet you will be using. The additional punctuation marks you will need to know are:
1. !- used to separate the sheet name from the cell reference.
2. $- used to denote an absolute value.
3. :- used to separate sheet names in ranged 3D formulas.
4. ,- used to separate individual sheet/cell references from each other.
3D Formula Syntax
The best way to learn how a 3D formula works is to look at some examples. Here are three examples of 3D formulas. The first is a simple 3D formula, the latter two are ranged 3D formulas:
1. =Sheet1!B2+Sheet2!B2+Sheet3!B2
2. =SUM(Sheet1:Sheet3!B2)
Or
3. =SUM(Sheet1!B2,Sheet2!B2,Sheet3!B2)
All three of these formulas will display the same answer. You can use any syntax that you like. The change in the syntax from what you previously learned is that now you are inserting the name of the particular worksheet in the workbook, followed by an exclamation point, and then the cell reference.
Creating 3D Range References
In example #2 above, you can see a 3D cell range reference used to create a formula. A 3D cell range is one cell range that spans several sheets deep. In example #2, the range is cell B2, but it is cell B2 in all of the worksheets from “Sheet1” through “Sheet3.”
When referencing 3D ranges in formulas, you use the colon (:) to separate the names of the first and last sheets in the range, followed by a single cell range reference. The cell reference given is then used as the selected cell range through all of the worksheets listed. So in the above example, you are adding cell B2 from Sheet1, Sheet2 and Sheet3.
In 3D ranges, the given cell range cannot change from one sheet to another. Otherwise it is just another 3D formula, and you should use one of the other two alternate syntaxes available.
