How Can I Create Named Ranges?

There are three ways to create named ranges:

• By entering a range name in the Name Box.

• By clicking Create From Selection in the Defined Names group on the Formulas tab.

• By clicking Name Manager or Define Name in the Defined Names group on the Formulas tab.

Using the Name Box to Create a Range Name

The Name Box is located directly above the label for column A, as you can see in Figure 1-1. (To see the Name Box, you need to display the Formula bar.) To create a range name in the Name Box, simply select the cell or range of cells that you want to name, click in the Name Box, and then type the range name you want to use. Press Enter, and you’ve created the range name. Clicking the Name arrow displays the range names defined in the current workbook. You can display all the range names in a workbook by pressing the F3 key to open the Paste Name dialog box. When you select a range name from the Name Box, Microsoft Office Excel selects the cells corresponding to that range name. This enables you to verify that you’ve chosen the cell or range that you intended to name.

Figure 1-1: You can create a range name by selecting the cell range you want to name and then typing the range name in the Name Box.

For example, suppose we want to name cell F3 east and cell F4 west. See Figure 1-2 and the file Eastwest.xlsx. We simply select cell F3, type east in the Name Box, and then press Enter. Then we select cell F4, type west in the Name Box, and press Enter. If we now reference cell F3 in another cell, we will see =east instead of =F3. This means that whenever we see the reference east in a formula, Excel will insert whatever is in cell F3.

Figure 1-2: Naming cell F3 east and F4 west

Suppose we want to assign a rectangular range of cells (such as A1:B4) the name Data. Simply select the cell range A1:B4, type Data in the Name Box, and press Enter. Now a formula such as =AVERAGE(Data) would average the contents of cells A1:B4. See the file Data.xlsx and Figure 1-3.

Figure 1-3: Naming range A1:B4 Data

Sometimes we want to name a range of cells made up of several noncontiguous rectangular ranges. For example, in Figure 1-4 and the file Noncontig.xlsx we might want to assign the name Noncontig to the range consisting of cells B3:C4, E6:G7 and B10:C10. To assign this name, select any one of the three rectangles making up the range (we chose B3:C4). Hold down the Ctrl key and select the other two ranges (E6:G7 and B10:C10). Now release the Ctrl key, type the name Noncontig in the Name Box, and press Enter. Using Noncontig in any formula will now refer to the contents of cells B3:C4, E6:G7 and B10:C10. For example, entering the formula =AVERAGE(Noncontig) in cell E10 yields 4.75 (because the 12 numbers in our range add up to 57 and 57/12=4.75).

Figure 1-4: Naming a noncontiguous range of cells

Creating Named Ranges by Using the Create from Selection Option

The worksheet States.xlsx contains sales during March for each of the 50 U.S. states. Figure 1-5 shows a subset of this data. We would like to name each cell in the range B6:B55 with the correct state abbreviation. To do this, select the range A6:B55, and click Create From Selection in the Defined Names group on the Formulas tab (see Figure 1-6 on the next page). Then check the Left Column box, as indicated in Figure 1-7 on the next page.

Figure 1-5: By naming the cells that contain state sales with state abbreviations, you can use the abbreviation when you refer to the cell rather than the cell’s column letter and row number.

Figure 1-6: Select Create From Selection

Figure 1-7: Select the Left Column check box

Excel now knows to associate the names in the first column of the selected range with the cells in the second column of the selected range. Thus, B6 is assigned the range name AL, B7 is named AK, and so on. Note that creating these range names in the Name Box would have been incredibly tedious! Click the Name arrow to verify that these range names have been created.

Creating Range Names by Using the Name Manager Option

If you click Name Manager on the Formulas tab and then choose New, the New Name dialog box shown in Figure 1-8 opens.

Figure 1-8: The New Name dialog box before creating any range names

Suppose you want to assign the name range1 (range names are not case sensitive) to the cell range A2:B7. Simply type range1 in the Name box and then point to the range or type =A2:B7 in the Refers To area. Click OK, and you’re done. The New Name dialog box will now look like Figure 1-9.

Figure 1-9: New Name dialog box after creating a range name

If you click the Scope arrow, you can select the option Workbook or any worksheet in your workbook. We will discuss this decision later, so for now just choose the default Scope of Workbook. You can also add, if desired, comments for any of your range names.

The Name Manager

If you now click in the Name arrow the name range1 (and any other ranges you have created) will appear in the Name Box. In the 2007 Microsoft Office System there is an easy way to edit or delete your range names that was not present in earlier versions of Office. Simply open the Name Manager by selecting Formulas and then clicking Name Manager. You will now see a list of all range names. For example, for the file States.xlsx the Name Manager dialog box will look like Figure 1-10.

Figure 1-10: Name Manager dialog box for States.xlsx

To edit any range name, simply select the range name and click Edit. Then you can change the name of the range, the cells the range refers to, or the scope of the range.

To delete any subset of range names, simply select the range names you want to delete. If the range names are listed consecutively, simply select the first listed range name, hold down the Shift key, and select the last listed range name. If the range names are not listed consecutively, you can select any range name you want to delete and then hold down the Ctrl key while you select the other range names for deletion. Then press the Delete key to delete the selected range names.

Now let’s look at some specific examples of how to use range names.

• I want to total sales in Arizona, California, Montana, New York, and New Jersey. Can I use a formula to compute total sales in a form such as AZ+CA+MT+NY+NJ instead of SUM(A21:A25) and still get the right answer?

• Let’s return to the file States.xlsx in which we assigned each state’s abbreviation as the range name for the state’s sales. If we want to compute total sales in Alabama, Alaska, Arizona, and Arkansas, we could clearly use the formula SUM(B6:B9). We could also point to cells B6, B7, B8, and B9, and the formula would be entered as =AL+AK+AZ+AR. The latter formula is, of course, much easier to understand.

• As another illustration of how to use range names, look at the file Historicalinvest.xlsx, shown in Figure 1-11, which contains annual percentage returns on stocks, T-Bills, and bonds. (Some rows aren’t visible; the data ends in row 81.)

  
  Figure 1-11: Historical investment data

• After selecting the cell range B7:D81 and choosing Formulas, Create From Selection, we choose to create names in the top row of the range. The range B8:B81 is named Stocks, the range C8:C81 T.Bills, and the range D8:D81 T.Bonds. Now we no longer need to remember where our data is. For example, in cell B84, after typing =AVERAGE(, we can press F3 and the Paste Name dialog box appears, as shown in Figure 1-12.

  
  Figure 1-12: You can add a range name to a formula by using the Paste Name dialog box.

• Then we can select Stocks in the Paste Name list and click OK. After entering the closing parenthesis, our formula, =AVERAGE(Stocks), computes the average return on stocks (12.05 percent). The beauty of this approach is that even if we don’t remember where the data is, we can work with the stock return data anywhere in the workbook!

• We would be remiss if we did not mention the exciting new AutoComplete capabilities of Excel 2007. If you begin typing =Average(T then Excel will show you a list of range names and functions that begin with T. Then you can simply click T.Bills to complete the entry of the range name. See Figure 1-13.

  
  Figure 1-13: Example of AutoComplete feature

• What does a formula like Average(A:A) do?

• If we use a column name (in the form A:A, C:C, and so on) in a formula, Excel treats an entire column as a named range. For example, entering the formula =AVERAGE(A:A) will average all numbers in column A. Using a range name for an entire column is very helpful if you frequently enter new data into a column. For example, if column A contains monthly sales of a product, as new sales data is entered each month, our formula computes an up-to-date monthly sales average. I caution you, however, that if you enter the formula =AVERAGE(A:A) in column A, you will get a circular reference message because the value of the cell containing the average formula depends on the cell containing the average. You will learn how to resolve circular references in Chapter 10, “Circular References.” Similarly, entering the formula =AVERAGE(1:1) will average all numbers in row 1.

• What is the difference between a name with workbook scope and one with worksheet scope?

• The file Sheetnames.xlsx will help us to understand the difference between range names that have workbook scope and range names that have worksheet scope. When we create names with the Name Box, the created names have workbook scope. For example, suppose we use the Name Box to assign the name sales to the cell range E4:E6 in Sheet3 and these cells contain the numbers 1, 2, and 4, respectively. Then if we enter a formula such as =SUM(sales) in any worksheet, we will obtain an answer of 7. This is because the Name Box creates names with workbook scope and so anywhere in the workbook where we refer to the name sales (which has workbook scope) the name refers to cells E4:E6 of Sheet3. In any worksheet if we now enter the formula =SUM(sales) we will obtain 7 because anywhere in the workbook Excel will link sales to cells E4:E6 of Sheet3.

• Now suppose that we type 4, 5, and 6 in cells E4:E6 of Sheet1, and 3, 4, and 5 in cells E4:E6 of Sheet2. Next we go to the Name Manager, give the name jam to cells E4:E6 of Sheet1, and define the scope of this name as Sheet1. Then we move to Sheet2, go to the Name Manager, and give the name jam to cells E4:E6, and define the scope of this name as Sheet2. Our Name Manager dialog box now looks like Figure 1-14.

  
  Figure 1-14: Name Manager dialog box with worksheet and workbook names

• Now what if we enter the formula =SUM(jam) in each sheet? In Sheet 1, =SUM(jam) will total cells E4:E6 of Sheet1. Because those cells contain 4, 5, and 6, we obtain 15. In Sheet2 =Sum(jam) will total cells E4:E6 of Sheet2, yielding 3 + 4 + 5=12. In Sheet3, however, the formula =SUM(jam) will yield a #NAME? error because there is no range named jam defined in Sheet3! If we enter anywhere in Sheet3 the formula =SUM(Sheet2!jam) then Excel will recognize the worksheet-level name that represents cell range E4:E6 of Sheet2 and yields a result of 3 + 4 + 5 =12. Thus, prefacing a worksheet-level name by its sheet name followed by an exclamation point (!) will allow us to refer to a worksheet-level range in a worksheet other than the sheet in which the range is defined.

• I really am getting to like range names. I have started defining range names for many of the workbooks I have developed at the office. However, the range names do not show up in my formulas. How can I make recently created range names show up in previously created formulas?

• Let’s look at the file Applynames.xlsx. See Figure 1-15.

  
  Figure 1-15: How to apply range names to formulas

• We have entered the price of a product in cell F3, and product demand of =10000– 300*F3 in cell F4. Our unit cost and fixed cost are entered in cells F5 and F6, respectively. Our profit is computed in cell F7 with the formula =F4*(F3–F5)–F6. We have just used Formulas, Create from Selection, choosing the Left Row option to name cell F3 price, cell F4 demand, cell F5 unit cost, cell F6 fixed cost, and cell F7 profit. We would like these range names to show up in our cell F4 and cell F7 formulas. To apply our range names, first select the range where you want the range names applied (in our case F4:F7). Now go to the Defined Names area on the Formulas tab, click the Define Name arrow, and then click Apply Names. Highlight the Names you want to apply, and click OK. Note that cell F4 now contains the formula =10000–300*price and cell F7 contains the formula =demand*(price–unitcost)–fixed_cost, as desired.

• By the way, if you want the range names to apply to the entire worksheet, simply select the entire worksheet by clicking the Select All button at the intersection of the column and row headings.

• How can I easily select a named range?

• If you have selected a cell within a named range, press Ctrl+* to select the entire range.

• How can I paste a list of all range names (and the cells they represent) into my worksheet?

• Press F3 to display the Paste Name box, and then click the Paste List button. A list of range names and the cells each corresponds to will be pasted into your worksheet, beginning at the current cell location.