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.
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.
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 My Documents\Microsoft Press\Excel SBS\Formulas folder.
BE SURE TO start Excel 2007 before beginning this exercise.
OPEN the VehicleMiles workbook from the My Documents\Microsoft Press\Excel SBS\Formulas folder.
CLOSE the VehicleMiles workbook.