How to Automate Consolidation in Excel?

Get FREE Advanced Excel Exercises with Solutions!

This article illustrates how to automate consolidation in Excel. You can consolidate data from different worksheets or files in Excel. Data consolidation using Excel is very useful to accumulate data, create sales reports, and so on. It is necessary to automate consolidation in Excel to update the consolidated data automatically when the source data changes. Follow the article to learn how to do that in Excel.

How to Automate Consolidation in Excel: with Easy Steps

Follow the steps below to automate data consolidation in Excel.

๐Ÿ“Œ Step 1: Prepare Source Data

  • Assume you have the following dataset. Here, the worksheets Y2021 and Y2022 contain the sales of the first quarter of the respective years.
  • If the top row and left column are the same across all sheets, then the values will be added to the same cells. Otherwise, the data will be added to the adjacent rows or columns.
  • Here, the worksheets are in the same workbook (Automate Consolidation). It is better to open all spreadsheets before data consolidation if you want to do that from different files.

๐Ÿ“Œ Step 2: Use the Consolidate Feature

automate consolidation in excel

๐Ÿ“Œ Step 3: Choose Function & Add References

  • Next, choose the function for consolidation from the dropdown list. After that, click on the upward arrow to add the references for the source data to consolidate.

  • Then go to the first source data (Y2021) and select the range within the data that you want to consolidate. Next, click on the downward arrow.

  • Now, you must click on the Add button to add the reference. Then, click on the upward arrow again to add more references.

  • After that, go to the second source of data (Y2022). Then, select the range within the data that you want to consolidate, as in the earlier method. Next, click on the downward arrow.

๐Ÿ“Œ Step 4: Automate Consolidation

  • After that, click on the Add button as earlier to add this reference. You will see a list of the added references in the Consolidate dialog box.
  • Then you need to check the checkboxes for the Top Row and the Left column labels. You can keep them unchecked if your dataset has no labels.
  • Now the most important thing to automate consolidation is to check the โ€œCreate links to source dataโ€ Otherwise, the consolidated data wonโ€™t get updated if the source data changes.
  • After that, press the OK button.

automate consolidation in excel

๐Ÿ“Œ Step 5: Observe the Results

  • Finally, you will see the following results. Notice that some groups have been created automatically, as there are some + signs at the left.

  • Expand the groups to see the source data and their locations. Now, any changes made in the source data will automatically update the consolidated data. Because you can see that the consolidated data is directly linked to the source data.

automate consolidation in excel

Things to Remember

  • You must check the โ€œCreate links to source dataโ€ checkbox to automatically update the consolidated data with any changes made to the source data.
  • If you need to add more rows or columns to the source data in the future, then add the labels for those rows and columns before consolidation. Then, populate the cells in the respective rows and columns with zeros. If you update those cells containing zeros in the future, the consolidated data will also update.

Download Practice Workbook

You can download the practice workbook from the download button below.


Now you know how to automate consolidation in Excel. Do you have any further queries or suggestions? Please let us know in the comment section below.

Related Articles


What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Shamim Reza
Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF