Advanced Excel – Range Names
Introduction to Range Names
Range Names is so important when referencing cells in formulas, it can be more convenient to name the cell or range of cells. Naming data ranges adds meaning to formulas because others who are editing can see what the data, that a formula is referencing, is for. The first step to using a data range is to name it. Once a range is named, other cells or formulas the range will absolutely reference that range. This allows you to copy formulas without applying “$” in the range name.
- Open the file named Students_Grades.xlsx.
This workbook has a list of names followed by the points they have received in the class. There is also a column labeled Class Grade. Here only the first student’s Class Grade is calculated. In cell F2, there is a seemingly random number. We want to designate this as the total possible points in the class so that we can use it to calculate the student’s final grades.
- Double click into cell D2.
The cell uses relative referencing to divide the value from column C by the value in cell H2 and formatted to return a percent. If this formula was copied down column D, every cell besides D2 would return an error. We could fix this by using absolute referencing, but it is also fixed by naming the data in H2 and using this name in the formula.
- Press the escape key to move from cell D2 without editing it, and click into H2.
- Hover over the box to the left of the formula bar that displays ‘H2’.
- Click into the name box and type “Total_Points”.
Names must not contain spaces nor start or end with a number; however, they can use underscores as well as capital letters. Here we used the underscore to separate the words, but capital letters at the beginning of words can do the same.
- Press enter to accept the name.
Whenever H2 is the active cell, the name box will display ‘Total_Points’. Clicking the down arrow in the name box and selecting this name will also make F2 the active cell.
- In D2, change ‘H2’ to ‘Total_Points’ and press enter.
When you start typing the cell’s name, it appears in the dropdown formula list as shown in the picture below. You can then use the down key and tab or double click the name to insert it into the formula. The formula now absolute references the named cell without using the ‘$’ in front of the name.
- Copy the formula through the rest of column D.
In addition to naming one cell, a range of cells can be named. This is useful if there are large ranges that need to be manipulated. In this sheet, there is a table displaying the class statistics, we will use range names to calculate these statistics.
- With Students_Grades.xlsx open, select column C and D.
We want to use some functions on these data sets, but we don’t want to select the sets every time we use the data, so we will name the columns.
- Navigate to the Defined Names group in the Formulas tab and click Create from selection.
A dialog box displaying different options for naming the columns is displayed. The ‘Top Row’ option is selected. This means the name of each column will be based on the data in the top cell of that column.
- Click ok.
If you were to click into the name box now and select either of the names we just created, Excel would navigate to those ranges.
- Type “=MIN(Class_Points_Earned)” into cell I2, “=MAX(Class_Points_Earned)”
into cell J2, and “=AVERAGE(Class_Points_Earned)” into cell K2.
Notice that Excel calculated this data from the cells in column C only taking cells with actual data into account.
- Repeat the previous step for cells H3:J3 using the min, max, and average formulas respectively.
A downside to using names is that when there are a lot of names in a workbook, knowing what those names are can become tricky. Now that we have a few different names in the workbook, we can look at how to manage the names. The names can be pasted into the sheet so that anyone using the sheet later knows what range the names are for.
- Click into cell G20.
We want an area on the sheet that has a lot of space for the list to be placed. Our current list is not very long, but even if we had a longer list, this area would have enough space.
- Press F3 on the keyboard.
A dialog box displaying the possible range names are listed. At the bottom left hand side of this box is a botton labeled ‘Paste List’.
- Click ‘Paste List’.
Our list of names is now put in one column and the location or range of cells that the name reference is placed in the column to the right of this list. This is a quick way for anyone who is using the worksheet in the future to see the names in the sheet; however, using the ‘Name Manager’ you can edit the names and add comments about the names.
- Navigate to the Formulas tab and click Name Manager.
A dialog box displaying the names from this worksheet is shown. There are options to edit and delete selected names as well as the option to sort the list using column names and to filter through the list. There is also the option to redefine the cells that are named. Notice in the list of names, there is a section for comments. We want to add a comment to the cell named ‘Total_Points’.
- Choose ‘Total_Points’ and click ‘Edit’.
- In the comments area type: “This is the total points possible in the class. Use it to find students overall class grade.”
- Click ‘Ok’ to save the comment.
The comment is partially showed in the comment section, but for future viewers of the worksheet to see the whole comment, they too would have to choose the name and click
- As practice, define “Letter_Grade_Table” as a name for cells G7:H18.
We will use this name in the next subsection.
Range names can add meaning to formulas and allow large sets of data to be manipulated without having to select the entire set each time it is used.
You can check the previous lessons in advanced excel learning here: