Home and Learn: Microsoft Excel Course
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:
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:
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:
When you click on Create from Selection, you'll see the following dialogue box appear:
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:
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:
Select Helen from the list and press the enter key on your keyboard. You should see this on your spreadsheet:
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:
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