Create a summary report in Excel with data consolidation

If you have two or more Microsoft Excel worksheets that are identical
to each other (except the values are different), you can have Excel’s Data
Consolidate feature consolidate the worksheets into a summary report.

For example, suppose you have a workbook that consists of
two worksheets. One worksheet has your students’ names in A1:A20 and their
corresponding midterm grades in B1:B20. The second worksheet lists the
students’ names in column A and their final grades in column B.

To create a worksheet listing the students’ average grade,
follow these steps:

1. Create
   a new worksheet and click A1.
2. Go to
   Data | Consolidate.
3. Select
   Average from the Function drop-down list.
4. Click
   the Collapse dialog button.
5. Select
   A1:B20 in Midterm Grades Sheet.
6. Click
   the Collapse dialog button and click Add.
7. Click
   the Collapse dialog button and Select A1:B20 in Final Grades Sheet.
8. Click
   the Collapse dialog button and click Add.
9. Under
   Use Labels In: select the Left Column check box. Click OK.

The students’ average grades are now listed in the new
worksheet.

Help users increase productivity by automatically signing up for TechRepublic’s free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.