Naming Groups of Data

When you work with large amounts of data, it’s often useful to identify groups of cells that contain related data. For example, you can create a worksheet in which cells C4:I4 hold the number of packages Consolidated Messenger’s Northeast processing facility handled from 5:00 PM to 12:00 AM on the previous day.

Instead of specifying the cells individually every time you want to use the data they contain, you can define those cells as a range (also called a named range). For instance, you can group the items from the preceding graphic into a range named NortheastLastDay. Whenever you want to use the contents of that range in a calculation, you can simply use the name of the range instead of specifying each cell individually.

Note 

Yes, you could just name the range Northeast, but if you use the range’s values in a formula in another worksheet, the more descriptive range name tells you and your colleagues exactly what data is used in the calculation.

To create a named range, select the cells you want to include in your range, click the Formulas tab on the user interface, and then, in the Defined Names group, click Define Name to display the New Name dialog box. In the New Name dialog box, type a name in the Name field, verify that the cells you selected appear in the Refers to field, and then click OK. You can also add a comment about the field in the Comment field and select whether you want to make the name available for formulas in the entire workbook or just on an individual worksheet.

If the cells you want to define as a named range have a label you want to use as the range’s name, you can display the Formulas tab and then, in the Defined Names group, click Create from Selection to display the Create Names from Selection dialog box. In the Create Names from Selection dialog box, select the check box that represents the label’s position in relation to the data cells and then click OK.

A final way to create a named range is to select the cells you want in the range, click in the Name box next to the formula bar, and then type the name for the range. You can display the ranges available in a workbook by clicking the Name box down arrow.

To manage the named ranges in a workbook, display the Formulas tab on the ribbon and then, in the Defined Names group, click Name Manager to display the Name Manager dialog box.

When you click a named range, Excel 2007 displays the cells it encompasses in the Refers to field. Clicking the Edit button displays the Edit Name dialog box, which is a version of the New Name dialog box, enabling you to change a named range’s definition. You can also get rid of a name by clicking it, clicking the Delete button, and then clicking OK in the confirmation dialog box that appears.

Important 

If your workbook contains a lot of named ranges, you can click the Filter button in the Name Manager dialog box and select a criterion to limit the names displayed in the Name Manager dialog box.

In this exercise, you will create named ranges to streamline references to groups of cells.

Use the VehicleMiles workbook in the practice file folder for this topic. This practice file is located in the Formulas folder under SBS_Office2007.

Be Sure To start Excel 2007 before beginning this exercise.

Open the VehicleMiles workbook from the My Documents\Microsoft Press\Excel SBS\Formulas folder.

1. Select cells C4:G4.

2. In the Name box on the left of the formula bar, type V101LastWeek and press .

Excel 2007 creates a named range named V101LastWeek.

3. On the Formulas tab of the user interface, in the Defined Names group, click Name Manager.

The Name Manager dialog box appears.

4. Click the V101LastWeek name.

The cell range to which the V101LastWeek name refers appears in the Refers to field.

5. Edit the cell range in the Refers to field to =LastWeekMiles!$C$4:$H$4, click OK, and then click the check button next to the Refers to field.

Excel 2007 changes the named range’s definition.

6. Click Close.

The Name Manager dialog box disappears.

7. Select the cell range C5:H5.

8. On the Formulas tab, in the Defined Names group, click Define Name.

The New Name dialog box appears.

9. In the Name field, type V102LastWeek.

10. Verify that the definition in the Refers to field is =LastWeekMiles!$C$5:$H$5.

11. Click OK.

Excel 2007 creates the name and closes the New Name dialog box.

Close the VehicleMiles workbook.