Formula intersections can help simplify your formulas. There are 2 types of intersections in Excel: implicit and explicit. In this guide, we’re going to show you implicit vs explicit intersections in Excel.

Download Workbook

Implicit Intersections

If you are using Office 365, you might already have access to a feature called dynamic arrays. Dynamic arrays populate cells automatically when you use a range, like you will see in some of the examples in the guide. To avoid this “populating” behavior, you need to use the single operator @ in the front of each range. For example, =@B9:B12+@C9:C12. You can learn more about this here: Single operator in Excel.

An implicit intersection what Excel gets a value of a single cell in a range based on the row or column of the formula that contains the reference. For example, in the following image, there are consecutive numbers in A1:A4. The cell C2 contains a basic formula which targets A1:A4. Although, the formula refers to the whole range, Excel displays only the 2 value, which is in the same row.

Implicit vs Explicit Intersections in Excel 01

The same principle is also true for the formula in C8. However, this time the formula returns a value based on the column.

You can use this with multiple ranges as well. The following example shows a traditional use case and 2 other ways of using implicit intersections. While the first version uses the reference, the second uses named ranges.

Implicit vs Explicit Intersections in Excel 02

The Total columns are calculated by the values in Qty in Stock and Purchased columns. To apply this to the other rows can copy the formulas, or use the AutoFill feature.

On the other hand, you can use the same formulas for the implicit intersection approach. We suggest you use named ranges as well to organize your workbook better. For more information about named ranges please see: Excel Named Ranges.

VALUE Errors

The most likely cause of #VALUE! errors is the formula placement. If the formulas and the referenced ranges don’t have share a row or column, you will get this error.

For example, the formula in D28 returns an error, because the QtyInStock and Purchased named ranges don’t have a cell in column D or row 28.

Implicit vs Explicit Intersections in Excel 03

Explicit Intersections

Explicit intersections are intersections of two ranges. As opposed to implicit intersection, you can supply each range and bind them with an intersection operator - the space character (“ “).

The formula returns the sum of cells in C4:D5 in the following example. The space between B4:E5 and C2:D7 ranges means that Excel will evaluate their intersection, which is C4:D5.

Implicit vs Explicit Intersections in Excel 04

The following example uses the same data. However, this approach copies individual cells from a single table using explicit intersections.

Implicit vs Explicit Intersections in Excel 05

Explicit interceptions do not limit you to share a row or column with the given reference. The only disadvantage is that you need to enter each range individually.