Quick, clean, and to the point

How to apply a named range to an existing formula

Sometimes you might create named ranges after you've already built formulas. In that case, Excel will not automatically update the formulas to use the named ranges. However, there is a way to apply named ranges to formulas.

Let's take a look.

Here we have a table that shows hours worked and gross pay for a small team. Everyone is paid the same hourly wage, so the formula to calculate pay uses an absolute reference to cell C3.

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 type the name and press return. You can test a range by using drop down to select the range.

You can see that the named range is there, but the existing formulas have not changed. They still use an absolute reference to C3.

One way to update the formulas is to edit them manually. Just remove the reference to C3, and add it back again by selecting C3 to pickup the new name. Excel will automatically use named ranges that you select when entering a formula.

You can also insert a name directly into a formula. On the formulas tab of the ribbon, click the Use in formula menu and select the name you want to add.

You can also bring up the paste name dialog box with the keyboard shortcut F3.

This isn't difficult, but it will get tedious if you have longer and more complicated formulas, or when you have lots of named ranges.

Luckily, there's a faster way to apply names to existing formulas. First, I'll undo the changes I just made.

To use this command, first select 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, select the name or name you want 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 another example with more than one named range. Here we have a list of properties for sale. We also have named range for the table called properties, and a named range for the prices called prices.

If we check the summary formulas, we can see that none of them use 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.


Related shortcuts

Dave Bruns

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.