Named Ranges

In this chapter, we will be looking at naming cells and ranges in Excel. Naming cells and ranges can be very helpful because those cells or ranges can be referred to without reference to their actual cell address. 

Practice Spreadsheet

Use this workbook for the chapter.

Note: This is the same workbook used in the Ranking chapter.

Taking Average Without Names

Shown in Figure 8.1 is a list of employees and their information, including their ages, their college GPA, and their salaries. Their ages are shown in Column D. To calculate the average of their ages, which are not named, use the following steps:

BUS115_image_8.1.png
Figure 8.1
 

  1. Select an empty cell, and type =AVERAGE( into the cell. (See Figure 8.2)
    1. Excel will recognize this as a formula.
  2. Select all of the cells under Age. (See Figure 8.2)
    1. Excel will automatically fill in the cell range in the formula.
  3. Press Enter.
    1. Excel will calculate the average age of the employees.
BUS115_image_8.2.jpg
Figure 8.2
 

Naming a Cell

Notice when we did that, the average formula had to specify the range of cells needed to calculate the average. See Figure 8.2. Instead of doing this, we can name a cell or range. For example, look at Alice’s age in Figure 8.1. To name this cell, use the following steps:

  1. Select the cell you want to name.
    1. In the top left of the formula bar, the cell’s name comes up as D3. (See Figure 8.3)
  2. Select the name, and type alice_age. (See Figure 8.3)
    BUS115_image_8.3.png
    Figure 8.3
     
  3. Press Enter to save the name.

You can’t use spaces or special characters in a named range. It’s a good idea to use an underscore in place of a space when you name a cell or range. 

Referencing a Named Cell

Now, when you select a new cell, you can reference this named cell by typing the name rather than selecting the cell manually. To do this, use the following steps:

  1. Select a new cell, and type the equals sign =.
    1. Excel will automatically recognize this as a formula.
  2. Start to type alice_age.
    1. Excel will bring up a list of suggestions, including the name of this cell. (See Figure 8.4)
      BUS115_image_8.4.png
      Figure 8.4
       
  3. Press Tab to allow Excel to autofill alice_age into this cell. 
  4. Press Enter.
    1. Excel will input the data found in the cell named alice_age, which in this case is 49

If you want to use the data in the named cell in a calculation, use the following steps:

  1. Select a cell, and type the equals sign =.
  2. Select the cell named alice_age
    1. Excel will automatically recognize that cell as “alice_age.” (See Figure 8.5)
  3. Type *5. 
  4. Press Enter
    1. Excel will calculate 49 times 5.
      BUS115_image_8.5.png
       Figure 8.5
       

This can be helpful because the name of the cell allows you to refer to a cell without even knowing exactly where it is.

Naming a Range

You can also name an entire array of cells, or in other words, an entire range. To practice naming a range, use the following steps:

  1. Select all of the data under Age. (See Figure 8.6)
  2. Select name in the top left of the formula bar. (See Figure 8.6)
  3. Type age. (See Figure 8.6)
  4. Press Enter to save the name.
BUS115_image_8.6.png
Figure 8.6

Use the same process for the data in the other categories. The data under College GPA should be named gpa, the data under Salary should be named salary, the data under Employee should be named names, and the data under Position should be named job.

Calculating Average of a Named Range

Once a range of numbers has been named, it can be referenced by name rather than having to select the entire set of data every time. To do this to the set of data under Age, use the following steps:

  1. Select a new cell, and type =AVERAGE(.
    1. Excel will recognize this as a formula.
  2. Instead of selecting all of the data under Age, simply type age. (See Figure 8.7
  3. Make sure to close the parentheses. (See Figure 8.7)
  4. Press Enter
    1. Excel will calculate the average age of the employees.
      BUS115_image_8.7.png
      Figure 8.7
       

Referencing Named Ranges Across Worksheets

Naming cells and ranges allow you to do formulas much more quickly. It is especially helpful with calculations that exist on other worksheets. To do this, use the following steps:

  1. Add a new worksheet.
  2. Select a cell, and type =AVERAGE(gpa). (See Figure 8.8)
    BUS115_image_8.8.png
    Figure 8.8
     
    1. Excel will recognize the name of the range.
    2. Make sure to close the parentheses. 
  3. Press Enter.
    1. Excel will calculate the average GPA of the employees.

When you have a named range, you no longer have to flip back to a different worksheet to reference that range; all you need to do is type the name into the formula.

This will also work for totals. To calculate the total of the employees’ salaries across worksheets, use the following steps:

  1. Select an empty cell in the new worksheet.
  2. Type =SUM(salary). (See Figure 8.9)
    BUS115_image_8.9.png
     Figure 8.9
     
    1. Excel will recognize the name of the range.
    2. Make sure to close the parentheses.
  3. Press Enter
    1. Excel will calculate the total of the employees’ salaries.

Once you’ve created these named ranges, you can always see which ones they are by selecting the drop-down list in the top left of the formula bar, the same place that allows you to name the range. (See Figure 8.10)

If you select any of the names on this list, it will take you to the location in the workbook of that named range. 

BUS115_image_8.10.png
Figure 8.10
 

Renaming Named Ranges

Let’s say that we wanted to rename alice_age so that it was simply named alice. This can be done using the same steps as when we first renamed the cell. But once the cell is renamed again, both versions of the name show up in the drop-down list. See Figure 8.11. Even though the cell is renamed, the use of either of the names in a formula will still work. There are now two named ranges that refer to that same cell.

BUS115_image_8.11.png
Figure 8.11
 

Removing Range Names

If you don’t want duplicate names, you can remove or delete a name that you no longer need. To remove alice_age, use the following steps:

BUS115_image_8.12.jpg
Figure 8.12
  1. Select the Formulas tab on the toolbar. (See Figure 8.12)
  2. Select Name Manager. (See Figure 8.12)
    1. This will bring up a dialogue box that gives you the details of every named range that exists in the sheet. (See Figure 8.13
    2. Selecting a name will bring up its value if it has one, which cell it refers to, and its scope.
  3. Select alice_age. (See Figure 8.13)
    BUS115_image_8.13.png
    Figure 8.13
     
  4. Select Delete. (See Figure 8.14)
  5. When the pop-up message appears, select OK. (See Figure 8.14)
    1. When you select Done, any cells that had a formula that used the deleted name will now have errors in them, because Excel no longer recognizes that name.
BUS115_image_8.14.png
Figure 8.14
 

When you make edits or remove names of ranges, make sure you are aware of what formulas might be impacted. 

Changing Ranges

Sometimes if you add more data after naming a range, you will want to add this data to the named range. Let’s say that we added a new employee to the data. To add another cell to the range named age, use the following steps:

  1. Select the Formulas tab on the toolbar. (See Figure 8.12)
  2. Select Name Manager. (See Figure 8.12)
    1. This will bring up a dialogue box that gives you the details of every named range that exists in the sheet. (See Figure 8.13)
  3. Select age
    1. Information about what this named range refers to will show up. (See Figure 8.15)
      BUS115_image_8.15.png
      Figure 8.15
       
  4. In the box under Refers to:, change 17 to 18. (See Figure 8.16)
  5. Select the green checkmark. (See Figure 8.16)
    1. The range will be updated.
BUS115_image_8.16.png
Figure 8.16
 

This dialogue box allows you to make all those types of edits in one place across all named ranges in your workbook.

Named ranges can be very useful, especially when your spreadsheets are large and have lots of information with lots of different sheets, making it difficult to remember where the cells you want to reference are located. It can also make creating formulas a lot easier, simplifying the problem-solving process by seeing exactly what you’re referring to in the formula using a named range, instead of having to reference every unique cell or range in each formula. 

Supplemental Resource

This content is provided to you freely by BYU-I Books.

Access it online or download it at https://books.byui.edu/bus_115_business_app/named_ranges.