Microsoft Office Tutorials and References
In Depth Information
Chapter 6: Reordering and 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.