Here is the list of formulas you can use with 3-D references: FunctionĬalculates average (arithmetic mean) of numbers.Ĭalculates average (arithmetic mean) of numbers includes text and logicals.įinds largest value in a set of values includes text and logicals.įinds smallest value in a set of values includes text and logicals.Ĭalculates standard deviation based on a sample.Ĭalculates standard deviation based on a sample includes text and logicals.Ĭalculates standard deviation of an entire population.Ĭalculates standard deviation of an entire population includes text and logicals.Įstimates variance based on a sample includes text and logicals.Ĭalculates variance for an entire population.Ĭalculates variance for an entire population includes text and logicals. This formula basically does the same thing as the two formulas below, but it’s far easier to write and read.

Below is a formula that can sum values from identical monthly sheets: Now, let’s see how a 3D reference can be used in a formula. Jan:Dec!C2:E9 reference points to all C2:E9 ranges in all sheets from 'Jan' to 'Dec'.

Working with ranges follows a similar approach, using the colon character. The range of sheets is the actual sheets that you can see on the sheets bar. Sheets named 'Jan' and 'Dec' act as the start and end points for the range of sheets. Below, Jan:Dec!C2 denotes all cells that correspond to C2 across sheets 'Jan' and 'Dec'. Let’s see how this works with an example. The colon (:) character used between cells to create ranges, can also be used between sheets. A good practice when utilizing this feature is sticking with a similar structure across all worksheets. Data from all sheets can be used with ease with 3D references. For example, annual reports can be created easily by grouping data into months, each kept on a separate worksheet. 3D references are great for consolidating data from similarly built sheets.

The third dimension, being across sheets, can be seen like a z-axis. 3D references are special use of Excel ranges that refer to the same cell or range through multiple sheets.

3d relative cell reference excel