Implicit intersection operator: @

The implicit intersection operator was introduced as part of substantial upgrade to Excel's formula language to support dynamic arrays. Dynamic arrays bring significant new calculation ability and functionality to Excel.

Upgraded Formula Language

Excel's upgraded formula language is almost identical to the old language, except that it uses the @ operator to indicate where implicit intersection could occur, whereas the old language did this silently. As a result, you may notice @'s appear in some formulas when opened in dynamic array Excel. It's important to note that your formulas will continue to calculate the same way they always have.  

What is 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. If your formula was returning a single value, then implicit intersection did nothing (even though it was technically being done in the background). The logic works as follows:

  • If the value is a single item, then return the item.

  • If the value is a range, then return the value from the cell on the same row or column as the formula.

  • If the value is an array, then pick the top-left value.

With the advent of dynamic arrays, Excel is no longer limited to returning single values from formulas, so silent 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 @.  

Why the @ symbol? 

The @ symbol is already used in table references to indicate implicit intersection. Consider the following formula in a table =[@Column1]. Here the @ indicates that the formula should use implicit intersection to retrieve the value on the same row from [Column1].  

Can you remove the @? 

Often you can. It depends on what the part of the formula to the right of the @ returns: 

  • If it returns a single value (the most common case), there will be no change by removing the @.

  • If it returns a range or array, removing the @ will cause it to spillto the neighboring cells.

If you remove an automatically added @ and later open the workbook in an older version of Excel, it will appear as alegacy array formula (wrapped with braces {}), this is done to ensure the older version will not trigger implicit intersection.

When do we add the @ to old formulas? 

Generally speaking, functions that return multi-cell ranges or arrays will be prefixed with @ if they were authored in an older version of Excel. It's important to note that there is no change to the way your formula behaves — you can just see the previously invisible implicit intersection now. Common functions that could return multi-cell ranges include INDEX, OFFSET, and User Defined Functions (UDFs).  A common exception is if they are wrapped in a function that accepts an array or range (e.g. SUM() or AVERAGE()). 

See Excel functions that return ranges or arrays for more details.

Examples

Original formula

As seen in dynamic array Excel 

Explanation

=SUM(A1:A10) 

=SUM(A1:A10) 

No change - No implicit intersection could occur, as the SUM function expects ranges or arrays. 

=A1+A2 

=A1+A2 

No change - No implicit intersection could occur. 

=A1:A10 

=@A1:A10 

Implicit intersection will occur, and Excel will return the value associated with the row the formula is in.

=INDEX(A1:A10,B1) 

=@INDEX(A1:A10,B1) 

Implicit intersection could occur. The INDEX function can return an array or range when its second or third argument is 0.  

=OFFSET(A1:A2,1,1) 

=@OFFSET(A1:A2,1,1) 

Implicit intersection could occur. The OFFSET function can return a multi-cell range. When it does, implicit intersection would be triggered. 

=MYUDF() 

=@MYUDF() 

Implicit intersection could occur. User Defined Functions can return arrays. When they do, the original formula would have triggered implicit intersection. 

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See Also

FILTER function

RANDARRAY function

SEQUENCE function

SORT function

SORTBY function

UNIQUE function

#SPILL! errors in Excel

Dynamic arrays and spilled array behavior

Expand your Office skills
Explore training
Got It
Get instant Excel help
Connect to an expert now
Subject to Got It terms and conditions

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×