Microsoft Office Tutorials and References
In Depth Information
Consolidating by category
Figure 8-80 Use the categories in the left column of each source worksheet as the basis for this
To consolidate by category, follow these steps:
Select the destination area. This time the destination area must include the row
headings—but how many rows? To answer that, you can look at each source
worksheet and determine how many unique line items you have. An easier way,
however, is to select a single cell—in this case, cell A4—as the destination area. When
you specify a single cell as your destination area, the Consolidate command ills in
the area below and to the right of that cell as needed. In the example, to preserve the
formatting, we inserted more than enough rows to accommodate the data.
Click Data, Consolidate.
To consolidate by row categories in this example, select the Left Column check box in
the Use Labels In area. Click the default Sum function in the Function drop-down list.
(The consolidation worksheet already has column labels.)
Each source reference must include the Name, Units, and Sales data. Select these
ranges on each monthly worksheet. For example, on the Jan worksheet, we selected
$A$4:$C$8. Unlike when consolidating by position, you have to manually select the
ranges in each supporting worksheet because Excel selects the last range you added,
which will not necessarily be what you need in each worksheet.