Why Use Named Ranges?

Using named ranges makes your formulas easier to read and write. Consider a reference to $G$4 vs. CityTax. The reference to CityTax is much simpler and more meaningful than a reference to the grid such as $G$4.

Named ranges appear as you write your formulas, making them faster to type and eliminating the need to click between sheets and scroll on a sheet to locate the cell(s) you want to reference.

Named ranges have always had a special power, and this is to link Excel features that cannot work together directly. You will see many examples of this throughout the book.

Define a Named Range with the Name Box

The quickest way to define a named range is to use the Name Box . Let’s see how to do this and then use that name in a formula.

Cell F2 is used as the threshold value in the logical test, and cell F4 stores the discount percentage that is used for the value if true behavior.

You can check that the names have been created successfully by clicking the drop-down list arrow on the Name Box . Figure 3-3 shows both names listed along with an existing [lstProducts] name.

If you click a name in the list, it will take you to that named range.

Using Named Ranges in Formulas

Let’s now replace the cell references in the formula with references to the named ranges.

The easiest way to do this is to type the names when creating or editing a formula. Figure 3-4 shows our two named ranges being entered into the formula.

As you type the name, a list appears that shows names of functions and ranges. You can identify a named range by the icon to the left of the name.

You can also insert a named range using a list on the Ribbon. This is especially helpful if you are unsure on the name that was assigned.

From within the formula where you want the name inserted

1. 1.

   Click Formulas ➤ Use in Formula.

    
2. 2.

   Click the name in the list (Figure 3-5).

    

Another alternative is to press the keyboard shortcut F3. This opens the Paste Name window (Figure 3-6). Select the name to paste and click OK.

This window can also be opened by clicking Paste Names from the Use in Formula list on the Ribbon.

Rules for Defining Names

If you try to use a name that already exists, you are taken to that name. Quickly jumping to a range or macro using this method is a cool feature of the Name Box.

If you enter a name using an invalid character, you will see the message shown in Figure 3-7.

Tips for Defining Names

Benefits to using prefixes include that you can easily identify the type of name by its prefix, you can use the same name more than once because the prefix is different, and Excel will group them together, making it quick and easy to enter them (Figure 3-8).

You want your names to be quick to find and easy to understand. These are only ideas, and you should do what works for you.

Named Ranges with Multiple Cells

Named ranges are not restricted to single cell ranges. A named range can be multiple contiguous cells, a selection of non-contiguous cells, or even an entire row or column.

Continuing with the previous example, let’s name the range of total values from range B2:B8:

1. 1.

   Select range B2:B8.

    
2. 2.

   Type “rngTotal” in the Name Box (Figure 3-9).

    
3. 3.

   Press Enter.

    

Figure 3-10 shows the completed formula. It has spilled to the other cells in range C2:C8, and this is identified by the blue border.

This is a dynamic array, and this behavior will occur if you are using an Excel 2021, Excel for Microsoft 365 (Windows and Mac), or the Excel for the Web version. Dynamic arrays are covered in detail in Chapter 10.

If you are using a non-dynamic array–enabled version of Excel, you will need to fill the formula down the range. It will not spill.

The IF function expects a single value, but multiple values were provided to it when using the [rngTotal] name, so the formula assumed the use of the value in the same row on the grid. This behavior is known as implicit intersection.

As an advanced Excel formula user, it is important to understand this behavior so that we can recognize it on spreadsheets. And there is a case that the formula is more meaningful when using the named range.

However, despite demonstrating this technique, it is not one that I would encourage. A better approach would be to format the range as a table and use the qualities of the table for the formula. Tables are discussed in the next chapter.

Named Range for a Drop-Down List

A common scenario for defining a multicell named range is when using it for the source of a drop-down list .

For this example, we have a list of products that has been named [lstProducts] (Figure 3-11). This can be found on the [Lists] sheet of the provided [named-ranges.xlsx] workbook.

On the [Sales] worksheet, there is a SUMIFS function (covered later in the book) in cell F3 returning the total sales for the product entered in cell E3 (Figure 3-12).

We will create a drop-down list in cell E3 to ensure that the data entered is accurate. We will use the [lstProducts] defined name for the source of the list.

The drop-down list can then be used to easily select the required product (Figure 3-14).

Define a Dynamic Named Range

The products’ drop-down list works great, but it is not dynamic. If products are added to the end or removed from the list on the [Lists] sheet, the [rngProducts] name will not automatically adjust in size.

Sure, there are clever techniques around this issue, such as inserting cells in the middle of the range. This forces the named range to adjust its height.

However, this is an advanced Excel formulas book, and it would be great if the named range automatically changed height when products are added or removed.

To do this, we will use the brilliant INDEX function as the source for the named range.

You do not get any assistance when entering a formula into an Excel window, so it is good practice to enter it into a cell first and then copy it into the required window.

The following formula has been entered into cell C2 (Figure 3-15). This uses two INDEX functions; one on either side of the range operator (:). These return the first and last cells in the list and together create the range.

=INDEX(Lists!$A:$A,2):INDEX(Lists!$A:$A,COUNTA(Lists!$A:$A))

The first INDEX function returns the address of the cell in row 2 of column A. Row 2 is entered as a constant value. In the second INDEX function, the COUNTA function is used to count the number of non-blank cells in column A. In this example, it successfully returns the address of the final non-blank cell.

I am using an Excel for Microsoft 365 version, so the values from the range are returned and spilled to the grid. You will not see this if you are using an older version of Excel; however, the formula works, so proceed with the next steps:

1. 1.

   Copy the formula from the Formula Bar.

    
2. 2.

   Click Formulas ➤ Name Manager.

    
3. 3.

   Select the lstProducts name and click Edit (Figure 3-16).

    

1. 4.

   Delete the contents of the Refers to field and paste in the formula (Figure 3-17). Click OK.

    

When new products are added to the end of the list, they are automatically included in the drop-down list.

Figure 3-18 shows “Orange Juice” added to the end of the list. This list can then be sorted in A-Z order.

Figure 3-19 shows “Orange Juice” in the drop-down list after it was added to the source list on the [Lists] sheet, and the products were sorted in A-Z order.

Define Names with Create from Selection

Another way to create named ranges is to use the Create from Selection option. This is great for creating multiple named ranges at once.

In this example, we have the data shown in Figure 3-20. We have three levels that each apply a different discount.

We will name the cells in ranges G3, G4, and G5 using the values in the cells to their left (F3, F4, and F5). Then use a SWITCH function to subtract the necessary discount in column D.

1. 1.

   Select range F3:G5.

    
2. 2.

   Click Formulas ➤ Create from Selection or press Ctrl + Shift F3.

    
3. 3.

   The Create Names from Selection window appears and guesses where the values you want to use for the names are (Figure 3-21). Very often, this is correct, but you should check. Left column is selected in our example. This is correct. Click OK.

    

=C2*SWITCH(B2,"Gold",(1-Gold),"Silver",(1-Silver),"Bronze",(1-Bronze))

The SWITCH function checks the level in column B against its list of values and returns the matching discount to be applied to the amount.

Scope of a Defined Name

The default scope of a named range is the workbook level, and all the names created so far in this chapter have had that scope.

Let’s look at how to create a named range with worksheet scope.

We have the data shown in Figure 3-23, with the following IF function in column C that calculates a 15% discount if the target in cell E3 is reached:

=IF(B2>=$E$3,B2*(1-15%),B2)

We will create a named range for cell E3 with worksheet-level scope:

1. 1.

   Click cell E3.

    
2. 2.

   Click Formulas ➤ Define Name.

    
3. 3.

   In the New Name window (Figure 3-24), change the Name to “rngTarget”.

    
4. 4.

   Change the Scope using the drop-down list provided from Workbook to Scope and Constants (this is the name of the worksheet). Click OK.

    

The rngTarget name can now be used instead of the $E$3 reference in the IF function (Figure 3-25). However, if you try and reference rngTarget from another worksheet, it will not be recognized.

You cannot edit the scope of an existing named range. Figure 3-26 shows the Edit Name window and the scope option disabled.

It is worth noting the behavior of names when sheets are copied in Excel. The behavior is often undesirable causing confusion and cluttered workbooks. Therefore, it is important to be familiar and understanding of what happens when sheets are copied.

The following lists the general behavior of names when sheets are copied within the same workbook or to another workbook:

• When a worksheet with a workbook scope named range is duplicated within the same workbook, a second name with the same name but worksheet level scope is created.

  Figure 3-27 shows two [Discount] and [Threshold] names caused by copying the [Named Ranges] worksheet. These new names have worksheet scope. This is shown in the [Scope] column of the Name Manager window. The name of the duplicated worksheet [Named Ranges (2)] is shown.

• When a worksheet with a named range is copied to another Excel workbook, the name is copied along with the sheet. This applies to both workbook and worksheet scope named ranges.

  This is generally a good action as you probably require the named range in that workbook too. However, it is important to be aware of this behavior; otherwise, Excel files can get cluttered quickly with redundant names.

• When a workbook contains a named formula with workbook scope, and a worksheet is copied to another workbook, the named formula is also copied to the other workbook. However, the named formula links back to the workbook from which it was copied.

  Figure 3-28 shows the [lstproducts] name that we created using the INDEX formula. A worksheet was copied to another workbook, and this named formula was copied with it, but links back to the [named-ranges.xlsx] workbook.

  This behavior is bad. Be careful of external links in a workbook that are generated by users copying sheets to the workbook. This only happens with workbook scope names.

Edit a Name

1. 2.

   Edit the Name to read “rngThreshold” (Figure 3-42). Click OK.

   Note You can also edit the Refers to and Comment fields, but note that the Scope of a name cannot be changed.

    

All formulas that reference the name [Threshold] will automatically update to reference [rngThreshold].

Filtering Names

The Name Manager provides an excellent filter – very useful if you have many names.

For example, you can filter for only names with workbook scope, or only the defined names and ignore the tables, or filter to only show names that have errors.

Figure 3-43 shows three of the names displaying the #REF! error. This is caused because the data that the names were using has been removed.

Click Filter in the top-right corner and click Names with Errors to only show these names.

When the names have been fixed, or you just want to return to seeing all the names, click Filter ➤ Clear Filter (Figure 3-44).

Print a List of the Names

You can also print a list of the names to the worksheet. This feature will only print names that have workbook scope.

Let’s print the names to cell A2 of the [Print Names] worksheet:

1. 1.

   Click cell A2.

    
2. 2.

   Click Formulas ➤ Use in Formula ➤ Paste Names or press F3.

    
3. 3.

   Click the Paste List button (Figure 3-45).

    

The list of names is printed, including what they refer to (Figure 3-46).

Apply Names to Existing Formulas

If you define names after using them within formulas, there is a neat way to update these formulas with the names.

Let’s use an example with the formula we used in the “Define Names with Create from Selection” section of this chapter.

The formula is shown in Figure 3-47, but it uses grid references and not the names that we defined for cells G3, G4, and G5.

=C2*SWITCH(B2,"Gold",(1-$G$3),"Silver",(1-$G$4),"Bronze",(1-$G$5))

To update the formulas with the names

1. 1.

   Click Formulas ➤ list arrow next to Define Name ➤ Apply Names (Figure 3-48).

    

1. 2.

   Click the Bronze, Gold, and Silver names. Click OK (Figure 3-49).

   Note The names will be applied to all formulas where they can match the name and grid reference in the entire worksheet.

    

The Ignore Relative/Absolute option ensures that the name and grid are an exact match. If the grid reference is relative, but the named range is absolute, it will be ignored.

This is preferable, so we leave it checked. Changing relative references to absolute and vice versa at large scale does not sound good.