Cell Names & Reference



 

Home
Up
YouTube
Longer Videos
VLOOKUP Function Hints
IF Function Hints
Cell Names & Reference
Pancake Bfast: IF & Naming
PMT Function Hints

 


Advantages to using Named ranges (or cells):
bulletNamed Ranges, by default, always are absolute cell references.  That means when it is dragged or copied to another cell, its address stays unchanged.
For example, if the formula B3/Revenue is in cell E3 is copied to cell E4, then the resulting formula will be B4/Revenue.
This is particularly important when using tables in LOOKUP functions.
bulletFormulas are more readable and meaningful.
A formula like =A5*TaxRate is more meaningful particularly when working with a complex worksheet.
bulletUsing the drop down on the top left of the window one can quickly locate  the cells by name.
For example:
bullet When the formula in a cell in dragged to an adjacent cell (one below or above or to the right or left) the references in the formula adjust. 
bullet If a formula in cell D11 is  B3 * C11 dragged down to D12, the formula in D12 will become B4 * C12
If C11 contains the Beginning Balance in month 1 then C12 will reference the beginning balance in month 2, which is correct.  This sort of relative addressing is just what you want and is usually the case.
However, if  cell B3 contains something like Monthly Payment, then the new formula in D12 will not work since B4 does not contain a meaningful number.  One must name cell B3 to insist that Excel treat it Absolutely
.
To name cell(s) when the name(s) is/are next to it (one cell to the left, right, above or below)
bulletselect the cells and its adjacent name (next to it either side or above/below) 
bulletName it by:
bulletExcel 2007:
bulletuse the Formulas Ribbon
bulletCreate from Selection
bullettypically the location of the name is already correctly checked, so all you need to do is click OK or press enter
bulletExcel 2003: Insert menu, Name, Create, typically the location of the name is already correctly checked, so all you need to do is click OK or press enter.
To Name ranges like a table or when the name is not already on the sheet
  1. Select the cells (many times this is a table, remember, if this is a table, select just the table).  
    bulletExcel 2003: Insert menu, Name, Define, type  name (no spaces in the name
    and I recommend it be very short)
    bulletExcel 2007: after highlighting the cell(s), either
    bulletright click and select Name a Range .. or
    bulletFormulas Ribbon, Define
  2. To delete a name Insert menu, Define, Remove
If you name a cell/range before you enter it in a formula, Excel will automatically use the name when you point to the cell.

If you name the cell/range after using it in formulas, you need to Apply the name to the cell where it is used.
Go back to each cell where it is the named cell/range is used in a formula and

bulletExcel 2007: after highlighting the cell(s) that use the cells you have named:
bulletuse the Formulas Ribbon
bulletDefine Names drop down (little triangle to right,
bulletApply Names...
Highlight all the names since Excel will only use what it need
bulletClick OK or press enter
bulletExcel 2003: Insert Menu, Name, Apply. Again the correct name of the reference cell is already selected so all you have to do is click OK or press enter

So if you drag down a formula and the value that appears is really weird, this is probably because one of the cell references should have been an absolute value.  To correct this:

  1. Figure out which number should have not changed when you dragged the formula and assign that number a name.  Use Create if there is a name adjacent to the referred to cell, use Define if not. 
  2. Select the cell that you dragged down, the one that had a good value showing.  After Applying the name, you should able to drag it to adjacent cells.
More Help in the Tutorials

Test yourself

Home ] References ] In the News ] Learning Pyramid ] Photos of Students ] CIS100 Comp. Apps ] The Internet - CIS120 ] Web Page Development ] Tips and Tricks ] Job Search ] Fall '10  Schedule ] Tutorial Sites ] Hints for Printing ] About M.L.G. ] Disclaimer ]Up ] YouTube ] Longer Videos ] VLOOKUP Function Hints ] IF Function Hints ] [ Cell Names & Reference ] Pancake Bfast: IF & Naming ] PMT Function Hints ]

This page was created by Professor Maureen Greenbaum and was last updated on 04/21/09 .

Page Name:  Cell Names & Reference
URL:             http://faculty.ucc.edu/business-greenbaum/C100SS_HnamingAbsolute.htm

Disclaimer:  Information on this site represents the thoughts and opinions expressed by the author and not that of Union County College.  The author takes full responsibility for the information presented.  By using the information contained herein, the user willingly assumes all risks in connection with such use.  Neither the author nor Union County College shall be held liable or responsible for content, errors, and/or omissions in information herein or information contained on any Web Page to which it is linked.  Furthermore, neither the author nor Union County College shall be liable for any special, consequential, or exemplary damages resulting, in whole or in part, from any user's use of, or reliance on, this material or material set forth on any Web Page to which it is linked. 
Copyright:      ã Maureen Greenbaum Sumware in NJ  2001, '02, '03, '04, '05, '06, '07, 08, 09, 10, 11, 12, 13.  All rights reserved. AddThis Social Bookmark Button