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 the 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 that name in a formula. I’ll follow the same process with the Price column. Now I can refer to the prices in other formulas by using that name. You can also name ranges more formally using the controls that appear in the Formulas tab of the ribbon. For example, I can name the Beds column by first selecting the data and then clicking Define name. Excel will then open the New Name dialog box, where 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 you 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 Names from Selection. In this case, we need to tell Excel to use values in the left column for the names. When I click OK, Excel creates three names. Remember to include the labels you’d like to use for names in your selection when you use this feature. Note that all names that are available to a worksheet will appear in the name box. In addition, you can access all defined names by selecting Name Manager on the Formulas tab of the ribbon. The Name Manager allows you to update and delete named ranges. You can also confirm the cell references that each name defines.
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.
