Microsoft Excel 2010 - Level 3
© Watsonia Publishing Page 39 Labels And Names
CHAPTER 4 LABELS AND NAMES
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.
INFOCUS
WPL_E834
Microsoft Excel 2010 - Level 3
© Watsonia Publishing Page 40 Labels And Names
UNDERSTANDING LABELS AND NAMES
1
Labels are used to refer to individual cells or
ranges of cells as an alternative to using cell
references. Names are more descriptive than
labels, but they serve the same purpose. For
example, in a formula that calculates profit, the
profit column’s formula may hold the formula
=Income-Expenses, which is more readable than
=E12-E9. Here are some other examples.
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.
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%.
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.
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.
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.
Microsoft Excel 2010 - Level 3
© Watsonia Publishing Page 41 Labels And Names
CREATING NAMES USING TEXT LABELS
Try This Yourself:
Open
File
Before starting this exercise
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
Click on the Formulas tab of
the Ribbon, and click on the
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 Range
UK 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…
To create a name from an existing label:
1. Select the cell or range adjacent to the label
2. Click on the Formulas tab and click on the
Define Name command in the Defined
Names grouping
3. Click on [OK]
Handy to Know…
If there is a gap in the list or row of values,
Excel will only include the values closest to
the label. Therefore, if you have a gap in the
desired range, you should formally assign
the labels to a particular range, or use a
name.
2
3
Excel assumes that there will be many occasions
where the text typed in a worksheet describes the
values to which it is adjacent. If you have typed a
text label next to a continuous list of values, you
can use it as a tag to refer to the values. This can
be done using the Define Name command in
the Defined Names group on the Formulas tab of
the Ribbon.
4
Microsoft Excel 2010 - Level 3
© Watsonia Publishing Page 42 Labels And Names
USING NAMES IN NEW FORMULAS
Try This Yourself:
Same
File
Continue using the previous
file with this exercise, or
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
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…
To use a range name in a new formula:
1. Type the formula in the normal way but type
the range name in lieu of the range address
Handy to Know…
If you attempt to type a name that Excel
doesn’t recognise, the #NAME? error
message will appear in the cell.
Range names are NOT case sensitive so
you can type them in either upper- or lower-
case.
1
2
Once range names have been defined within a
worksheet or workbook they can be used when
typing formulas. Range names are stored within
the workbook so that when a name is typed into a
formula Excel checks its internal listing. If the name
is there, Excel will use it to reference the
appropriate cells and calculate the formula.
3
Microsoft Excel 2010 - Level 3
© Watsonia Publishing Page 43 Labels And Names
APPLYING NAMES TO EXISTING FORMULAS
Try This Yourself:
Same
File
Continue using the previous
file with this exercise, or
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
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
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…
To apply names to existing formulas:
1. Select the range containing the formulas
2. Click on the drop arrow for Define Name
in the Defined Names group and click on
Apply Names
3. Select the names to apply and click on [OK]
Handy to Know…
Even though some of the formulas in the
example above already had names, these
names have been re-applied to the formulas.
The Apply Names command is also a handy
way of refreshing range names.
4
5
Range names are often defined within a
workbook after many of the formulas have
already been entered. It would be a pain to have
to re-create the formulas using newly defined
range names. Fortunately, Excel allows you to
apply defined names to existing formulas
without having to edit or re-type the formulas into
the workbook.
7
Microsoft Excel 2010 - Level 3
© Watsonia Publishing Page 44 Labels And Names
CREATING NAMES USING THE NAME BOX
Try This Yourself:
Same File
Continue using the
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 to create the
name
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
For Your Reference…
To use the Name box to create range names:
1. Select the range in the worksheet to name
2. Click in the Name box
3. Type the desired name and press
Handy to Know…
If you use the Name box and type in a name
that already exists as a range name, Excel
will think you want to navigate to that range
and will position the cell pointer in that range.
2
5
Names are defined to represent individual cells,
ranges of cells, or specific values. Names can be
used in formulas and to re-select cells. A quick
way to create a name for a range is by selecting
the cell(s) and then typing the name in the Name
Box. The Name Box is located below the toolbars
and to the left of the Formula Bar.
6
Worksheet Cell Name
United Kingdom B13 UK_Sales
New Zealand B13 NZL_Sales
Microsoft Excel 2010 - Level 3
© Watsonia Publishing Page 45 Labels And Names
USING NAMES TO SELECT RANGES
Try This Yourself:
Same
File
Continue using the previous
file with this exercise, or open
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
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 &
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…
To use range names to select ranges:
1. Click on the Name box drop arrow
2. Click on the desired range name
Handy to Know…
Single-cell range names are a good way of
navigating to specific locations in large and
complex workbooks. For example, if you
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.
1
2
Names can be used in a variety of ways. They
can be used to refer to a range of cells on which
you want to perform a calculation. You can also
use them to reselect an area easily. This means
that you can define ranges for printing or charting,
for example, and return to them just by selecting
their name. This is ideal when you need to print
specific selections of cells.
5
2
Microsoft Excel 2010 - Level 3
© Watsonia Publishing Page 46 Labels And Names
PASTING NAMES INTO FORMULAS
Try This Yourself:
Same
File
Continue using the previous
file with this exercise, or
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…
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 to accept the
formula
Repeat the above steps to
create the formulas as
follows:
Cell Formula
C10 =UK_Sales
D10 =NZL_Sales
For Your Reference…
To paste names into a formula:
1. Click in the cell where you want the formula
2. Click on the Use in Formula command
and select Paste Names
3. Click on the desired name
Handy to Know…
You can also press to display the Paste
Name dialog box.
Range names are the perfect way of making
more complex formulas easier to comprehend.
You can type range names into formulas as the
formula is created. But what happens if you are
unsure of the range name or its spelling?
Fortunately, Excel provides you with a facility
where range names can be pasted into formulas
directly from a list of valid names.
1
2
3
Microsoft Excel 2010 - Level 3
© Watsonia Publishing Page 47 Labels And Names
CREATING NAMES FOR CONSTANTS
Try This Yourself:
Same
File
Continue using the previous
file with this exercise, or
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
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
constants as you type…
Press to complete the
formula
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…
To create a constant range name:
1. On the Formulas tab, click on Define Name
in the Defined Names group
2. Type the name for the constant
3. Replace the address in Refers to with a
value
Handy to Know…
One advantage of creating a name for a
value is that the value is not easily
accessible, and therefore has some
protection. To display the value in a
worksheet, use the name of the constant in a
formula, and format it to your requirements.
For example, =TaxRate.
1
3
If you have key values in your formulas that are
unlikely to change, you can create a name to
represent a value rather than a cell reference.
This type of value is known as a constant
because it doesn’t need to be changed. Constants
are defined in the same way as range names,
except that they are not directly entered into a cell
in the workbook.
5
Microsoft Excel 2010 - Level 3
© Watsonia Publishing Page 48 Labels And Names
CREATING NAMES FROM A SELECTION
Try This Yourself:
Same
File
Continue using the previous file
with this exercise, or open the
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
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
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…
To create names from a selection:
1. Select the range (including the labels)
2. On the Formula tab of the Ribbon, click on
Create from Selection
3. Specify the location of the labels and click
[OK]
Handy to Know…
The Create from Selection command is
typically used where the labels are in the first
column and values are in the second. You
must ensure that the labels are somewhere
in the selected range.
1
2
There will be times in a workbook when you have
a whole lot of labels that are used to describe the
contents of the adjacent cells. When this occurs
you can select the cells and get Excel to pick up
the labels and assign them as names to the cells
to the right (the most common), the left, to the top,
or to the bottom of the labels.
4
Microsoft Excel 2010 - Level 3
© Watsonia Publishing Page 49 Labels And Names
SCOPING NAMES TO THE WORKSHEET
Try This Yourself:
Open
File
Before starting this exercise
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
Click on the drop arrow for
Scope to see the current
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
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 for
Scope, click on United
Kingdom and click on [OK]
For Your Reference…
To scope a range name to the worksheet:
1. Select the range in the worksheet
2. Click on the Define Name command
3. Click on the drop arrow for Scope and
click on the worksheet name
Handy to Know…
Normally you can’t have duplicate range
names in a workbook. Since worksheet
range names are scoped only to a worksheet
it is possible to have the same range name
duplicated across several of the worksheets.
3
4
Unless otherwise specified, range names are
scoped to the entire workbook. This means
that range names can be accessed from any
worksheet in the workbook. You can also scope
range names to the worksheet. This means that a
range name will only be available to the worksheet
in which it was created.
5
Microsoft Excel 2010 - Level 3
© Watsonia Publishing Page 50 Labels And Names
USING THE NAME MANAGER
Try This Yourself:
Open
File
Before starting this exercise
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
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
to worksheets
Click on [Close]
For Your Reference…
Using the Name Manager:
1. On the Formulas tab, click on Name
Manager in the Defined Names group
2. Use the manager to delete, edit and filter the
range names as appropriate
Handy to Know…
Be careful of the filter because it is a
compound filter. Each time you choose a
filter option it builds from the previous filter to
further refine your search. If you no longer
want to work with a filtered names list, use
the Clear Filter option to clear the filter out.
1
5
Once you’ve got a dozen or more range names in
a workbook, management of those range names
becomes crucial. There will be times when you
want to redefine a name, delete redundant
names, or just see what is available. Fortunately,
Excel’s Name Manager allows you to do all of
these things plus more.
7
Microsoft Excel 2010 - Level 3
© Watsonia Publishing Page 51 Labels And Names
DOCUMENTING RANGE NAMES
Try This Yourself:
Same
File
Continue using the previous
file with this exercise, or
open the file E834 Labels
and Names_11.xlsx...
Click on the Insert
Worksheet tab at the
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
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…
To paste a list of names into the worksheet:
1. On the Formulas tab, click on Use in
Formula in the Defined Names group
2. Click on Paste Names
3. Click on [Paste List]
Handy to Know…
Be careful when creating a range name list
as only those range names scoped to the
workbook will be listed. Range names
scoped to worksheets do not appear in the
list.
1
2
The job’s not done till the paperwork’s finished –
or so the saying goes. Excel allows you to paste
a list of all range names and their locations into
the workbook. This provides an invaluable
documentation tool so that you, and other users,
can see what range names are in the workbook
and what ranges they refer to.
5
Microsoft Excel 2010 - Level 3
© Watsonia Publishing Page 52 Labels And Names
NOTES:
1
4
