7. Reordering and Summarizing Data
Chapter at a Glance
Most of the time, when you enter data in a Microsoft® Office Excel® 2007 worksheet, you will enter it in chronological order. For instance, you could enter hourly shipment data in a worksheet, starting with the first hour of the day and ending with the last hour. The data would naturally be displayed in the order in which you entered it, but that might not always be the best order to answer your questions. For instance, you might want to sort your data so that the top row in your worksheet shows the day of the month with the highest package volume, with subsequent rows displaying the remaining days in decreasing order of package volumes handled. You can also sort based on the contents of more than one column. A good example is sorting package handling data by week, day, and then hour of the day.
After you have sorted your data into the desired order, Office Excel 2007 enables you to find partial totals, or subtotals, for groups of cells within a given range. Yes, you can create formulas to find the sum, average, or standard deviation of data in a cell range, but you can do the same thing much more quickly by having Excel 2007 calculate the total for cells with the same value in a column. If your worksheet holds sales data for a list of services, you can calculate subtotals for each product category.
When you calculate subtotals in a worksheet, Excel 2007 creates an outline that marks the cell ranges used in each subtotal. For example, if the first 10 rows of a worksheet have overnight shipping data, and the second 10 rows have second-day shipping data, Excel 2007 divides the rows into two units. You can use the markers on the worksheet to hide or display the rows used to calculate a subtotal; in this case, you can hide all the rows that contain overnight shipping data, hide all the rows that contain second-day shipping data, hide both, or show both.
Excel 2007 does have a capability you might expect to find only in a database programthe capability to have a user type a value in a cell and have Excel 2007 look in a named range to find a corresponding value. For instance, you can have a two-column named range with one column displaying the International Standard Book Number (ISBN) of a book and the second column displaying the title of the same book. By using a VLOOKUP formula that references the named range, you can let colleagues using your workbook type an ISBN in a cell and have the name of the book with that ISBN appear in the cell with the formula.
In this chapter, you'll learn how to sort your data using one or more criteria, calculate subtotals, organize your data into levels, and look up information in a data list.
Do you need only a quick refresher on the topics in this chapter? See the Quick Reference entries on pages xxviilxiii.