What is a formula range

A formula range is usually a reference to a range of cells, within which a formula persists consistently throughout the full range. Cell references within this formula can however be relative.

A formula range, like a cell range, is defined by the reference of the upper left cell of the range and the reference of the lower right cell of the range.

Example of a formula range

In the example below we see the formula range (C1:C5). The definition is determined by the minimum value (C1) and the maximum value (C5).

The formula =SUM(A1:B1) persists within the full range. The cell references in this formula are relative to the position of the formula within the range: in cell C1 we see the calculation of the sum of A1 and B1, but in cell C2 we see the calculation of the sum of A2 and B2 and in cell C3 we see the calculation of the sum of A3 and B3, and so on.

Automatic expansion

When you add new values to the columns (or rows) at the end of a formula range, the formula will expand automatically.

In the image below, we insert the number 42 in column A, cell A6 and the number 4 in column B, cell B6. As soon as we hit enter, the SUM of these two values is automatically calculated in cell C6; the formula range expanded!

Issues with formula ranges

Several issues can occur in formula ranges, such as references to empty cells, which is either meaningless or a mistake. References to merged cells can also be risky, because a merged cell has different reference possibilities (cell names), but only one is correct. Another issue occurs when a formula range is interrupted, for example, by a numeric value. Because the cells in the range will only display the result of the formula, it is hard to spot an interruption.

PerfectXL detects different types of problems with ranges.