Implicit Intersection describes a formula behavior in Excel where many values are reduced to a single value. Typically, it happens when a range or array is passed into a formula meant to display a single result in a single cell. In this situation, Excel will resolve the formula to a single value following the steps below:
- If the result is already single value, return the value.
- If the result is an array , try to return a value from the same row or column as the formula.
- Otherwise, return the top-left value from the array
Sometimes, when the result is an array, Excel won’t return the top-left value in array (step #3) unless the formula is entered an array formula with control + shift + enter. In other words, entering a formula with control + shift + enter disables implicit intersection.
Example
Implicit intersection can occur when a formula is entered next to vertical data, or above or below horizontal data. For example, the formula in D6 in the example shown is:
=B4:B8+1
In this case, Excel resolves the range B4:B8 to the value in B6 (3) and returns a result of 4. When Excel can’t determine a single reference in a formula where a range is passed, but a single value is expected, a #VALUE error is returned. For example, if same formula above is entered in cell D9, the result is #VALUE.
Array formulas
Entering an array formula with control + shift + enter (CSE) explicitly disables the implicit intersection behavior. This makes it possible to create formulas that manipulate multiple values input as ranges. If the formula in D6 is wrapped in SUM, then entered with control + shift + enter:
{=SUM(B4:B8+1)}
All values in the range are processed, and the formula returns 27.
Excel Tables
Implicit intersections can be useful in Excel Tables , where the same formula can be used in multiple cells (for consistency) but continue to resolve to a single cell at the row label. For example, this formula would add 7 days to the value in a “date” column in table:
=table[date]+7
Even though the formula refers to the entire “date” column, the formula will operate on a single value in the date column at the row level.
Dynamic Array Excel and @ operator
In Excel 365 , there is no need to enter a formula with control + shift + enter to enable array behavior, because all formulas are treated as array formulas by default. However, for compatibility reasons, you will sometimes see the @ symbol inserted in a formula created in an older (pre dynamic array) version of Excel. This @ symbol is called the implicit intersection operator, and it disables array behavior. In other words, it tells Excel you want a single value.
This is done to ensure that older formulas continue to return the same (single) result when they might otherwise spill multiple values onto the worksheet. In general, functions that return multi-cell ranges or arrays will be automatically prefixed with @ if they were created in an earlier version. This behavior only occurs in dynamic array versions of Excel.
For simplicity, we use the term “Legacy Excel” to refer to any version of Excel before dynamic array formulas were introduced. Practically speaking, this means Excel 2019 and older, since Excel 365 and Excel 2021 are the only versions of Excel that include dynamic array formulas and new functions like SORT , UNIQUE , FILTER , SEQUENCE , and XLOOKUP .
Dynamic array formulas are a huge change to the Excel formula engine because they let you easily work with multiple values at the same time in a formula. In practical terms, this means array formulas “just work”. In Legacy Excel, most array formulas still need to be entered with control + shift + enter.
Example
The LEN function returns the length of a text string as a number:
=LEN("apple") // returns 5
To get a total of all characters in a range, you can wrap LEN inside the SUM function like this:
=SUM(LEN(range)) // total characters in range
This is a simple array formula , but it must be entered with control + shift + enter in Legacy Excel:
{=SUM(LEN(B5:B10))}
Note: Excel will add the curly brackets automatically when the formula is entered with control + shift + enter. Do not add the curly braces manually.
In Modern Excel, the same formula below will work as-is, without special handling:
=SUM(LEN(B5:B10))
The bottom line is that array formulas in Legacy Excel need special handling. In current versions of Excel, array formulas just work.
