Microsoft Excel 2010 - Level 3
CHAPTER 4 LABELS AND NAMES
INFOCUS
WPL_E834
Labels and names are used to identify cells and ranges using a tag
that is more meaningful than ordinary cell references such as B6 or
C5:D11. These names and labels can be used in formulas and in
links between worksheets and workbooks to make the formulas
easier to understand. They can also be used to identify specific print
areas or to help you locate a specific place in a spreadsheet.
If you haven’t worked on your workbook for a while, or have to work
with someone else’s, the clever use of labels and names will help
you find your way around the workbook and understand the
formulas.
In this session you will:
gain an understanding of how labels and names are used
learn how to create range names using existing text
labels
learn how to use range names in new formulas
learn how to apply names to existing formulas
learn how to create range names using the Name box
learn how to use range names to select and navigate to
ranges
learn how to paste range names into formulas
learn how to create constants in Excel
learn how to create names from a selection in the
worksheet
learn how to create range names that are scoped to a
specific worksheet
learn how to use the range Name Manager
learn how to paste a list of range names into the
worksheet.
© Watsonia Publishing Page 39 Labels And Names
Microsoft Excel 2010 - Level 3
UNDERSTANDING LABELS AND NAMES
Labels are used to refer to individual cells or example, in a formula that calculates profit, the
ranges of cells as an alternative to using cell profit column’s formula may hold the formula
references. Names are more descriptive than =Income-Expenses, which is more readable than
labels, but they serve the same purpose. For =E12-E9. Here are some other examples.
1 Labels
The term label usually refers to text that you have typed in a cell. If the text appears next to a continuous
list of values, Excel sees the label as a ‘tag’ that represents that range of values. The label can then be
used in formulas instead of direct cell references. If you have used numbers as ‘labels’, such as the year
2010, Excel allows you to define these as labels too. Note that labels can be used only within the same
worksheet.
Here the label Wages, typed originally in
A5, is being used in the formula in F5 to
refer to the range B5:E5.
Similarly, the label Qtr_1 could be used to
refer to the cells in the range B5:B8.
2 Names
If you want to create a tag that refers to a range of cells holding values and text, or that you can use in
formulas on other worksheets, you need to create a Name.
Names are like labels except that the name has to be specified against a particular range, and does not
usually appear on the spreadsheet. Names can be used to refer to cells in other worksheets, in other
workbooks, and can even be used to represent a fixed value rather than a range of cells. For example, if
you need to use a constant value in your calculations, but don’t want the value to appear in the
worksheet in case it is accidentally changed, you can define a name and assign it a value. For example,
GST could represent the value 10%.
The name ExpenseTotals is being created
in the Name box to refer to the non-
contiguous ranges A5:A8 and F5:F8.
This name can be used to re-select the
ranges later, or to refer to the ranges from
another part of the workbook.
Need to Know…
There are two important rules to follow when creating Names:
1. You can only use letters of the alphabet, numbers or the underscore character ( _ ) in names.
Spaces and other special characters such as &, * or % are not allowed.
2. Names must not begin with a number. Use a letter or an underscore as the first character of your
name, for example, Year2008 or _2010.
© Watsonia Publishing Page 40 Labels And Names
Microsoft Excel 2010 - Level 3
CREATING NAMES USING TEXT LABELS
Excel assumes that there will be many occasions can use it as a tag to refer to the values. This can
where the text typed in a worksheet describes the be done using the Define Name command in
values to which it is adjacent. If you have typed a the Defined Names group on the Formulas tab of
text label next to a continuous list of values, you the Ribbon.
Try This Yourself:
Before starting this exercise
Open
File
you MUST open the file E834
Labels and Names_1.xlsx...
Click on the Past 5 years
worksheet tab and spend a
few moments studying the
data
Select the range B6:B10
2
Click on the Formulas tab of
the Ribbon, and click on the 3
Define Name command in
the Defined Names grouping
to see the New Name dialog
box
Notice how Excel has used the
text label from B5 as the
assumed name for the range…
Click on [OK] to accept the
default settings
The new name will appear in
the Name box…
Repeat the above steps and
create the following names:
Name
UK
Range
C6:C10
New_Zealand D6:D10
Year_1 B6:D6
Year_2 B7:D7
Year_3 B8:D8
Year_4 B9:D9
Year_5 B10:D10
For Your Reference… Handy to Know…
To create a name from an existing label: If there is a gap in the list or row of values,
1. Select the cell or range adjacent to the label Excel will only include the values closest to
the label. Therefore, if you have a gap in the
2. Click on the Formulas tab and click on the
desired range, you should formally assign
Define Name command in the Defined the labels to a particular range, or use a
Names grouping name.
3. Click on [OK]
© Watsonia Publishing Page 41 Labels And Names
Microsoft Excel 2010 - Level 3
USING NAMES IN NEW FORMULAS
Once range names have been defined within a formula Excel checks its internal listing. If the name
worksheet or workbook they can be used when is there, Excel will use it to reference the
typing formulas. Range names are stored within appropriate cells and calculate the formula.
the workbook so that when a name is typed into a
1
Try This Yourself:
Continue using the previous
Same
file with this exercise, or
File
open the file E834 Labels
and Names_2.xlsx...
Click on cell B12
Type =sum(Australia) and
press
If you have typed the name
correctly the formula will
now display a calculated 2
result as though you had
typed the formula in the
normal way using cell
references. If your formula
contains an invalid name,
then the error #NAME? will
appear…
Type the following formulas:
Cell Formula
C12 =sum(UK)
D12 =sum(New_Zealand)
For Your Reference… Handy to Know…
To use a range name in a new formula: If you attempt to type a name that Excel
1. Type the formula in the normal way but type doesn’t recognise, the #NAME? error
the range name in lieu of the range address message will appear in the cell.
Range names are NOT case sensitive so
you can type them in either upper- or lower-
case.
© Watsonia Publishing Page 42 Labels And Names
Microsoft Excel 2010 - Level 3
APPLYING NAMES TO EXISTING FORMULAS
Range names are often defined within a range names. Fortunately, Excel allows you to
workbook after many of the formulas have apply defined names to existing formulas
already been entered. It would be a pain to have without having to edit or re-type the formulas into
to re-create the formulas using newly defined the workbook.
4
Try This Yourself:
Continue using the previous
Same
file with this exercise, or
File
open the file E834 Labels
and Names_3.xlsx...
Click on E6, then type
=SUM(B6:D6) and press
Click on E6, and drag the fill
handle at the bottom right, to
E12
Click on E11 and press
to delete the superfluous
5
formula
Select the range A5:E12
Click on the Formulas tab of
the Ribbon, click on the
drop arrow for Define Name
in the Defined Names
group and click on Apply
Names
Click on all of the names
until they are selected, then
7
click on [OK] to apply the
selected names to formulas
in the selected range
Click on the formulas in the
worksheet and notice how
names have been inserted in
lieu of cell addresses
For Your Reference… Handy to Know…
To apply names to existing formulas: Even though some of the formulas in the
1. Select the range containing the formulas example above already had names, these
names have been re-applied to the formulas.
2. Click on the drop arrow for Define Name The Apply Names command is also a handy
in the Defined Names group and click on way of refreshing range names.
Apply Names
3. Select the names to apply and click on [OK]
© Watsonia Publishing Page 43 Labels And Names
Microsoft Excel 2010 - Level 3
CREATING NAMES USING THE NAME BOX
Names are defined to represent individual cells, the cell(s) and then typing the name in the Name
ranges of cells, or specific values. Names can be Box. The Name Box is located below the toolbars
used in formulas and to re-select cells. A quick and to the left of the Formula Bar.
way to create a name for a range is by selecting
2
Try This Yourself:
Continue using the
Same File
previous file with this
exercise, or open the file
E834 Labels and
Names_4.xlsx...
Click on A2, hold down
and click on E12 to
select the range A2:E12
Click in the Name box
(just above column A)
and type RptPastYears
Press
name
to create the
5
Click on the Australia
worksheet tab and click
on B13
Click in the Name box,
type AUS_Sales and
press
Repeat steps 4 and 5
and create the names as
shown
6 Worksheet Cell Name
United Kingdom B13 UK_Sales
New Zealand B13 NZL_Sales
For Your Reference… Handy to Know…
To use the Name box to create range names: If you use the Name box and type in a name
1. Select the range in the worksheet to name that already exists as a range name, Excel
will think you want to navigate to that range
2. Click in the Name box
and will position the cell pointer in that range.
3. Type the desired name and press
© Watsonia Publishing Page 44 Labels And Names
Microsoft Excel 2010 - Level 3
USING NAMES TO SELECT RANGES
Names can be used in a variety of ways. They that you can define ranges for printing or charting,
can be used to refer to a range of cells on which for example, and return to them just by selecting
you want to perform a calculation. You can also their name. This is ideal when you need to print
use them to reselect an area easily. This means specific selections of cells.
1
Try This Yourself:
Continue using the previous
Same
file with this exercise, or open
File
the file E834 Labels and
Names_5.xlsx...
Click on the drop arrow of
the Name box to see a list of
names that have been created
Click on RptPastYears to see
select and move to this range
This selected range could now
be used for a variety of tasks
2
including copying, printing,
charting, etc…
Click on the Name box drop
arrow and click on
UK_Sales
Click on the Name box drop
arrow again and click on
Australia
You can also use the Go To
box and range names to
navigate…
Click on the Home tab of the
Ribbon and click on Find &
5
2
Select in the Editing
group, then click on Go To to
display the Go To dialog box
Click on New_Zealand and
click on [OK]
For Your Reference… Handy to Know…
To use range names to select ranges: Single-cell range names are a good way of
1. Click on the Name box drop arrow navigating to specific locations in large and
complex workbooks. For example, if you
2. Click on the desired range name name the top cell of your budget data
BudgetData you can use the Name box
drop arrow or Go To to navigate to that
specific location.
© Watsonia Publishing Page 45 Labels And Names
Microsoft Excel 2010 - Level 3
PASTING NAMES INTO FORMULAS
Range names are the perfect way of making unsure of the range name or its spelling?
more complex formulas easier to comprehend. Fortunately, Excel provides you with a facility
You can type range names into formulas as the where range names can be pasted into formulas
formula is created. But what happens if you are directly from a list of valid names.
1
Try This Yourself:
Continue using the previous
Same
file with this exercise, or
File
open the file E834 Labels
and Names_6.xlsx...
On the Past 5 years
worksheet tab, click on B10
This is the last year of total
sales for Australia and
should be based on the
total sales in the Australia
worksheet…
2
Click on the Formulas tab
of the Ribbon, click on Use
in Formula in the
Defined Names group,
then select Paste Names to
display the Paste Name
dialog box
Click on AUS_Sales and
click on [OK] to have Excel
build a formula for you
Press
formula
to accept the
Repeat the above steps to
create the formulas as
3
follows:
Cell Formula
C10 =UK_Sales
D10 =NZL_Sales
For Your Reference… Handy to Know…
To paste names into a formula: You can also press to display the Paste
1. Click in the cell where you want the formula Name dialog box.
2. Click on the Use in Formula command
and select Paste Names
3. Click on the desired name
© Watsonia Publishing Page 46 Labels And Names
Microsoft Excel 2010 - Level 3
CREATING NAMES FOR CONSTANTS
If you have key values in your formulas that are because it doesn’t need to be changed. Constants
unlikely to change, you can create a name to are defined in the same way as range names,
represent a value rather than a cell reference. except that they are not directly entered into a cell
This type of value is known as a constant in the workbook.
1
Try This Yourself:
Continue using the previous
Same
file with this exercise, or
File
open the file E834 Labels
and Names_7.xlsx...
On the Past 5 years
worksheet, click on F5 and
type Tax, then press
Click on the Formulas tab
on the Ribbon, then click
on Define Name in the 3
Defined Names group, to
see the New Name dialog
box
Type TaxRate in Name and
press until Refers to is
selected
Type 10% and click on
[OK]
In F6, type =E6*TaxRate
Notice how the name
appears in a list of
5
constants as you type…
Press
formula
to complete the
Click on F6 again, move the
mouse pointer to the fill
handle and drag down to
F10
Each formula in the filled
cells will use the constant
For Your Reference… Handy to Know…
To create a constant range name: One advantage of creating a name for a
1. On the Formulas tab, click on Define Name value is that the value is not easily
in the Defined Names group accessible, and therefore has some
protection. To display the value in a
2. Type the name for the constant worksheet, use the name of the constant in a
3. Replace the address in Refers to with a formula, and format it to your requirements.
value For example, =TaxRate.
© Watsonia Publishing Page 47 Labels And Names
Microsoft Excel 2010 - Level 3
CREATING NAMES FROM A SELECTION
There will be times in a workbook when you have the labels and assign them as names to the cells
a whole lot of labels that are used to describe the to the right (the most common), the left, to the top,
contents of the adjacent cells. When this occurs or to the bottom of the labels.
you can select the cells and get Excel to pick up
1
Try This Yourself:
Continue using the previous file
Same
with this exercise, or open the
File
file E834 Labels and
Names_8.xlsx...
Click on the Australia worksheet
tab and select the range A7:B11
The range must include a series
of labels and this one does, in
column A…
On the Formulas tab of the
Ribbon, click on Create from
2
Selection in the Defined
Names group, to see the Create
Names from Selection dialog
box
Ensure that Left column is
ticked (because that is where the
labels are) and click on [OK]
Click on the Name box drop
arrow to see the new names
4
Repeat the above steps for the
following ranges:
Worksheet Range
United Kingdom A7:B11
New Zealand A7:B11
Click on the Name box drop
arrow and click on
Edgecliff_Cellars to navigate to
that range
For Your Reference… Handy to Know…
To create names from a selection: The Create from Selection command is
1. Select the range (including the labels) typically used where the labels are in the first
column and values are in the second. You
2. On the Formula tab of the Ribbon, click on
must ensure that the labels are somewhere
Create from Selection in the selected range.
3. Specify the location of the labels and click
[OK]
© Watsonia Publishing Page 48 Labels And Names
Microsoft Excel 2010 - Level 3
SCOPING NAMES TO THE WORKSHEET
Unless otherwise specified, range names are range names to the worksheet. This means that a
scoped to the entire workbook. This means range name will only be available to the worksheet
that range names can be accessed from any in which it was created.
worksheet in the workbook. You can also scope
3
Try This Yourself:
Before starting this exercise
Open
File
you MUST open the file E834
Labels and Names_9.xlsx...
On the Australia worksheet
tab, click on cell B5
Click on the Formulas tab of
the Ribbon, then click on
Define Name in the
Defined Names group to see
the New Name dialog box 4
Click on the drop arrow
Scope to see the current
for
worksheet names
Click on Australia and click on
[OK]
The new name, Qtr_1, appears
in the Name box…
Click on the United Kingdom
worksheet tab, then click on the
Name box drop arrow
No names will appear because
the only name we’ve created 5
has been scoped to the
Australia worksheet…
Click on cell B5, then click on
Define Name to see the
New Name dialog box
Click on the drop arrow
Scope, click on United
for
Kingdom and click on [OK]
For Your Reference… Handy to Know…
To scope a range name to the worksheet: Normally you can’t have duplicate range
1. Select the range in the worksheet names in a workbook. Since worksheet
range names are scoped only to a worksheet
2. Click on the Define Name command it is possible to have the same range name
3. Click on the drop arrow for Scope and duplicated across several of the worksheets.
click on the worksheet name
© Watsonia Publishing Page 49 Labels And Names
Microsoft Excel 2010 - Level 3
USING THE NAME MANAGER
Once you’ve got a dozen or more range names in names, or just see what is available. Fortunately,
a workbook, management of those range names Excel’s Name Manager allows you to do all of
becomes crucial. There will be times when you these things plus more.
want to redefine a name, delete redundant
1
Try This Yourself:
Before starting this exercise
Open
File
you MUST open the file E834
Labels and Names_10.xlsx...
Click on the Formulas tab of
the Ribbon, then click on
Name Manager in the
Defined Names group, to see
the Name Manager dialog box
Spend a few moments
scrolling through the list to see
what is there
Click on Dodgy_Bros in
Name and click on [Delete]
Click on [OK] to confirm the
deletion 5
Click on Fabulous_Forties in
Name, then click on [Edit] to
display the Edit Name dialog
box
Type Fabulous_Forty in
Name and click on [OK] to
change the name of the range
name
Click on [Filter], then click on
Names Scoped to Worksheet
to see only the names scoped 7
to worksheets
Click on [Close]
For Your Reference… Handy to Know…
Using the Name Manager: Be careful of the filter because it is a
1. On the Formulas tab, click on Name compound filter. Each time you choose a
Manager in the Defined Names group filter option it builds from the previous filter to
further refine your search. If you no longer
2. Use the manager to delete, edit and filter the want to work with a filtered names list, use
range names as appropriate the Clear Filter option to clear the filter out.
© Watsonia Publishing Page 50 Labels And Names
Microsoft Excel 2010 - Level 3
DOCUMENTING RANGE NAMES
The job’s not done till the paperwork’s finished – documentation tool so that you, and other users,
or so the saying goes. Excel allows you to paste can see what range names are in the workbook
a list of all range names and their locations into and what ranges they refer to.
the workbook. This provides an invaluable
1
Try This Yourself:
Continue using the previous
Same
file with this exercise, or
File
open the file E834 Labels
and Names_11.xlsx...
Click on the Insert
Worksheet tab at the 2
bottom of the workbook, to
create a new worksheet
On the Formulas tab of the
Ribbon, click on Use in
Formula in the
Defined Names group, then
click on Paste Names to
see the Paste Name dialog
box
Click on [Paste List] to
paste a list of range names
5
into the worksheet
Double click on the border
between the headings for
columns A and B to best fit
column A
Click elsewhere in the
worksheet to deselect the
cells and see the changes
more clearly
For Your Reference… Handy to Know…
To paste a list of names into the worksheet: Be careful when creating a range name list
1. On the Formulas tab, click on Use in as only those range names scoped to the
Formula in the Defined Names group workbook will be listed. Range names
scoped to worksheets do not appear in the
2. Click on Paste Names list.
3. Click on [Paste List]
© Watsonia Publishing Page 51 Labels And Names
Microsoft Excel 2010 - Level 3
NOTES:
© Watsonia Publishing Page 52 Labels And Names
