Create a summary report in Excel with data consolidation - Special Movies and Comedy


Special Movies and Comedy

Wednesday, April 13, 2011

Create a summary report in Excel with data consolidation

Consolidating Excel worksheets can be a very valuable tool, especially in the corporate world. If you currently have multiple Excel spreadsheets with data that you need to add into one document then all you need to do is consolidate instead of copying the data yourself.

If you have two or more Microsoft Excel worksheets that are identicalto each other (except the values are different), you can have Excel's DataConsolidate 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 theircorresponding midterm grades in B1:B20. The second worksheet lists thestudents' 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 newworksheet.

1 comment:

  1. Any time you can save time by importing versus copying you can gain maybe a few more minutes of free time:) Life is hard enough and sometimes we need a few tools to make it easier for us. Excel is still a large mystery to me but as a blogger and future webmaster I can see the necessity of this database program (or ones similar to it).