Microsoft Office Tutorials and References
In Depth Information
Consolidating Your Data—Getting It All Together
position. Then click Data Tools Consolidate. You should see the three ranges we selected for the
previous consolidation listed. Click on each one and click Delete. Then type Sales08 in the reference
fi e l d, cl i ck Add, an d r e pe at the pr oce dur e by ty pi n g an d addi n g Sal e s09 an d Sal e s10. Re me mbe r that
we named these ranges when we opened the Sales By Year sheet, and these ranges include the cells in
which the data labels appear, in addition to the data.
Then click the Top row and Left Column boxe s i n the Use Labels in area of the Consolidate dialog
box, and click OK. You should see (Figure 7–29):
Figure 7–29. Consolidating by category
Note the totals here for Ted and Jane differ from our previous results because we’ve switched the
positions of Ted and Jane’s names, and hence their sales totals, in sheet 2008. We’ve just carried out a
consolidation by category , in which the names of the salespersons d on’t share the same relative
position across the three sheets. That’s why we needed here to click on Top Row and Left Column to
orient us, because Consolidate needs to “find” all the salespersons and their data by looking for their
names and the Sales label on each sheet— wherever they may be positioned.
That point may call for a bit of review. “Consolidation by category” means that Excel will search
for salespersons’ names wherever they happened to be positioned on the 2008, 2009, and 2010 sheets.
The salespersons are the categories here.
There’s one other Consolidation option that you may want to explore— Create links to source data .
Checking this enables you to change the numbers in any of the sheets contributing to the
consolidation, such that those changes will be recorded in the consolidation result. To demonstrate,
return to the Consolidated Data sheet and make sure range A4:B8 is selected, with the results you’ve
seen above. Then click the Data tab Data Tools Consolidate, and click Create links to source data,
then click OK. You’ll see (Figure 7–30):
Figure 7–30. Consolidating by linking to the source data
You’ll note the obvious addition to the screen. A collection of plus signs has taken over a new, gray
area to the left of the worksheet proper, and you’ll note as well that the row numbers exhibit gaps in
their sequence, and—less obviously—that a new third column has inserted itself between the
salespersons’ names and the actual sales data. Click one plus sign and you’ll see (Figure 7–31):