Cell Naming & References

Advantages to using Named ranges (or cells):

	Named 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.
	Formulas are more readable and meaningful. A formula like =A5*TaxRate is more meaningful particularly when working with a complex worksheet.
	Using the drop down on the top left of the window one can quickly locate the cells by name.

For example:

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.

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)

select the cells and its adjacent name (next to it either side or above/below)

Name it by:

Excel 2007:

	use the Formulas Ribbon
	Create from Selection
	typically the location of the name is already correctly checked, so all you need to do is click OK or press enter

Excel 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

Select the cells (many times this is a table, remember, if this is a table, select just the table).

Excel 2003: Insert menu, Name, Define, type name (no spaces in the name
and I recommend it be very short)

Excel 2007: after highlighting the cell(s), either

	right click and select Name a Range .. or
	Formulas Ribbon, Define

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

Excel 2007: after highlighting the cell(s) that use the cells you have named:

	use the Formulas Ribbon
	Define Names drop down (little triangle to right,
	Apply Names... Highlight all the names since Excel will only use what it need
	Click OK or press enter

Excel 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:

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.
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.