Microsoft Office Tutorials and References
In Depth Information
Consolidating Your Data—Getting It All Together
consolidation by position, which is the approach you’ll take when the data labels on the various sheets
are all in the same position, as they are in our sheets. Since our three sheets line up their labels in
exactly the same way, all we need do in the Consolidate dialog box above is enter the ranges in which
the data appear in the three yearly sheets. Click in the Reference field if necessary, and then click the
2008 tab, drag cells B5:B8, and click Add . You’ll see (Figure 7–27):
Figure 7–27. Identifying the first range to be consolidated
Then click sheet 2009, select cells B5:B8, click Add, and do the same for sheet 2010. (Note that when
you click on sheets 2009 and 2010 you should find that B5:B8 is already selected. If not, just drag those
cells.) Then click OK in the above dialog box. You should see (Figure 7–28):
Figure 7–28. The consolidation—you’re looking here at the Consolidated Data sheet
The data are consolidated in the Consolidated Data sheet. Each salesperson’s sales totals for the
years 2008-2010 are now recorded, or consolidated, in one cell. Again, this is a consequence of the
data labels on the three yearly sheets all holding the same relative position. Thus Consolidate knows
that because Ted is the first-listed salesperson on all three sheets, the first cells in the three ranges we
adde d to the Con sol i date di al og box al l r e pr e se n t T e d’ s sal e s data, an d so on . ( Important note : Wor ki n g
by position, as we’ve done here, doesn’t require that the salesperson’s labels on the respective sheets
all share exactly the same addresses. They need to share the same relative position, such that Ted would
al way s be l i ste d fi r st, fol l owe d by Jan e , e tc.)
Now for what again is called consolidation by category . Delete the results in the Consolidated Data
sheet (but not the data labels) and click on sheet 2008. Switch the positions of the names Ted and Jane,
so that Jane is now listed above Ted. Then click back on the Consolidated Data sheet and this time
select A4:B8, which includes the data labels— something we didn’t need to do when we consolidated by
 
Search JabSto ::




Custom Search