Microsoft Excel Training Videos Home

Use of named ranges in another interesting way

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.

  • 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.
In similar fashion we can define all the necessary ranges with names.
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:
  • We click on formulas in the ribbon
  • Next we select 'define name' from the 'defined names' sub menu.
  • 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
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.
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 another worksheet.


Use of named ranges