Hello all!! Welcome back to
another session on learning Excel. I hope your Excel learning is so far
fruitful. We have been using named ranges extensively in our posts. So I
thought it’s about time we see in and out of named ranges, though its bit late, better late than never.
Apart from these defined names, Excel also allows naming tables. This feature deserves separate post for itself so we will cover it in one of our future posts.
Navigate to the Named Ranges
You can go to a range quickly by clicking on the down arrow in the Name Box at the upper left corner of your Excel screen (which displays a list of all range names in the worksheet) and clicking on the name of the range to which you want to go. The entire range will be highlighted and the upper left cell of the range will be active.
For practical examples of named ranges, please refer to our previous posts by following the links below.
What Is Named Range
A name is meaningful shorthand
that makes it easier to understand the purpose of a cell reference. As the name
suggests, you can name a range so that instead of using the range addresses,
you can use the name in the formulas. Suppose you have your 12 months sales
figures in cells A1:A12, you can name these cells together as MySales. Apart
from ease of reading, you can use these names in the formulas quite seamlessly.
=SUM(MySales) is more legible than =SUM(A1:A12), isn’t it?
Having said that, you can not
only name a cell or block of cells using this feature but also a standalone
value can be named, so do a custom formula. These names make navigation and
selection easier too. Since
a named range doesn't change when a formula is copied to other cells, it
provides an alternative to using absolute cell references in functions and formulas.
Apart from these defined names, Excel also allows naming tables. This feature deserves separate post for itself so we will cover it in one of our future posts.
How to Name a Range
Typing in Name Box
You can name a range in several
values. Quickest way is typing the name in Name
Box. This is the box that displays the range address; it is visible on the
left hand side of formula bar. Select the cell or block of cells you want to
name, click in Name Box and type the
name you want. Press Enter once you
are done with typing name. Clicking anywhere else apart from pressing Enter will not save the name.
From Define Name Command
In Excel 2007 or later versions,
names can be created by clicking on Formulas
ribbon > Define Name. This action
brings up a dialogue box named New Name which
contains the fields to define a name, scope, comment and a field to mention
what this name refers to. This box appears as shown in below picture. Refers to: value is auto picked which is the address of currently active cell(s).
In Excel 2003 or earlier
versions, names can be created by clicking on Insert menu > Name
> Define. This action opens up Define Name dialogue box which contains
fields to define a name, what this name refers to. This box appears as shown in
below picture.
From Create Name Command
If you want to create a whole set
of names in one go rather than painstakingly enter each one, you can make use
of Excel’s Formulas ribbon > Create from Selection command (in Excel
2007 or later) or Insert menu > Name > Create (in Excel 2003 or earlier).
Before clicking this option, you
need to arrange your data. Once you click above options, a dialogue box opens
up where in you can select if you want pick names from Top row, Left column,
Bottom row or Right column of your selected data. This dialogue box pictures are
as below.
 |
| Excel 2007 or later |
 |
| Excel 2003 |
Note: If cell labels contain
spaces, the spaces will be replaced by underscore characters and if they
contain text that starts with a number they will be preceded by an underscore.
Naming Conventions
- Name can contain up to 255 characters
- Range names cannot start with anything other than a letter, underscore (_)or a backslash (\).
- Name should not include spaces
- Although they can include numbers, they mustn’t start with a number
- A range name cannot be a cell reference, for example: you cannot name a range as A1. This might sound obvious, but because of the dramatic increase in the number of columns in Excel 2007/10 it’s quite easy to accidentally try and enter a cell reference – for example in Excel 2007 SAL1 (shortcut for salary) or PUR1 (shortcut for purchase) are both cell references, though they would be perfectly valid range names in Excel 2003.
- Excel reserves some names for its regular operations and quite obviously you cannot use the reserved names (for example: You cannot use the uppercase and lowercase characters "C", "c", "R", or "r" as a defined name, because they are all used as a shorthand for selecting a row or column for the currently selected cell when you enter them in a Name or Go To text box.)
- Excel names are not distinguished by letter case (means names are not case sensitive). You cannot give SALES and sales in one workbook. Both SALES and sales are same in Excel’s view in this context.
One of the options in New Name dialogue box is Scope. This is a drop-down with options
viz. workbook and one option for each sheet. If you have defined a name, such as MyList,
and its scope is Sheet1, that name is recognized only in Sheet1, but cannot be
recognized in other sheets without mentioning entire path that leads to the
name. For example, if in Sheet2, you want to use name MyList whose scope is Sheet1, you have to mention Sheet1!MyList.
Likewise, if you have defined a name, such as MyList, and its scope is limited to the workbook, that name is
recognized for all worksheets in that workbook, but not for any other workbook.
A name must always be unique within its scope. Excel prevents you from
defining a name that is not unique within its scope. However, you can use the
same name in different scopes. For example, you can define a name, such as
GrossProfit, that is scoped to Sheet1, Sheet2, and Sheet3 in the same. You need
to mention the entire path in that case to make Excel understand which
GrossProfit we are trying to use in the formula.
Refers
To
Although names are most commonly
used to refer to Excel cells or blocks of cells, a name can also refer directly
to a value or an Excel formula. To achieve this, you will need to use the
Define Name dialogue and enter the formula in the “Refers To” box. For example:
define a Name “InterestRate” with Refers To as 10%. You can use this name
in formulas like =A1*InterestRate. This method eliminates the need of maintaining
a cell specifically to keep interest rate.
If you entering a big formula in Refers to, it will be a good idea to
make sure the formula works correctly in a cell before copying it to the Refers
to: box.
How
to Use Names in Formulas
- You can use the defined names just by typing those names in the formulas.
- If you are using Excel 2007 or earlier, when you start first letter of the name, Excel lists down all the available names in drop-down and you can select the one you want.
- You can also insert a defined names from Formulas ribbon > Defined Names > Use In Formula
- You can press F3 while on a sheet or while you are entering a formula. A list of available names is shown in a pop up box i.e. Paste Name from where you can insert the names into your function.
Apply
Name
Excel facilitates you to apply names to existing formulas. Think of a situation; you have a workbook with complex formulas using actual cell references. Later, you got to know about named ranges and created names for all the required ranges. You are now very eager to use them in your existing formulas to simplify them. Do you have to edit all the formulas manually to remove cells references and add names? Fortunately answer is no. You need not do all the formula editing manually, just make use of Excel’s Apply Names command (command is Apply if you are using Excel 2003).
Below are the screenshot of the location of this command.
 |
| Excel 2007 |
Apply Names will be enabled (clickable) only if there is a name created already. Clicking this command opens up a dialogue box as below. (I also clicked on Options button to show all the available options)
Available names are listed in the white space below Apply names: field. I named a range A1:A10 as "abc". Checking Ignore Relative/Absolute field treats A1:A10 (relative reference) and $A$1:$A$10 (absolute reference) same way. Any formula containing these 2 types of reference will be changed to names. For example: =SUM(A1:A10) and =SUM($A$1:$A$10) will be changed to =SUM(abc). Keeping this option unchecked will replace only similar reference what you have given in Refers To: under that name.
Note: It is easy applying names to existing reference but other way around is not possible with existing Excel functionality. So be careful before applying names as you cannot reverse later.
The Name Manager
Alright, you defined a name but now want to change the ‘name’ or ‘refers to’ field value. You can do that by accessing Name Manager.
To review or edit an existing name in Excel 2003 or before you need to follow the Insert > Name > Define. Select the name to be edited from the list. You can then delete it, or change what it refers to in the “Refers To:” box. In Excel 2007/10 Formulas > Defined Names > Name Manager presents a list of names in use together with information about their Scope. Again a name can be selected and then the “Refers To:” changed or the Edit button used or name can be deleted.
Name Manager also provides an option to filter available names. You can see various options available to filter in the screenshot below.
Name Manager also provides an option to filter available names. You can see various options available to filter in the screenshot below.
Navigate to the Named Ranges
You can go to a range quickly by clicking on the down arrow in the Name Box at the upper left corner of your Excel screen (which displays a list of all range names in the worksheet) and clicking on the name of the range to which you want to go. The entire range will be highlighted and the upper left cell of the range will be active.
For practical examples of named ranges, please refer to our previous posts by following the links below.
- Data Validation from other sheets/workbooks
- Data Validation using IF function
- Data Validation using INDIRECT function
- Data Validation - Growing List / Dynamic List
- Data Validation - Cascading Lists (Using CHOOSE function)
- Data Validation - Cascading Lists (Using INDEX & OFFSET function)
No comments:
Post a Comment