Use named ranges for absolute references in formulas

In Excel, named ranges come in handy in all sort situations. As Mary Ann Richardson explains, they can even save you some time and effort when you need to create absolute references.

There are a number of ways to design absolute references in formulas. For example, in an order form, you might use a Lookup function to determine the shipping charges from a shipping table, as shown here.

If you use the cell range for the table (F3:G8) in your Lookup function, you have to enter it as $F$3:$G$8 to make it absolute. But if you use a named range to reference the table, the reference is already absolute. Follow these steps:

  1. Select the cell range F3:G8 for the shipping charges table.
  2. Click in the Name text box, type Shipping, and press [Enter].
  3. Click in cell L2 and type =VLOOKUP(K2,Shipping,2)
  4. Copy the formula in L2 to L4.

The results of the formulas are shown below:

Note that using named ranges eliminates having to change the functions if the location of the shipping charges table changes.

Miss an Excel tip?

Check out the Microsoft Excel archive and catch up on other Excel tips. Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Editor's Picks