Broad Strokes

Reference formulas allow you retrieve data by referencing the location (and/or contents) of other cells

Reference formulas are a good trick to have up your sleeve if you happen to be performing repetitive tasks on similarly formatted blocks of data, or if you need to create smarter lookups. These formulas also offer some interesting possibilities when they are paired with named ranges (such as tables), and can also be useful as a source of unique index numbers to associate with a range referenced in an array formula.

Usage and Style

Reference formulas are a worthy addition to your Excel arsenal

The selection of reference formulas in Excel leaves little to be desired – my only caveat is that you won’t see opportunities to use these formulas unless you’re aware that they exist. If you practice using them for simpler tasks, you’ll find them at your fingertips in the situations where they could really save you some time.

Syntax

Click the following buttons to read more about each formula.

INDIRECT()

Returns the cell/range at the given cell address

ADDRESS()

Returns a cell reference as text, based on cell coordinates and sheet name

ROW() & COLUMN()

Queries the row or column number of a given cell address

ROWS() & COLUMNS()

Queries the number of rows or columns in the given range

OFFSET()

References a cell or range based on its offset and size relative to a given reference

Examples

The excel file embedded below demonstrates the use of these formulas. Note that these formulas are expansive in scope and may prove to be useful in a variety of circumstances.

All of the example formulas listed here look up data from the named table at the top of the sheet.

Download this Workbook [12.20 KB]