Advanced Spreadsheets – 3-D and Mixed Reference

Use a 3-D reference within a sum function.

Till now we have seen how to use the cell ranges from the same sheet as the arguments for the functions. But we can also use cell ranges from other sheets as arguments for the function. This is known as 3-D referencing.

To use 3-D referencing, use the same ‘Select’ icon which is always used and when the ‘Select’ icon is in use and in the minimized state, you can go to any of the open sheets and select the cell ranges from there. Then, upon maximizing the wizard, these ranges will be taken as the arguments for the function. This is how 3-D referencing works. This has also been shown in the illustration given below.

Use mixed references in formulas.

Using mixed references in formulas means to use the different kinds of references which we have learned till now in the same formula. A single formula can use all or some of the different kinds of references for getting the arguments and is not restricted to using only one particular kind. So if a function has, say, 4 arguments. The first can be entered manually, the second one can be a nested function, the third one can be a 3-D reference and the fourth one can be a cell range on the same sheet. So we can use as many types of references for the same formula as we want. This is what is meant by using mixed references for a formula.