Microsoft Office Tutorials and References
In Depth Information
Consolidate Data from Multiple Worksheets
Consolidate Data from Multiple
Worksheets (continued)
If the worksheets that contain the data you
want to summarize do not use the same layout,
you can still perform the consolidation.
However, in this case you need to tell Excel to
consolidate the data by category . This method
consolidates the data by looking for common
row and column labels in each worksheet.
For example, suppose you are consolidating
sales, and Division A sells software, books,
and videos, Division B sells books and
CD-ROMs, and Division C sells books,
software, videos, and CD-ROMs. When you
consolidate this data, Excel looks for the
common data. For instance, Excel would
summarize the software values from
Divisions A and C, the CD-ROM values
from Divisions B and C, and the book values
from all three divisions.
Remember, for this to work, the common data
values must use the same row or column labels.
Consolidate By
Category
1 Create a new worksheet
for the consolidation.
2 Open the workbooks that
contain the worksheets
you want to consolidate.
3 Select the upper-left
corner of the destination
range.
4 Click the Data tab.
5 Click Consolidate.
4
1
5
2
3
2
2
The Consolidate dialog
box appears.
6 Click the Function
drop-down arrow and
then click the summary
function you want to use.
7 Click inside the Reference
text box.
8 Select one of the ranges
you want to consolidate.
Note: Be sure to include the row
and column labels in the range.
9 Click Add.
6
7
8
9
 
Search JabSto ::




Custom Search