Microsoft Office Tutorials and References
In Depth Information
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
secondday shipping data, hide both, or show both.
Excel also has a capability you might expect to find only in a database program—in Excel,
you can type a value in a cell and have Excel look in a named range to find a
corresponding value. For instance, you can have a two-column named range with one column
displaying customer identification numbers and the second column displaying the name
of the company assigned each number. By using a VLOOKUP formula that references
the named range, you can let colleagues using your workbook type a customer
identification number in a cell and have the name of the corresponding company 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 worksheet.
Practice Files Before you can complete the exercises in this chapter, you need to copy
the book’s practice files to your computer. The practice files you’ll use to complete the
exercises in this chapter are in the Chapter06 practice file folder. A complete list of
practice files is provided in “Using the Practice Files” at the beginning of this topic.
Sorting Worksheet Data
Although Excel makes it easy to enter your business data and to manage it after you’ve
saved it in a worksheet, unsorted data will rarely answer every question you want to ask
it. For example, you might want to discover which of your services generates the most
profits, which service costs the most for you to provide, and so on. You can discover that
information by sorting your data.
When you sort data in a worksheet, you rearrange the worksheet rows based on the
contents of cells in a particular column or set of columns. For instance, you can sort a
worksheet to find your highest-revenue services.
You can sort a group of rows in a worksheet in a number of ways, but the first step is to
identify the column that will provide the values by which the rows should be sorted. In
the revenue example, you could find the highest revenue totals by sorting on the cells
in the Revenue column. First you would select the cells in the Revenue column and
display the Home tab. Then, in the Editing group, in the Sort & Filter list, click Sort Largest