Microsoft Office Tutorials and References

In Depth Information

**Chapter 6: Reordering and summarizing data**

Reordering and

6

summarizing data

IN THIS CHAPTER, YOU WILL LEARN HOW TO

▪
Sort worksheet data.

▪
Sort data by using custom lists.

▪
Organize data into levels.

▪
Look up information in a worksheet.

Most of the time, when you enter data in a Microsoft Excel worksheet, you will enter it in

chronological order. For example, 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 you entered it, but that might not always be the best

arrangement 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 data 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 order you want, you can 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 calculate the total for rows that have the same value in one of

their columns. For example, 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 creates an outline that marks the cell

ranges used in each subtotal. For example, if the first 10 rows of a worksheet contain

overnight shipping data, and the second 10 rows contain second-day shipping data, Excel

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.