Named Range - associate a text value with a number

You can set up a named range where a text value is associated with a number value. For example, suppose you had four sales people in your team: John, Ali, Priyanka, and Helen. Each person sold a number of units per year. John sold 21 units, Ali sold 31 units, Priyanka sold 32 units, and Helen sold 45 units. You want to know how many units Ali and Helen sold combined. If you use Named Ranges, you could set up a formula with this in a cell on your spreadsheet:

=Ali + Helen

You can see this in the image below, where cell A7 is highlighted:

A simple Excel spreadsheeet with sales figures

In the formula bar, it doesn't say =B3 + B5. It says =Ali + Helen. Yet we still get an answer of 76, just the same as if we'd entered the formula =B3 + B5. The name Ali has been associated with a value of 31, and the name Helen has been associated with a value of 45. So how does it work?

Create the simple spreadsheet above, with the same headings and values. Now select the cells from A2 to B5:

Selected cells

With the cells selected, click on the Formulas ribbon at the top of Excel. On the Formulas ribbon, locate the Defined Names panel, and click on Create from Selection:

The Defined Names panel in Excel

When you click on Create from Selection, you'll see the following dialogue box appear:

Create Names from Selection dialogue box

Make sure Left Column is selected, as this is where the text names are. Now click OK. You will be returned to Excel, where it will look as though nothing has happened. However, click in cell A7 and type =Ali. You should Excel already knows about this name:

A Name set up in Excel

With Ali selected in the small box at the top, you can press the tab key on your keyboard to have Excel finish the entry. Our simply double click the name. (We have the Name Alice set up elsewhere in our spreadsheet.)

Now complete the formula by typing + Helen:

Two Names set up in Excel

Select Helen from the list and press the enter key on your keyboard. You should see this on your spreadsheet:

An addition using Named Ranges in Excel

What we've done here is to create names, and had Excel associate values with them. This can greatly improve the readability of your spreadsheets.

Delete a Named Range

If you want to delete or edit a name, click the Name Manager item from the from the Defined Names panel on the Formulas ribbon. You should see a dialogue box appear (this may only be in Excel from versions 2016 onwards):

The Name Manager dialogue box

Here, we want to delete the Alice name we set up, so have highlighted that item. Click the Delete button at the top to get rid of the highlighted name.

In the next section, we'll explore Pivot tables in Excel.

<--Back to the Excel Contents Page