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.
Use this workbook for the chapter.
Note: This is the same workbook used in the Ranking chapter.
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:
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:
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.
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:
If you want to use the data in the named cell in a calculation, use the following steps:
This can be helpful because the name of the cell allows you to refer to a cell without even knowing exactly where it is.
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:
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.
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:
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:
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:
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.
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.
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:
When you make edits or remove names of ranges, make sure you are aware of what formulas might be impacted.
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:
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.
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.