In this chapter, you will learn to: | |
✓ | Name groups of data. |
✓ | Create formulas to calculate values. |
✓ | Summarize data that meets specific conditions. |
✓ | Find and correct errors in calculations. |
Microsoft Office Excel 2007 workbooks give you a handy place to store and organize your data, but you can also do a lot more with your data in Office Excel 2007. One important task you can perform is to calculate totals for the values in a series of related cells. You can also use Excel 2007 to find out other information about the data you select, such as the maximum or minimum value in a group of cells. By finding the maximum or minimum value in a group, you can identify your best salesperson, product categories you might need to pay more attention to, or suppliers that consistently give you the best deal. Regardless of your bookkeeping needs, Excel 2007 gives you the ability to find the information you want. And if you should make an error, you can find the cause and correct it quickly.
Many times you can’t access the information you want without referencing more than one cell, and it’s also often true that you’ll use the data in the same group of cells for more than one calculation. Excel 2007 makes it easy to reference a number of cells at once, enabling you to define your calculations quickly.
In this chapter, you’ll learn how to streamline references to groups of data on your worksheets and how to create and correct formulas that summarize Consolidated Messenger’s business operations.
See Also
Do you need only a quick refresher on the topics in this chapter? See the Quick Reference entries at the beginning of this book.
Important
Before you can use the practice sites provided for this chapter, you need to install them from the book’s companion CD to their default location. See "Using the Book’s CD" at the beginning of this book for more information.
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.
Note
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 DocumentsMicrosoft PressExcel SBSFormulas folder.
Select cells C4:G4.
In the Name box on the left of the formula bar, type V101LastWeek and press
.Excel 2007 creates a named range named V101LastWeek.
On the Formulas tab of the user interface, in the Defined Names group, click Name Manager.
The Name Manager dialog box appears.
Click the V101LastWeek name.
The cell range to which the V101LastWeek name refers appears in the Refers to field.
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.
Click Close.
The Name Manager dialog box disappears.
Select the cell range C5:H5.
On the Formulas tab, in the Defined Names group, click Define Name.
The New Name dialog box appears.
In the Name field, type V102LastWeek.
Verify that the definition in the Refers to field is =LastWeekMiles!$C$5:$H$5.
Click OK.
Excel 2007 creates the name and closes the New Name dialog box.
