Consolidate data in Excel

Apr 29, 2021 | Excel Hints and Tips

Are you trying to consolidate data or summarise data in Microsoft Excel?

You try a calculation but the order of your data is all over the shot and autofilling it just isn’t going to work. This is going to be a long and arduous task – or is it!! 

Although I always recommend keeping the order of data consistent so if you are calculating you can generally autofill the calculation down to other rows this is sometimes out of your control.  Having an alternative that works regardless is going to be a blessing.  Unfortunately the consolidate feature is not as flexible as using a normal calculation would be but it can get you out of a hole at times. 

Your data can be on different sheets in the same book or even in different files and consolidate allows the functions of Sum, Count, Average, Max. Min, Product, CountNums, StdDev,StdDevp, Var and Varp to be used with the data. 

Using consolidate 

    1. Open the file(s) that contain the data
    2. Go to a new file or new sheet and click on the cell where you want the result to go 
    3. Click on Data tab and choose the consolidate icon 
Consolidate icon in Microsoft Excel

4. Select the function 

5. Click in the reference box and select the first range of cells to consolidate.  If the order of the data is inconsistent this must include the labels for the rows and/or columns.  Without these it is assumed that the data is in the same order in each location. 

6. Click on Add to add this to the All references box 

7. Repeat steps 5 and 6 for each set of data.  (You may need to delete the information in the reference box before doing this) 

8. Assuming the data is in an inconsistent order, and headings have been selected in step 5, tick one or both of the top row and left column boxes.  This uses the labels in these rows or columns to match data even in the order is not the same. 

9. Choose whether to Create a link to source data.  If this is not ticked then the answers are fixed and will not change if the underlying data does.  If it is ticked the data has a calculation behind it which links directly back to the original cells and the breakdown for each individual figure. 

Consolidate dialog in Microsoft Excel

This is a great little feature can certainly help data be swiftly consolidated in certain circumstances.

Further Reading

If you’ve enjoyed reading about the Consolidate feature in Microsoft Excel, there’s some other blogs below that you might find useful:

Want to learn more about Excel? Then email to discuss how I can help or have a look at the Excel Courses I run.