Quick, clean, and to the point

How to create a named range

Named ranges are one of the most useful features in Excel. They make your formulas much easier to read and understand, they automatically give you absolute references, and they reduce errors.

Let's take a look at a few ways to create named ranges.

The simplest way to create a named range is to use the name box, which sits to the left of the formula bar. Simply select the cells you want to name, and type a name in the box.

For example, to give the data in this table a name, first select the cells that include the data, then enter a name in the name box.

Now I can easily refer to the name in a formula.

I'll follow the same process with the prices column. Now I can refer to the prices in other formulas using that name.

You can also name ranges more formally using controls in the Formulas tab of the ribbon.

For example, I can name the Beds column by first selecting the data, then clicking Define name. Excel will then open the New Name dialog box, and I can give the range a name, set the scope, provide a description, and finally select the cells that the name should refer to.

Note two things here: first, Excel can often guess the name for the range as long as the data is clearly labeled. Second, if you click into the reference, Excel will show the range on the worksheet.

Excel can also name multiple ranges at the same time using the Create from Selection button.

To illustrate, I'll create names for the data that appears in the summary table. First select the data and the labels, then click Create from Selection. In this case, we need to tell Excel to use values in the left column for names.

When I click OK, we get three new names.

Remember to include the labels that should be used for names in your selection when you use this feature

Note that all names available to a worksheet will appear in the name box.

In addition, you can access all defined names by selecting Name Manager from the Defined Names menu on the Formulas tab of the ribbon.

The name manager allows you to update and delete named ranges. You can also confirm the range that each name defines.

Dave Bruns

Download 200+ Excel Shortcuts

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