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.

## Implicit Intersections

*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.

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.

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.

## 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**.

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

*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.