The importance of using names in formulas

Make sure you've named all cells and ranges you'll use more than once before writing formulas and functions.

Name all constants, assumptions, grand totals, rates, etc. Use named references in your formulas and functions.

In days of yore... Absolute, Mixed, Relative References, the $ symbol and the F4 key

In olden days, you needed to embed what were called absolute and mixed references into formulas and functions. These looked like:

$A$2 (absolute), $A2 (mixed with column absolute), and A$2 (mixed with row absolute)

You would change the standard (relative) reference (A2) into an absolute or mixed reference by adding the $ symbols or by pressing F4 repeatedly to convert a reference.

Those days are over... named ranges, well executed, will do everything that absolute and mixed references (as well as relative references) used to do, but better. And named ranges open up a wide range of functionality & opportunities that the old ways will never let you achieve.

Lucky Users and Users with History
"Lucky users" (who never learned about absolute & mixed references, F4, or $) follow this rule
If you are going to use a cell (or range of cells) as a reference in more than one formula, name the cell/range first and use the name in your formulas.

You must name variables, constants, and assumptions that you will use more than once. As you get more comfortable with formulas, read up on absolute vs. relative references in your books or on-line help.

"Users with history" (who have learned about absolute and mixed references, F4 or $) follow this rule
Name everything.

You will never again have to worry about mixed and absolute references!

The whole idea of absolute, mixed and relative references created with F4 or by using the dollar sign is almost a thing of the past. The correct use of names will achieve all previous tasks accomplished with those methods, and much, much more.

You can continue to use the dollar sign or the F4 key to change references from relative to absolute to mixed, but you should move towards using names as quickly as possible.

The "tricky" part becomes, as time goes by, that you will actually create names that are defined as relative or mixed references. By default, names are defined as absolute references. Excel is smart enough to use them as mixed references in some scenarios. But there will eventually be a scenario in which you will want to define a name as a mixed reference, or even (rarely, but possibly) a relative reference. At this point in your future in Excel, you will actually need to understand the 'guts' of absolute vs. mixed vs. relative references.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.