Explanation

This is a pretty standard use of the SUMIFS function. In this case, we need to sum amounts based on two criteria: type (forecast or actual) and group. To sum by type, the range/criteria pair is:

type,G$4

where type is the named range D5:D14, and G4 is a mixed reference with the row locked in order to match the column header in row 4 when the formula is copied down.

To sum by group, the range/criteria pair is:

group,$F5

where group is the named range B5:B14, and F5 is a mixed reference with the column locked in order to match group names in column F when the formula is copied across.

Variance formulas

To calculate a variance amount in column I, the formula simply subtracts the forecast from the actual:

=G5-H5

The variance percentage formula in column J is:

=(G5-H5)/H5

with percentage number format applied.

Notes

  1. The data shown here would work well in an Excel Table , which would automatically expand to include new data. We are using named ranges here to keep the formulas as simple as possible.
  2. Pivot tables can also be used to calculate variance. Formulas provide more flexibility and control at the cost of more complexity.

Explanation

In this example, the goal is to create an “locked” reference that won’t change when columns or rows are added or deleted in a worksheet, or during a copy / paste / cut operation.

The INDIRECT function accepts text, and evaluates that text as a reference. As a result, the text is not susceptible to changes, like a normal cell reference. It will continue to evaluate to the same location regardless of changes to the worksheet. For example, this formula:

=INDIRECT("A1")

will continue to return a reference to cell A1 even if row 1, or column A, are deleted.

Examples

A reference created with INDIRECT can be used just like a regular cell reference in other formulas. In the worksheet shown above, the formulas in E5:E9 use INDIRECT to create a locked reference that won’t change:

=INDIRECT("B5")
=SUM(INDIRECT("B5:B16"))
=MIN(INDIRECT("B5:B16"))
=MAX(INDIRECT("B5:B16"))
=COUNT(INDIRECT("B5:B16"))

This approach can be useful when a worksheet is routinely edited in a way that would break traditional cell references.

Sheet names

Formulas with sheet names must follow standard rules. Sheets names without spaces or punctuation need no extra handling:

=INDIRECT("Sheet1!A1")

Sheet names with space or punctuation need to be enclosed in single quotes (’):

=INDIRECT("'Sheet 1'!A1") // note single quotes

Note: if you rename a sheet name used in INDIRECT, the reference will break. This happens because the reference is entered as text and therefore is not automatically updated like a normal reference.

Different from absolute and relative references

Using INDIRECT is different from standard absolute, relative, and mixed references . The $ syntax is designed to allow “intelligent” copying and pasting of formulas, so that references that need to change will change , while references that shouldn’t change, will not change . Using INDIRECT with text references stops all changes to the reference, even when columns/rows are inserted or deleted, or when a sheet is renamed.

Note: INDIRECT is a " volatile function " function, and can cause slow performance in large or complicated workbooks.