Microsoft Office Tutorials and References
In Depth Information
Consolidating Your Data—Getting It All Together
Figure 7–31. Expanding Ted’s sales data—the three years contributing to his total are revealed
You’ve viewing an instance of Excel’s outlining capability, which in this case breaks out the three
years’ worth of sales data for Ted. Clicking the plus sign reveals—or expands —the se da ta , whi ch ha d
been hidden—thus explaining the row-sequence gaps we noted earlier (you may also want to widen
the columns so as to better view the Sales By Year labels). These new data really consist of cell
references to the relevant data in the three yearly sheets for each salesperson. Thus by clicking the
first such row for Ted, you’ll see in the formula bar:
=’2008’!$B6
which references the cell in sheet 2008 in which Ted’s sales data appears. And because we’re
working with cell references, changing any data in these linked cells will change the consolidation
result here. Then, clicking what is now a minus sign collapses these detail rows and hides them again.
Click the 2 you’ll see in the upper reaches of that gray area housing the plus-minus signs will expand
all the hidden row data for all the salespersons, and clicking 1 collapses them (Figure 7–32):
Figure 7–32. Outlining buttons for expanding and collapsing the data
And if you click the Data tab Outline group the Ungroup down arrow Clear Outline , the pl
usminuses will disappear, but you’ll still be left with the salesperson detail rows, this time all visible.
And that’s how to consolidate data across multiple worksheets. On the other hand, the
consolidation options aren’t terribly flexible, and you may decide that other alternatives to
 
Search JabSto ::




Custom Search