Implicit intersection: use of @ sign in Excel formulas

Detailed explanation of the implicit intersection operator @ symbol of Excel

Excel's implicit intersection feature has been around for many years, but is little known. It wasn't until Excel 365 no longer used it as the default behavior that many questions arise. This tutorial aims to explain the meaning of the "@" symbol (called the implicit intersection operator) in Excel formulas.

You may feel like you know your workbook well, and then a "@" character suddenly appears at the beginning of the formula. What does this mean? How exactly does it work? In short, it is an implicit intersection operator that disables the new default array behavior of the formula and instructs Excel to return a single value. For more details, please continue reading.

Excel implicit intersection

Implicit intersection in Excel refers to simplifying multiple values ​​into a single value. Typically, when an array or range is provided to a formula, the formula should output only one value in one cell.

Implicit Intersection in Excel 2019 - 2000

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

• If the formula produces a single value , that value is returned (actually, in this case implicit intersection does nothing).
• For ranges , use values ​​in cells that are in the same row or column as the formula.
• For arrays , use the value in the upper left corner.

For example, when multiplied by two columns of numbers, Excel selects only one number in each column that is in the same row as the formula and outputs the result in only one cell (D2 in our case):

=B2:B5*C2:C5

To multiply by the number in other cells, you need to copy the formula downward.

To disable implicit intersection , you must use Ctrl Shift Enter to enter the array formula (this is why traditional array formulas are sometimes called CSE formulas ). This explicitly tells Excel to process multiple values ​​as range or array input.

In our example, select cell D2:D5, type the above formula, and press Ctrl Shift Enter to confirm together. Once done, the formula will be enclosed in braces { } to indicate that this is an array formula. As a result, the numbers in each row are multiplied immediately:

Implicit intersection in Excel 365

The introduction of dynamic arrays changes the default behavior of all formulas in Excel 365. Now any formula that can produce multiple results will automatically overflow it into the worksheet. This makes implicit intersection unnecessary and no longer triggered by default . Therefore, Excel 365 is sometimes called dynamic array Excel or DA Excel .

Here, the entire range is multiplied by a regular formula entered only in the topmost cell (D2):

=B2:B5*C2:C5

The result is an overflow range with 4 cells:

If you want the formula to return only one value, you need to explicitly enable implicit intersection . To do this, they introduced a special operator, and in the next section you will find complete details about it.

Implicit Intersection Operator - @ symbol in Excel formula

The implicit intersection operator was introduced in Excel 365 to prevent default dynamic array behavior. If you want the formula to return only one value, add "@" before the function name (or before a range or array within the formula), and it will run like a regular non-array formula before the dynamic version.

For example:

=@B2:B5*@C2:C5

Note: The implicit intersection operator is supported only in Microsoft 365 subscriptions. If you try to add the "@" symbol in an older version, it will be silently removed after you complete the formula.

Why add @ to the old formula?

In Excel 365, when opening a workbook created in an older version, you may notice that some formulas have the "@" character added. In most cases, this is done to force the formula to behave the same as it did in the original version where it was created. In other words, if a formula returns a single value in an older version, but returns multiple results in Excel 365, it is automatically preceded by "@" to disable array behavior.

Typically, the intersection operator is inserted before a function that may return a multi-cell array or range, such as OFFSET, INDEX, or user-defined functions.

For example, the following formula written in Excel before Dynamic Excel:

=INDEX(B2:C5,,F1)

In dynamic array Excel, the following form will be taken:

=@INDEX(B2:C5,,F1)

The reason is that without the "@" operator, the formula returns all values ​​in C2:C5, because the row_num parameter of the INDEX function is omitted. To ensure consistency in all versions, previously unobtrusive implicit intersections become obvious. Please compare the results:

If functions with potential multi-cell output are nested in functions that can process arrays and output a single result (such as SUM, COUNT, AVERAGE, etc.), there is no reason to trigger implicit intersections, and the formula will be transferred to dynamic Excel as is without adding the "@" symbol. For example:

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

Can I remove the @ symbol from the formula?

sure. Excel performs formula conversion only once. If the "@" operator breaks or negatively changes the behavior of the formula, you can manually delete "@" and it will not appear again after saving the workbook.

What are the consequences of deleting the implicit intersection operator? According to the content returned by the formula section after the "@" symbol, there may be three possible results:

• If a single value is returned, no changes occur.
• If the array is returned, it will overflow to adjacent cells.
• A #SPILL error occurs if the array is returned but there are not enough empty cells to display all values.

Why use the @ operator in Excel 365?

As mentioned earlier, Excel for Microsoft 365 treats all formulas as array formulas by default without pressing Ctrl Shift Enter like in previous versions. If you want to disable array behavior, insert the implicit intersection operator.

Example 1. Eliminate #SPILL! Error

A very common scenario is to prevent or fix #SPILL errors. If you find it convenient to reference the entire column (this is usually not a good idea because it slows down Excel), however such formulas work fine in versions before dynamic:

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

In Dynamic Excel, it will cause a #SPILL error because there is not enough space to display nearly 1.05 million results.

Adding "@" before looking for value parameters can solve this problem:

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

When Excel predicts that formulas may overflow the edge of a worksheet, it recommends corrections, and it is wise to accept it:

Example 2. Make formulas work properly in legacy Excel

If you are using a Microsoft 365 subscription and share files with people who insist on using older versions, you must ensure consistency in formula behavior for everyone.

Suppose you wrote this dynamic array formula in Excel 365:

=B2:B5*C2:C5

In older versions, it will be converted to older versions of CSE array formulas:

{=B2:B5*C2:C5}

And in both cases a series of values ​​will be returned:

If you want to output only one result, add "@" before each expression and copy the formula to as many cells as you want (to keep the range unchanged remember to lock them with absolute cell reference):

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

In older versions of Excel, the "@" symbol will be automatically deleted and the formula will take a normal appearance:

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

What should not be done is to mix implicit intersection and array calculations in one formula! For example, if you try to enter the following in Excel 365:

=@B2:B5*C2:C5

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

If you reject the suggested variant, the mixing formula will be submitted and will provide some results (although it may not be the one you expected). However, when you open the formula in Excel before Dynamic Excel, the _xlfn.SINGLE function appears instead of the implicit intersection operator:

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

When older Excel calculates this formula, a #NAME! error is returned.

Implicit intersection in Excel tables

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

In Excel 2019 and earlier , you can reference the entire column and the formula resolves to a single cell in the current row.

For example, this formula successfully multiplies the numbers in the "Price" and "Quantity" columns:

=[Price]*[Qty.]

Even if it references the entire column, Excel before dynamic Excel still operates on a single value at the row level.

In Excel 365 , this approach will not work, because implicit intersection is turned off by default. Because the formula returns multiple values ​​and Excel cannot put them all into the table, it recommends adding the @ symbol before the column name:

This syntax should be familiar if you have experience using table references - @ symbolic representation formulas will handle the values ​​of the "price" and "quantity" columns in the same row.

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

It will work well in all versions:

Tip: If you plan to use dynamic array functions to calculate the data of a table, make sure to put the formula outside the table, because dynamic arrays are not supported within the table.

This is how implicit intersection works in Excel. Thank you for reading and hope to see you next week on our blog!

The above is the detailed content of Implicit intersection: use of @ sign in Excel formulas. For more information, please follow other related articles on the PHP Chinese website!