|
Name |
► |
Excel provides the ability to
name cells, ranges of cells, formulas or
constants for ease of use. Range names are
particularly useful when referred to in
functions as they mostly shorten the syntax and
adapt when cells are added in the middle of
them. Even where cells are added around
the range names and they do not expand, it is
far easier to alter one range name, which is
referred to in thousands of formulae,
 than
to change the range address in thousands of
formulae. With the arrow under this menu option,
you will get five sub options to choose from,
these are shown left.
-
This
was added as a range of cells, which can then
be referred to in a formula to average that
range. This is the 'AveMyRange' name shown
in the box. When I now type =AveMyRange in a
worksheet cell, it averages, using the
AVERAGE function, the 'myRange' range. A
workbook can contain workbook and worksheet
level names, however no two workbook or
worksheet names can be the same. To set up a
worksheet level name, you need to prefix the
name with the sheet name and an exclamation
mark (also called a 'bang' !). So you can
have a workbook range name of 'Test', a
Sheet2 range name of Sheet2!Test, etc. The
sheet level name only appears in the box,
when the relevant sheet is active and if
this is the case and there is a duplicate
workbook level name, this is not shown. By
default, unless specified, Excel sets up
workbook level names
-
Paste.....
-
Performs two functions from a secondary
dialog (below), giving the ability to
 select
a range name and paste the result (=Test). This works well, when you have many range
names and want to paste one into a formula
or similar. This dialog also allows you to
paste a two column list of all range names
and their respective 'Refers to' entry.
-
Create... -
Allows
you to quickly create range names based on
cells in top row,
 bottom row, right or left
columns of a selection. Which are created
depends on the selection made from the
secondary dialog. (Left). If identical
heading are selected on different sheets,
the first run of this function adds workbook
level names. Subsequent runs on other
sheets, creates worksheet level names. The
dialog left, applied to the data selected
below (A1:E5), would
create the range names shown in cells
A7:B11.
 (This list was coincidentally
created using the 'Paste List' option in the
above section).
-
Apply... -
Allows previously entered formulae to have
their 'hard-coded' ranges replaced with the
new range names. For example, in the data
above, if we wanted to add a total for the
North sales we could in B6 enter
=SUM($B$2:$B$5). If we then set up the
range names as in No.3 above and selected
this menu option, it would match the
reference in the SUM with that for the range
name 'North' and our formula would be
changed to =SUM(North).
 From
the dialog, you can select the range names
you want to apply and can set it to ignore
any difference to relative or absolute
formulas. You should be wary of this if
relative formulas are desirable.
-
Label... -
Excel has the 'ability' to use column and
row labels in formula. This feature is
turned off by default, but can be activated
via
Tools>Options...>Calculation>Accept
labels in formulas. Once this is activated,
you can set up labels to be used in
formulae.
 The
secondary dialog, (left) allows you to
select the labels and then select whether
these should be seen as row or column
labels. You will notice the scepticism I
have around labels in formulae, hence the ''
around 'ability' at the start of this
section.
|
