Named ranges as absolute references

In this lesson, we'll review the concept of using a named range as an absolute reference. By default, named ranges behave like absolute references and don't change when you copy formulas. In addition, they make your formulas easier to read.

Let's take a look.

Here we have the same worksheet we looked at in an earlier lesson which tracks the hours worked and gross pay of a small team. Everyone is paid the same hourly wage, so we used an absolute reference to the hourly wage in our formulas.

Let's create a named range for the hourly rate and convert our formulas to use the named range instead of a cell address.

The first step is to create the named range. An easy way to create a named range is to select the range to be named—in this case C6—and then use the name box to name the range.

We'll use the name "hourly rate." Named ranges must begin with a letter and cannot contain spaces or punctuation.

To test our new range we can click elsewhere in the worksheet and use the name box to navigate back to the range. Looks good.

Now we need to update our formulas to use the named range. To do this, we select the reference to C6 in the first formula and point to cell C6 again. Because C6 is now a named range, Excel replaces C6 with the name "hourly rate."

Now, we can just copy down the formula.

Like an absolute cell reference, the named range doesn't change during the copy operation.

As an extra benefit, the named range makes the formula easier to read.