Microsoft Office Tutorials and References
In Depth Information
Consolidate Data from Multiple Worksheets
Consolidate Data from
Multiple Worksheets
If you have multiple worksheets with the same
or similar data, you can consolidate all of that
data into a single worksheet that displays a
summary of the data.
It is common to distribute similar worksheets
to multiple departments to capture budget
numbers, inventory, survey data, and so on.
Those worksheets are then usually returned,
and all the data must be combined into a
summary report showing company-wide totals.
This is called consolidating the data.
Rather than doing this manually, Excel can
consolidate your data automatically. You can
use the Consolidate feature to consolidate the
data either by position or by category. In both
cases, you specify one or more source ranges
(the ranges that contain the data you want to
consolidate) and a destination range (the range
where the consolidated data will appear).
In the first part of this section, you learn how
to consolidate by position, which is the easiest
method. To use this method, the sheets you
are working with must have the same layout.
See the second part of this section to learn
how to consolidate by category.
Consolidate By
Position
1 Create a new worksheet
that uses the same
layout — including row
and column headers —
as the sheets you want to
consolidate.
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.
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.
9 Click Add.
4
5
2
1
3
2
2
8
6 7
9
 
Search JabSto ::




Custom Search