How to apply a named range to an existing formula

Abstract

Sometimes you might createnamed rangesafter you’ve already built formulas.

In that case, Excel will not automatically update the formulas to use the named ranges.

However, there are a couple of ways you could apply named ranges to formulas that already exist.

Let’s take a look.

Here we have a table that shows hours worked and gross pay for a small team.

Now lets say you want to convert these formulas to use a named range.

First, I’ll name cell C3 “hourly_rate” using the name box.

Just jot down the name and press Return.

you’re free to test a range by using the drop-down to go for the range.

They still use an absolute reference to cell C3.

One way to update formulas to use named ranges is to edit them manually.

Excel will automatically use named ranges that apply to cells that you select when entering a formula.

Luckily, there’s a faster way to apply names to existing formulas.

First, I’ll undo the changes I just made.

To use this method, first snag the formulas you’d like to update.

Then, under Define Name, on the Formulas tab of the ribbon, choose Apply Names.

When the dialog opens, go for the name that you’d like to apply, and click OK.

In this case, I only need to select hourly_rate.

Now all the formulas have been updated to use the named range called hourly_rate.

Let’s look at one more example.

Here we have a list of properties for sale.

If we check the summary formulas, we can see that they aren’t using these named ranges.

I can easily update these formulas to use named ranges, as before.

First, I’ll update the formula for total listings.

Next, I’ll update the formulas for Maximum and Minimum price.

Now all formulas use the named ranges.