A 3D reference in Excel allows you to refer to the same cell or a range of cells across multiple worksheets. In other words, it refers to a range of worksheet names as well as a range of cells. The important thing to remember is that all of the referred sheets must have the same pattern and data type.
create a 3-D reference
To make a formula with a 3D reference, perform the following steps:
- Click the cell where you want to enter your 3D formula.
- Type the equal sign (=), enter the function’s name, and type an opening parenthesis, e.g. =SUM(
- Click the tab of the first worksheet that you want to include in a 3D reference.
- While holding the Shift key, click the tab of the last worksheet to be included in your 3D reference.
- Select the cell or range of cells that you want to calculate.
- Type the rest of the formula as usual.
- Press the Enter key to complete your Excel 3-D formula.
Excel functions supporting 3-D references
Here is a list of Excel functions that supporting 3-D references:
SUM – adds up numerical values.
AVERAGE – calculates arithmetic mean of numbers.
AVERAGEA – calculates arithmetic mean of values, including numbers, text and logical.
COUNT – Counts cells with numbers.
COUNTA – Counts non-empty cells.
MAX – Returns the largest value.
MAXA – Returns the largest value, including text and logicals.
MIN – Finds the smallest value.
MINA – Finds the smallest value, including text and logicals.
PRODUCT – Multiplies numbers.
STDEV, STDEVA, STDEVP, STDEVPA – Calculate a sample deviation of a specified set of values.
VAR, VARA, VARP, VARPA – Returns a sample variance of a specified set of values.