Implicit intersection and @ operator in Excel 365 and older versions

Implicit intersection has been around in Excel for years, but very few people cared about it. Now that it is no longer the default behavior in Excel 365, so many questions arise. This tutorial aims to provide the answers.

You have the feeling you know everything about your workbooks, and then a @ character appears from nowhere in the beginning of your formulas. What does this @ mean? And how does it work precisely? In a nutshell, it is an implicit intersection operator that disables the new default array behavior of a formula and tells Excel to return a single value. For more detail, please continue reading.

Excel implicit intersection

Implicit intersection in Excel means reducing multiple values to a single value. Normally, it occurs when an array or range is supplied to a formula that is supposed to output just one value in a single cell.

Implicit intersection in Excel 2019 - 2000

In traditional Excel, implicit intersection is the default behavior. It is silently performed in the background for all formulas. The implicit intersection logic is as follows:

  • If the formula produces a single value, return that value (in fact, implicit intersection does nothing in this case).
  • In case of a range, use a value from the cell on the same row or column as the formula.
  • In case of an array, use the top-left value.

For example, when multiplying two columns of numbers, Excel picks just one number from each column in the same row where the formula is and outputs the result only in one cell (D2 in our case):

=B2:B5*C2:C5

To multiply the numbers in other cells, you need to copy the formula down.

The default implicit intersection in pre-dynamic Excel

To disable implicit intersection, you must enter an array formula with Ctrl + Shift + Enter (which is why traditional array formulas are sometimes called CSE formulas). This clearly tells Excel to process multiple values input as ranges or arrays.

In our case, select cells D2:D5, type the above formula and confirm it by pressing the Ctrl + Shift + Enter keys together. Once you do this, the formula gets surrounded in {curly braces}, indicating it's an array formula. As the result, the numbers in each row get multiplied at once:

Traditional array formula (CSE formula)

Implicit intersection in Excel 365

The introduction of dynamic arrays has changed the default behavior of all formulas in Excel 365. Now, any formula that can potentially produce multiple results, automatically spills them onto the sheet. That makes implicit intersection unnecessary, and it is no longer triggered by default. For this reason, Excel 365 is sometimes called dynamic array Excel or DA Excel.

Here, the entire ranges are multiplied with a regular formula that is only entered in the topmost cell (D2):

=B2:B5*C2:C5

The result is a spill range consisting of 4 cells:

Dynamic array formula in Excel 365

If you want a formula to return just one value, you need to explicitly enable implicit interception. For this, they introduced a special operator, and in the next section you will find full details about it.

Excel implicit intersection operator - @ symbol

The implicit intersection operator was introduced in Excel 365 to prevent the default dynamic array behavior. If you wish a formula to return just one value, put @ before the function's name (or before a certain range or array inside the formula), and it will behave like a regular non-array formula in pre-dynamic versions.

For example:

=@B2:B5*@C2:C5

Implicit intersection operator in Excel 365

Note. The implicit intersection operator is only supported in Microsoft 365 subscriptions. If you try to add the @ sign in older versions, it will be silently removed upon completing the formula.

Why is @ added to old formulas?

In Excel 365, you may notice the @ character added to some of your formulas when opening a workbook created in an older version. For the most part, it is done to force a formula to behave the same way as it did in the original version in which it was created. In other words, if a formula returned a single value in an older version but would return multiple results in Excel 365, it will be automatically prefixed with @ to deactivate the array behavior.

Typically, the intersection operator is inserted before the function that could return multi-cell arrays or ranges such as OFFSET, INDEX, or user-defined functions.

For example, the following formula authored in pre-dynamic Excel:

=INDEX(B2:C5,,F1)

will take the following form in dynamic array Excel:

=@INDEX(B2:C5,,F1)

The reason is that without the @ operator, the formula will return all the values from C2:C5 because the row_num argument of the INDEX function is omitted. To ensure the consistent behavior in all versions, the previously unnoticeable implicit intersection becomes evident. Please compare the results:

The implicit intersection operator is added to old formulas.

In case a function with a potential multi-cell output is nested in another one that can handle arrays and output a single result (such as SUM, COUNT, AVERAGE, etc.), then there is no reason to trigger implicit intersection, and the formula is transferred to dynamic Excel as-is without adding the @ sign. For example:

=AVERAGE(INDEX(B2:C5,,F1))

The formula is transferred to dynamic Excel without adding the @ sign.

Can I remove the @ symbol from my formulas?

Sure, you can. Excel performs the formula conversion just once. If the @ operator breaks or negatively changes the behavior of your formulas, you can delete @ manually, and it won't appear again after saving the workbook.

What are the consequences of deleting the implicit intersection operator? Depending on what the part of the formula following the @ sign returns, there are three possible outcomes:

  • If a single value is returned, there will be no change.
  • If an array is returned, it will spill into the adjacent cells.
  • If an array is returned but there are not enough empty cells to display all the values, a #SPILL error will occur.

Why use the @ operator in Excel 365?

As already mentioned, Excel for Microsoft 365 treats all formulas as array formulas by default, without you having to press Ctrl + Shift + Enter like you used to do in the previous versions. If you want to disable the array behavior, then insert the implicit intersection operator.

Example 1. Get rid of #SPILL! error

A very common scenario is preventing or fixing #SPILL errors. If you find it convenient to refer to entire columns (which is generally not a very good idea because it slows down Excel), but nevertheless such a formula will work fine in pre-dynamic versions:

=VLOOKUP(A:A, D:E, 2, FALSE)

In dynamic Excel, it will result in a #SPILL error because there isn't enough space to display nearly 1.05 million results.

Adding @ before the lookup_value argument resolves the problem:

=VLOOKUP(@A:A, D:E, 2, FALSE)

Use implicit intersection to prevent #SPILL errors

When Excel foresees that the formula may spill beyond the edges of the worksheet, it will suggest a correction, and you would be wise to accept it:

Implicit intersection prompt in Excel

Example 2. Get the formula to work properly in older Excel

If you are using a Microsoft 365 subscription and share your files with someone who sticks with an older version, it's important to ensure the consistent formula behavior for everyone.

Suppose you have written this dynamic array formula in Excel 365:

=B2:B5*C2:C5

In older versions, it will be converted to a legacy CSE array formula:

{=B2:B5*C2:C5}

and return a range of values in both cases:

A dynamic array formula is converted to a CSE array formula.

If you wish to output just one result, add @ before each expression, and then copy the formula to as many cells as needed (to keep the ranges unchanged, remember to lock them with absolute cells references):

=@$B$2:$B$5*@$C$2:$C$5

In older Excel, the @ symbol will be automatically removed, and the formula will take a normal look:

=$B$2:$B$5*$C$2:$C$5

A formula with @ operator is converted to a normal non-array formula.

What you should not do is mix implicit intersection and array calculations within one formula! For example, if you try to enter something like this in Excel 365:

=@B2:B5*C2:C5

You will be notified that such a formula is not supported in older versions:

The formula is not supported in older versions of Excel

If you reject the proposed variation, the mixed formula will be committed and will deliver some results (though, it may not be the results you expected). But when you open that formula in pre-dynamic Excel, the _xlfn.SINGLE function will appear instead of the implicit intersection operator:

=_xlfn.SINGLE(B2:B5)*C2:C5

When this formula is evaluated by older Excel, a #NAME! error will be returned.

Implicit intersection in Excel tables

Generally speaking, the implicit intersection behavior in tables is consistent with your Excel version.

In Excel 2019 and earlier, you can refer to a whole column, and the formula will resolve to a single cell in the current row.

For example, this formula successfully multiplies the numbers in the Price and Qty. columns:

=[Price]*[Qty.]

Even though it refers to the entire columns, pre-dynamic Excel still operates on single values at a row level.

Implicit intersection in Excel table

In Excel 365, this approach won't work because implicit intersection is off by default. Because the formula returns multiple values and Excel is unable to fit them all into a table, it will suggest prefixing the column names with the @ symbol:

Formulas returning multiple values are not supported in Excel tables.

If you have experience with table references, this syntax should be familiar to you - the @ symbol indicates that the formula will process the values from columns Price and Qty. on the same row.

=[@Price]*[@[Qty.]]

And it will work nicely in all versions:

Implicit intersection operator in table references

Tip. If you intend to use a dynamic array function to calculate a table's data, be sure to place your formula outside the table, because inside tables dynamic arrays are not supported.

That's how implicit intersection works in Excel. I thank you for reading and hope to see you on our blog next week!

You may also be interested in:

2 responses to "Implicit intersection and @ operator in Excel 365 and older versions"

  1. Laxman Kumar says:

    Hi Svetlana , i like your article, I requested u please write an article on Lambda Function.

Post a comment



Thank you for your comment! When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to your query. We cannot guarantee that we will answer every question, but we'll do our best :)