In Excel, you can name a cell or range, and refer to that name in formulas or other operations such as printing. The following are the rules for defining a name:
• Names can be up to 255 characters in length.
• Names can be composed of letters and/or numbers. Excel is not case-sensitive; names can be UPPERCASE or lowercase.
• Names must begin with a letter.
• Names must not contain spaces, but may include underscores, backslashes, and periods. • Names must not resemble a cell reference (such as C100, C$100, or R1C1).
It is generally advisable to keep names short and meaningful. There are advantages to using names:
• Named formulas are easy to read and interpret. For example, the formula =REVENUE-EXPENSES is easier to understand than =C5-C18.
• Once a range is named, if you add or delete cells, rows, or columns to that range, Excel automatically adjusts the definition of the name.
• If you are working with more than one sheet in a workbook, names created in any sheet can be used anywhere in the workbook.
The Define Name command in the Formulas ribbon is used to give a name to a cell or a range. EXERCISE 4-12
Defining names
This exercise demonstrates how to define names using the Define Name and Create from Selection commands.
1. Open the workbook CT2L4P6.XLS. This workbook contains a simple income statement which you can use to practise working with names.
2. To name the value in cell B1 “Revenue,” first select cell B1. Click the Define Name icon. The New Name dialog box appears (see Exhibit 4-11). Excel suggests the name “Revenue” for cell B1 by entering Revenue in the Name text box, and the cell reference of =Sheet1!$B$1 in the Refers to text box.
The name of Sheet1 and absolute cell reference of $B$1 is to ensure that the name Revenue can be referred to from any sheet in the workbook. You will learn more about absolute references in Lesson 6.
Excel picks up the name from the text in cell A1, to the left of cell B1. Click OK to accept these suggestions. If you want a name other than that suggested by Excel, you can enter it in the text box.
EXHIBIT 4-11
New Name dialog box
3. You can also name a range. To name the range B4:B7 as “Expenses,” first select this cell range. Click Define Name. Type Expenses in the Name text box, but do not press
ENTER or click OK yet. Notice the entry in the Refers to text box and verify that the cell reference is =Sheet1!$B$4:$B$7. Click OK. Press any of the arrow keys to deselect the range B4:B7.
4. Click the drop-down arrow to the right of the name box (left of the formula bar) to display the names in the workbook. Click “Expenses” in the list of names to select it and the range B4:B7 is highlighted (see Exhibit 4-12).
EXHIBIT 4-12
Displaying names
5. You can also give a name to a range using the Create from Selection command. Select range A8:B8. Click Create from Selection. The Create Names from Selection dialog box appears. You can choose to instruct Excel to use the name in the top row, in the left column, in the bottom row, or in the right column of the range. In this case, Excel suggests you use the name in the left column (see Exhibit 4-13). That is, use the name in cell A8 for cell B8. Click OK.
EXHIBIT 4-13
Create Names from Selection dialog box
6. Click the drop-down arrow to the right of the name box. The list of names shows three names: Expenses, Revenue, and Total_expenses. Excel adds an underscore to connect the two words that comprise the name of Total_expenses.
7. Save this workbook under your initials, and leave it open for the next exercise. If you had difficulty with this exercise, compare your workbook with CT2L4P6S.XLS. The main difference between the Define Name command and the Create from Selection command is that you can type a name for the Define Name command, but you can only use the text in one of the neighbouring cells for the Create from Selection command.
EXERCISE 4-13
Using names in formulas
This exercise provides practice in using names in formulas. You can either continue with the workbook from Exercise 4-12, or open the solution workbook CT2L4P6S.XLS. This workbook contains only one sheet.
1. Click the drop-down arrow to the right of the name box. Your workbook should have three names: Expenses, Revenue, and Total_expenses.
2. Examine the formulas on this worksheet. Click Show Formulas in the Formulas ribbon. Your worksheet should look like Exhibit 4-14. After checking the formulas in
column B, turn Show Formulas off. EXHIBIT 4-14
Formulas for CT2L4P6.XLS before using names
3. Check that the name “Expenses” has been assigned to the range B4:B7 by clicking the drop-down arrow to the right of the name box. Click Expenses and the range B4:B7 is highlighted (see Exhibit 4-12).
4. Select cell B8. Its formula is currently =SUM(B4:B7). However, the range B4:B7 has been assigned the name of Expenses. You can apply this name in the formula. click the down arrow of the Define Name command, then choose Apply Names. The Apply Names dialog box appears. Click Expenses to select it, then click OK. The formula in cell B8 now reads =SUM(Expenses).
5. Apply the names of Revenue and Total_expenses in the formula in cell B9 using the procedure described in step 4, but in the Apply Names dialog box, select Revenue and Total_expenses. The formula in cell B9 now reads =Revenue-Total_expenses. (See Exhibit 4-15.)
EXHIBIT 4-15
Formulas after using names
6. Instead of applying the name to an existing formula, you can enter the name directly in the formula. Select cell C9. Type =Revenue-Total_expenses and press ENTER. The formula is entered in cell C9, with the result being 4000. If your cell shows #NAME?, you must have mistyped one of the names.
7. Save the workbook under your initials and close it. If you have difficulty with this exercise, compare your workbook with CT2L4P7S.XLS.
EXERCISE 4-14
Creating names
Excel can automatically create names for cells based on row or column titles in your workbook. This exercise gives you some practice in using the Create from Selection command to compose multiple names in a range.
1. Open the workbook CT2L4P8.XLS. This workbook contains only one worksheet, which holds the expenses for the first three months. The formulas for the quarterly and monthly totals have not been entered. You will use names to complete these formulas. 2. Select the range A3:D9. Click Create from Selection. The Create Names from
Selection dialog box shows that Top row and Left column have been checked. Because you want to create names using row 3 and column A, click OK to accept these
suggestions.
3. Click the drop-down arrow to the right of the name box. The worksheet now has a list of names: Advertising, Feb, Freight, Jan, Mar, Miscellaneous, Rent, Salaries, and Utilities. Select Advertising and observe that this name has been given to the range B4:D4. Select Jan and notice that this name has been given to the range B4:B9.
4. Select cell E4. In this cell, you will construct the formula =SUM(Advertising). Start the formula by typing =SUM(, then click the Use in Formula command and select
Advertising. and type ). Press ENTER. The result should be $3,030.10.
5. For practice, repeat step 4 for two more of the expense categories for column E: E5: =SUM(Freight)
E6: =SUM(Miscellaneous)
Complete the formulas for cells E7 to E9 for additional practice.
6. Now select cell B10. Instead of selecting the name from the Use in Formula command list, enter the formula =SUM(Jan). The result should be $7,400.00.
7. Repeat step 6 for the month of February: C10: =SUM(Feb)
Complete the formula for cell D10 for practice.
8. In cell E10, enter the formula =SUM(E4:E9). The result should be $22,347.49. 9. Save the completed workbook under your initials and close it. If you had difficulty
with this exercise, compare your workbook with CT2L4P8S.XLS.