MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2019: Use a Named Range Instead of Absolute References


July 01, 2019 - by Bill Jelen

Excel Use a Named Range Instead of Absolute References. Photo Credit: Katherine Fleitas at Unsplash.com

If you want to avoid using $ in references to make them absolute, you can use named ranges instead. Select the tax rate cell in F1 and click in the name box to the left of the formula bar.

A tax rate of 6.25% is shown in cell F1. If you select F1, the cell address of F1 appears in a Name Box to the left of the formula bar. Click in the Name Box.

Type a name for this cell or range of cells. You cannot use spaces in the name, but TaxRate (or Tax_Rate) will work.


Type a name for the cell. The name can not have spaces, so use TaxRate instead of "Tax Rate".

When you type the formula, use =B2*TaxRate.

After naming the range, any new formulas will use the named range. For example, =B2*TaxRate. In this case, TaxRate is like an absolute reference. As you copy the formula, it will keep pointing at the TaxRate in F1.

Tip

To see all of the named ranges in a worksheet, reduce the zoom to 39% or lower.

Thanks to Myles Arnott, Glen Feechan, Shelley Fishel, Colin Legg, and Nathan Zelany for suggesting this feature. Bob Umlas sent in the tip about seeing names below 39% zoom.

Title Photo: Katherine Fleitas at Unsplash.com


Bill Jelen is the author / co-author of
Microsoft Excel 2019 VBA and Macros

Use this guide to automate virtually any routine Excel task: save yourself hours, days, maybe even weeks. Make Excel do things you thought were impossible, discover macro techniques you won’t find anywhere else, and create automated reports that are amazingly powerful.