Named ranges and structured references

Two ways to give data a name

Excel offers two kinds of names. A named range (also called a name) is a label you attach to a cell, a range, a constant or a formula, such as VatRate for cell $E$2.

A table is a block of data that Excel treats as one object with a name of its own, and whose columns you address by header, such as Journal[Amount]. That second form is called a structured reference.

Both replace an address by a word, but they solve different problems. A name gives one value or one fixed area a meaning. A table gives a list that keeps growing a shape that Excel maintains for you, so formulas stay complete when rows are added.

Creating and managing a name

There are three places to work with names:

• in the Name Box (the quickest way): select the cell or range, type the name in the Name Box left of the formula bar and press Enter
• with Formulas > Define Name: opens a dialog where you also choose the scope and add a comment
• in Name Manager (Ctrl+F3): lists every name with its value, the range it refers to and its scope, and lets you edit, rename or delete

A name has to follow a few rules:

• it starts with a letter, an underscore or a backslash
• it has no spaces (use VatRate or Vat_Rate)
• it may not look like a cell address
• it is not case sensitive and can hold up to 255 characters

Renaming a name in Name Manager updates every formula that uses it.

Scope: workbook or worksheet

Every name has a scope. A workbook name works on every sheet under the same word.

A worksheet name only works on its own sheet, and the same word can exist on several sheets with a different meaning, for example Total on each monthly sheet. Name Manager shows the scope in its own column.

From another sheet you reach a worksheet name by putting the sheet in front: January!Total.

Names for constants and formulas

A name does not have to point to a cell. In the Refers to box of Name Manager you can type a constant such as =0.21 or a formula such as =Sheet1!$E$2*12. The first gives you a rate without a cell, the second a calculation you reuse under one word.

For an input that matters to the audit, prefer a labelled cell that you name. A reviewer then sees the value in the grid, can trace it, and the name only supplies the readable word. A rate hidden in Name Manager changes every formula and shows in no cell.

=ROUND(SUM(B2:B7)*VatRate, 2)

=ROUND(SUM(B2:B7)*VatRate; 2)

=SUMIFS(C2:C9, C2:C9, ">"&ConfirmLimit)

=SUMIFS(C2:C9; C2:C9; ">"&ConfirmLimit)

Excel tables

Select a cell in your data and press Ctrl+T (or use Insert > Table, or Format as Table on the Home tab), and confirm that the data has headers.

Excel gives the block a name such as Table1; change it under Table Design in the Table Name box to something meaningful, for example Journal. Headers must be text and unique.

A table grows by itself. Type or paste a row directly under the last one and the table extends, formats the new row and copies a column formula down. The Total Row (in Table Design) adds a row with SUBTOTAL, which ignores rows hidden by a filter.

Structured references

A structured reference is built from:

• the table name
• the column header between brackets
• optionally, a special item that selects a part of the table

Excel writes it for you when you click a table column while typing a formula. The reference covers exactly the data rows, so it extends and shrinks with the table and never includes the header or the total row by accident.

The brackets follow a few rules:

• a header with a space needs no extra brackets: Journal[Net amount]
• extra brackets are needed for special characters in the header (comma, period, #, brackets, apostrophe) and for a header with a space after @: [@[Net amount]]
• inside the table, [@Amount] means the cell of this row, so a calculated column reads =[@[Net amount]]*VatRate

Dutch Excel shows the special items translated, as the table says; the column headers and your table name stay as you typed them. The formulas on this site are stored in English form.

=SUM(Journal[Amount])

=SUM(Journal[Amount])

=SUMIFS(Journal[Amount], Journal[Account], 4500)

=SUMIFS(Journal[Amount]; Journal[Account]; 4500)

=Journal[[#Totals],[Amount]]

=Journal[[#Totals],[Amount]]

When to use which

Use a table when the data is a list that gets longer: a journal, an invoice list, a sample. Use a name for a single value or a fixed area that several formulas share: a tax rate, a materiality, a year-end date. The two combine well: a table for the data and names for the inputs that test it.

The worked examples below use typed addresses so that this site can compute and check them. In your own workbook, replace the address by the name or the structured reference shown in the walkthrough.

The audit angle

Names make a client's formulas readable, but they hide where a value lives: =B2*VatRate does not show which cell or constant VatRate is. Open Name Manager first on any client workbook. Then check:

• the Refers to column for each name
• names with errors (sort or filter on them)
• names that refer to #REF! or to another workbook

A deleted sheet leaves exactly such names behind.

Also check these:

• what a name contains: a constant typed into Name Manager is an input without a cell, and a named formula can hide a calculation
• hidden names: a macro can hide names from Name Manager, so when a workbook behaves oddly, look at the formulas themselves too (Ctrl+` shows them all)
• tables: whether a total row, a filter or a hidden column changes what a structured reference sees

Related topics

• Cell references: relative, absolute and mixedA cell reference is the address a formula reads. Dollar signs decide whether it moves when you copy the formula, and getting that right is what lets one formula fill a whole column or table.
• CriteriaA criterion is the condition a cell must meet to be counted, added up or averaged: how to write exact, comparison, date and blank criteria, and how to combine several.
• Spill and dynamic arraysA spill is what happens when one formula returns several values: they flow into the cells below and beside it. See which Excel does this, how to refer to the result with #, and what #SPILL! means.
• Error valuesAn error value is a result that starts with # and names what went wrong, such as #N/A or #DIV/0!. It spreads through formulas, and hiding it can hide a gap in your audit evidence.