Excel Consolidate

How to combine Excel files

Over 1.8 million professionals use CFI to learn accounting, financial analysis, modeling and more. Start with a free account to explore 20+ always-free courses and hundreds of finance templates and cheat sheets.

What is Excel Consolidate?

The Consolidate Function[1] in Excel allows an analyst to combine information from multiple workbooks into one place.  The Excel consolidate function lets you select data from its various locations and creates a table to summarize the information for you.

Before you go on to learn more about Excel consolidation, you may find our Excel Shortcuts Cheat Sheet helpful.

Keyboard Shortcuts Sheet

Looking to be an Excel wizard? Increase your productivity with CFI's comprehensive keyboard shortcuts guide.

How to consolidate data in Excel

We’ve created a step-by-step guide to help you create your own consolidation by combining similarly organized data across multiple worksheets and workbooks.

  1. Open all files (workbooks) that contain the data you want to consolidate.
  2. Ensure the data is organized in the same way (see example below).
  3. On the Data ribbons, select Data Tools and then Consolidate.
  4. Select the method of consolidation (in our example, it’s Sum).
  5. Select the data, including the labels, and click Add
  6. Repeat step 5 for each worksheet or workbook that contains the data you need included
  7. Check boxes “Top row”, “Left column”, and “Create links to data source” (note you don’t have to tick these boxes if you don’t want labels or don’t want live links) and click the OK button.

The screenshots below will help you see an example of how to use the Excel consolidate function:

Excel Consolidate

Excel Consolidate Example

How to Do Excel Consolidate

Excel Consolidate Output

Why use the data consolidation function?

There are many reasons a financial analyst may want to use the Excel consolidate function.  One example would be combining budgets from various departments into one company-wide budget.  This may be common for an analyst or manager working in financial planning and analysis (FP&A) or other accounting functions.

What if the data sources have different items?

The Excel consolidate function will still work if there are different labels in the left column. The key is to use labels carefully and ensure they are the same in each table (e.g., if you have a spelling mistake or slightly different version of the label, it will treat them as separate).

More Excel training

Thank you for reading CFI’s guide to the Excel consolidate function. Check out our free Excel crash course to learn more tips, tricks, and best practices in Excel.  We’ve also developed a large library of other resources any financial analyst will find useful for their financial modeling skills, including:

Article Sources

  1. Consolidate Function
0 search results for ‘