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 used 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 the 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 cell C3. One way to update formulas to use named ranges is to edit them manually. Just remove the reference to C3, and add it back again by selecting C3 to pick up the new name. Excel will automatically use named ranges that apply to cells that you select when entering a formula. 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 method, 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 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. We also have a named range for the table called “properties,” and a named range for the price called “prices.” 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
