|Today we'll discuss the use of named
ranges again and see how we can implement them in more interesting
ways and avoid the pitfalls of their conventional use.
We always start using Excel by entering data in Excel like an item name
and its price. Based on the price we calculate a sales or purchase tax.
In case we enter an employee's name and her basic salary then we can
calculate things like leave travel allowance, medical benefits, house
rent allowance, etc as a percentage of the basic salary. Instead of
trying to remember many percentage values we can use named ranges which
are more intuitive. In fact, Excel 2007 displays the named ranges when
you type the first character of the name! Then you just click on it and
avoid any errors. Now let's see the whole process in action.
In similar fashion we can define all the necessary ranges with names.
- Click on formulas in the ribbon
- Select 'Defined Names' and select the sub menu 'define
name...' from 'Defined Names'
- In the New Name dialog box, we enter a name like
'Tax_Electronics' in the 'Name' text-box, define the scope of the name
(Workbook) or Sheet1 depending our our use, write a comment in the
comment-box, if required. Excel automatically refers to the relevant
cell in the 'Refers to' text-box.
But now what happens if someone deletes the value in the worksheet cell
of the named range? This is truly a genuine problem but Excel provides
a good solution.
We can proceed as follows to avoid the accidental deletion problem:
Now what do we do if, let's say, our business sells many different
types of products with different taxes and we find it difficult to
remember the names of the named range? We can paste the list of the
names in the same or different worksheet and refer to it quickly.
- We click on formulas in the ribbon
- Next we select 'define name' from the 'defined names' sub
- In the New Name dialog box we do something slightly
different: Instead of accepting the worksheet cell address we type the
percentage of tax as shown. Rest of the details are as above like the
name, scope and comment. We kind of 'hardwire' the value for future use
- Finally we click 'OK'
- We repeat the process for all the tax values
To do this click on 'Defined Names', select 'Name Manager' and in the
name manager select all the names, press 'ctrl+c' to copy the names,
then click close. Next we click again on 'Defined Names', select 'Use
in Formula', then 'Paste Names...' and 'Paste List' from the 'Paste
Name' dialog box. You can paste the list in the same worksheet or