Named ranges in Excel: how it works

Give a cell or range a name, such as Rate or Scores, and use that name in your formulas: =SUM(Scores) instead of =SUM(C2:C6). It reads more easily, and a name never shifts.

Try the first exercises for freeIn the lesson Named ranges

What is a named range?

A named range is a cell or a group of cells with a name of its own, such as Tax for the cell with the tax rate or Marks for the column with marks. In your formulas, you then type the name instead of the cell address: =SUM(Marks) instead of =SUM(C2:C6).

A formula with names reads almost like a sentence: =A2*Tax says straight away what's happening. A name also always refers to the same cells, like an absolute reference with dollar signs. If you fill the formula down, Tax keeps looking at the rate.

When do you give cells a name?

A rate or a percentage you use in lots of formulas: =A2*Tax.
A column you often add up or count: =SUM(Marks) or =AVERAGE(Marks).
A lookup table for VLOOKUP: =VLOOKUP(A2,PriceList,2,FALSE).
The choices for a drop-down list, so that you can simply type =Region under Source.

Creating and using a name, step by step

This is how to go about it:

Select the cells that get a name, for example C2 to C6 with the marks.
Click the Name Box, the small box to the left of the formula bar that normally shows the address of the cell.
Type the name, such as Marks, and press Enter. Without Enter, Excel doesn't save the name.
Use the name in a formula. Excel suggests it as soon as you type the first letters.=SUM(Marks)

Rules for a name

A name starts with a letter, has no spaces and mustn't look like a cell address. So Tax1 won't work, because that's a cell in column TAX. Use an underscore instead of a space: Tax_rate.

Where it often goes wrong

A name always refers to the same cells, just like an absolute reference with dollar signs. That's handy for a rate, but not for a formula that has to shift for each row.
If you delete the cells of a name, every formula that uses it gets #REF!.

Want to try it yourself?
Practise this in the lesson Named ranges. The first exercises are free.

Try the first exercises for free

Frequently asked questions

Where can I see all my names?

Under Formulas > Name Manager, or with Ctrl + F3. There you can rename a name, change its range or delete it.

Does a name work across the whole workbook?

Yes. By default you can use a name on every sheet of the workbook. So a rate can sit on a Settings sheet while you use it everywhere, as in =A2*Rate.