Implicit intersection - meaning of @ sign in Excel formulas

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.

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:

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:

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.

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:

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))

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.