Chapter 7. Reordering and Summarizing Data


7. Reordering and Summarizing Data

Chapter at a Glance

In this chapter, you will learn to:

Sort a data list.

Organize data into levels.

Look up information in a data list.


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.

See Also

Do you need only a quick refresher on the topics in this chapter? See the Quick Reference entries on pages xxviilxiii.


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" on page xiii for more information.





MicrosoftR Office ExcelR 2007 Step by Step
MicrosoftВ® Office ExcelВ® 2007 Step by Step (Step By Step (Microsoft))
ISBN: 073562304X
EAN: 2147483647
Year: 2004
Pages: 143
Authors: Curtis Frye

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net