What is a 3D reference in Excel? Definition, syntax, and examples

Microsoft's definition fits in a sentence: "A reference that refers to the same cell or range on multiple sheets is called a 3-D reference." The third dimension is the tab strip. An ordinary reference names a column and a row; a 3D reference adds a span of worksheets in front of it, which is why twelve monthly tabs collapse into one addend.

The syntax

The form is FirstSheet:LastSheet!cell or FirstSheet:LastSheet!range, wrapped in a function. Microsoft's worked example adds "budget allocations between three departments—Sales, HR, and Marketing—each on a different worksheet" with =SUM(Sales:Marketing!B3); the reference documentation uses =SUM(Sheet2:Sheet6!A2:A5) "to add cells A2 through A5 on worksheets 2 through 6".

The colon between the sheet names is the same range operator that sits between cell addresses. It means through, not and — the reference covers every tab positioned between the two endpoints, in tab order, whether or not you thought about them when you wrote it. Sheets are included by position on the tab strip, not by their names matching a pattern.

Building one by clicking is easier than typing it. Microsoft's sequence: type =, the function name and an opening parenthesis, "select the tab for the first worksheet that you want to reference", then "hold down SHIFT and select the tab for the last worksheet", select the range, and press Enter — the same five steps Ablebits describes. Excel writes the sheet range for you, quotation marks and all.

Which functions accept one

Not every function does, and this is the limitation that sends people looking for a workaround. Microsoft lists nineteen:

Category	Functions
Arithmetic	SUM, PRODUCT
Averages and counts	AVERAGE, AVERAGEA, COUNT, COUNTA
Extremes	MAX, MAXA, MIN, MINA
Spread	STDEV, STDEVA, STDEVP, STDEVPA
Variance	VAR, VARA, VARP, VARPA
Stacking	HSTACK, VSTACK

The pattern is that they aggregate values without caring where those values came from. Ablebits states the corresponding exclusion directly: functions such as COUNTIF, SUMIF and VLOOKUP do not support 3D references — anything that has to align a criteria range with a sum range, or search a lookup table row by row, needs a two-dimensional block to work on.

HSTACK and VSTACK are the interesting additions, because they turn a 3D reference into data rather than a single number. Exceljet's example is VSTACK(Sheet1:Sheet3!B5:E16), which "points to the range B5:E16 on Sheet1 through Sheet3 which, in this case, is Sheet1, Sheet2, and Sheet3" — three sheets stacked into one array you can then filter, sort or feed into a lookup. That is the route around the COUNTIF restriction: stack first, aggregate afterwards.

What happens when sheets move

A 3D reference is defined by tab positions, so rearranging the workbook rewrites what the formula covers. Microsoft documents five cases, all against =SUM(Sheet2:Sheet6!A2:A5).

You do this	Excel does this
Insert or copy	"Excel includes all values in cells A2 through A5 from the added worksheets in the calculations"
Delete	"Excel removes their values from the calculation"
Move out	Moving a sheet "to a location outside of the referenced worksheet range" removes its values from the calculation
Move an endpoint	"Excel adjusts the calculation to include the new worksheets between them unless you reverse the order of the endpoints"
Delete an endpoint	"Excel removes the values on that worksheet from the calculation"

The first row is the feature people actually want. Microsoft's framing: "You can even add another worksheet, and then move it into the range that your formula refers to" — drop a Facilities tab between Sales and HR and Sales:Marketing!B3 picks it up with no edit. It is also the trap. A scratch tab parked inside the range while somebody investigated a variance is silently added to every total, and nothing in the formula changes to say so.

The endpoint rules are the ones to read twice, because reversing the order does not error — it quietly moves the boundary. Microsoft's example: with a reference to Sheet2:Sheet6, "if you move Sheet2 after Sheet6 in the workbook, the formula points to Sheet3:Sheet6. If you move Sheet6 in front of Sheet2, the formula adjusts to point to Sheet2:Sheet5."

The defensive habit is to make the endpoints explicit and boring: two empty spacer tabs named Start and End bracketing the twelve monthly sheets, with the formula written =SUM(Start:End!B3). New months inserted between them are included automatically, and nothing dragged near the edges can redefine the span.

A 3D reference can also be given a name, through Formulas → Defined Names → Define Name, selecting the sheet span with Shift in the Refers to box — the same readability advantage Named ranges give an ordinary block.

The Google Sheets side

The sheet-range syntax is an Excel construction. In Google Sheets the equivalent is written out: each sheet's range listed as its own argument, which Google's own documentation shows in its definition of VSTACK — "This function appends ranges vertically and in sequence to return a larger array", with the syntax VSTACK(range1; [range2, …]) and sample usage VSTACK(A2:C4; A6:C8). Applied across tabs, that becomes =VSTACK(Jan!B5:E16,Feb!B5:E16,Mar!B5:E16), and a total is =SUM(Jan!B3,Feb!B3,Mar!B3).

Exceljet notes the cost of writing references out this way even in Excel: "the approach won't scale well as you add a larger number of sheets." Twelve months is a long formula; a tab per client is unmaintainable. The patterns that hold up at that size move the sheet names into data rather than into the formula text.

For anyone moving a workbook between the two applications, that makes Jan:Dec!B3 a restructuring decision rather than a syntax swap.

How Formula Foundry handles this

Formula Foundry does not create 3D references for you, and it does not audit which sheets a span currently covers — that stays a question for the tab strip and Excel's own reference behaviour. The add-on parses, formats, explains and translates formulas; it never evaluates your workbook, so it cannot tell you a total quietly grew by one sheet last Tuesday.

What it does is make the reference legible while you are writing it. The editor opens the selected cell's formula across multiple lines, indented along the nesting, and colour-codes it by token type — functions, ranges, constants and named references each get their own colour — so a sheet span inside a nested expression reads as a range rather than a run of letters and punctuation. On a formula combining several spans with ordinary references, that is the difference between checking the endpoints and hoping.

Two other pieces of the sidebar apply directly. The visual builder renders the formula as a form with every argument named, which is how you see at a glance that a SUM is taking one sheet-range argument rather than the three separate ranges you meant. And named references — including a name defined over a 3D span — are auto-suggested as you type and colour-coded once inserted, so the readable version costs no more typing than the cryptic one. The features overview shows the editor, the builder and the variables panel in Google Sheets and Excel.

FAQ