Data Consolidation in Microsoft Excel

To group and summarize the data, we can create an outline up to eight levels in the data list. To expose the detail for each group, use an outline to quickly display. If you have data in the same cells in multiple sheets which need to be average up together, you can do this using Consolidate option in Microsoft Excel.

Let’s take an example.We have 3 months sales data in 3 different sheets in an Excel workbook, and in one sheet, we have to return the average quantity of all month’s agent wise.


Follow below given steps to Average the values from the cells in different sheets:-

  • Select the cell C2 in consolidated sheet.
  • Go to Data tab, and select Consolidate from the Data tools group.
  • Consolidate dialog box will appear.


  • Select Sum from the function drop down list.
  • Click on Reference, and go to January sheet and select the range C2:C11.


  • Click on add button and then select the range C2:C11 in February sheet.
  • Click on add button and then select the range C2:C11 in March sheet.


  • Click on OK.


  • The Average sales quantity will appear in the range C2:C11 to all the agents.

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube