Use Consolidate in Excel with an example

Use Consolidate in Excel with an example

Consolidate option under Data menu is used to perform summary calculations on values in corresponding cells in various source ranges. Consolidate command is used to find the count, sum, product, minimum, maximum, variance, standard deviation, or average of the values in a group of ranges.

Example :

Below figure shows workbook containing ranges you want to consolidate :

Range 1 shows Quarter wise Sales data for Products in Shop 1 
Range 2 shows Quarter wise Sales data for Products in Shop 2

We need to consolidate Quarter wise sales data for products from both shops

1. Click on Data -> Consolidate
2. Consolidate Dialog box shows below Consolidate function 

3. Select Sum in Consolidate Function

4. Select the first range in Worksheet in Reference

5. Click on Add button to add to All references :

6. Select the second range in Worksheet in Reference and click on Add Button.

7. Check Top row and Left column checkbox and Click in OK button in Consolidate dialog to consolidate data