How do you consolidate data in a worksheet?

Consolidating Data is used for summarizing data that may be stored in various worksheets within the same workbook or in separate workbooks. The data can be consolidated for Ad hoc purposes or updating data on a regular basis by establishing a link.

A typical example for creating a Consolidating data could be for example, if you have a worksheet of sales figures for each of your regional offices, you might use a consolidation to roll up these figures into an Overall Sales worksheet. This master worksheet might contain expenses totals and averages, current inventory levels, and highest selling products for the entire organization.

To consolidate data:

Step 1: Choose the Master Sheet where you want to consolidate the data and click on the relevant cell.

Step 2: Choose Data > Consolidate… from the menu bar. You should be able to see the Consolidate dialog box.

Step 3: From the Function dropdown list choose the relevant function that you wish to apply for consolidation e.g. you can perform Average, Min, Max, etc… Sum is chosen by default as this is the most common type of consolidation users perform.

NB: Consolidation feature works best when the data on all the worksheets has the similar layout.

Step 4: Click on the reference box and select the relevant sheet and the cells that you want to include as first part of your consolidation range.

NB: Please choose the row and column headings while performing the above step especially if the Master worksheet doesn’t contain no data.

Step 5: Click the Add button on the Consolidate dialog box.
Repeat steps until you select all the data that you wish to include for consolidation.

Under the Use labels in section check if you want the Row and Column headings to be included in the Master worksheet.

Also depending on your circumstances you may want to create a link so that if the data in the other worksheet changes then the results are updated in the master worksheet. There may be a possibility that you consolidate data on a weekly/monthly /quarterly basis and do not wish to establish the link.

If you do want to establish the link then put a check on the create links to the source data.

Step 6: Press OK Button.

I hope this Clarifies your query.

If this has answered your query then i would request you to please mark the question as resolved!! If not and you have a specific question related to this then please post it as a new question and we should be able to answer that!!

Many thanks

Rajeev Rawat
MOS Master Instructor 2000 and 2003