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 of the formula range is determined by the minimum value (C1) and the maximum value (C5).

The formula =SUM(A1:B1) persists within the full (formula) 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. 

What is a Formula Range :: Excel Glossary :: PerfectXL Spreadsheet Validation Tool

Problems 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 in formula ranges 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.

Read more about the detection of range issues