In order to ensure backward compatibility, the character "@" is equivalent to function SINGLE() in the new version of Excel (Office 365).

Excel releases recently the dynamic array functionality. This functionality enables users to display arrays for any formula supposed to return arrays (like the MM_RANGE function‍).

For example, the formula "=A2:B5" will display full range whereas in the old version it would display only a value from the range at the same row or same column as the formula.

When loading a file that was created with an old version of Excel not containing the dynamic arrays, Excel adds the @ operator to some formulas to ensure backward compatibility.

What is the implicit intersection?

Implicit intersection logic reduces many values to a single value. Excel did this to force a formula to return a single value since a cell could only contain a single value. The logic works as follows:

• if the value is a single item, then it returns this item
• if the value is a range, then it returns the value from the cell on the same row or column as the formula.
• if the value is an array, then it then picks the top-left value

With the advent of dynamic arrays, Excel does not need to return a single value for each cell, so an invisible implicit intersection is no longer necessary. Where an old formula could invisibly trigger implicit intersection, dynamic array enabled Excel shows where it would have occurred with the @. @ is finally just an indication (in your new version of Excel) of an old formula that triggered implicit intersection.

When is @ added to old formulas?

Functions that return multi-cell ranges or arrays will be prefixed with @ if they were authored in an older version of Excel. It is important to note that there is no change to the way your formula behaves — previously invisible implicit intersection is just visible now. Most common functions that return multi-cell ranges: INDEX, OFFSET, or MYUDF. There is an exception if the function accepts an array or range (SUM or AVERAGE for example).

After loading the old formula OFFSET in dynamic array Excel, here is what it returns in Excel:

The operator @ is added there but the formula returns a single value, exactly like in the old version.

If you write the formula A1:A3 in a workbook from an older version of Excel and load it in dynamic array Excel, you can see below that @ is added too:

This formula returns, as in the older version, the single value from A1:A3 at the same row or column as the formula cell (here #VALUE because no value found).

Last example with the SUM function. Even if you write a SUM formula, you can see in the following picture that no @ is added to the formula:

This exception is due to the fact that function SUM expects arrays or ranges.

Can you remove the @?

Often you can. It depends on the return of your formula at the right of @:

• If it returns a single value, no change if you remove the @.
• If it returns a range or array, it will spill to the neighboring cells if you remove @ (in this case, the previous OFFSET formula example and range A1:A3 example will display ranges). It is, in this case, like resizing and populating by using MM_RANGE to your formula.

If you remove an automatically added @ and later open the workbook in an older version of Excel, it will appear as a legacy array formula (wrapped with braces {}).