Merge multiple Excel files into one

The Combine Worksheets Toolkit lets you merge multiple Excel files into one without copying and pasting. It offers 3 tools to do that.

Copy Sheets copies worksheets or data from several workbooks into one. You can choose to arrange the copied datasets side by side or one under another, save the original headers, formulas, and even formatting.

Combine Sheets merges spreadsheets based on their column headers. You decide whether to bring the data together from all or specific columns.

Consolidate Sheets comes in handy when you need to merge and calculate numbers at the same time. The wizard consolidates numeric data using Excel summary functions (aggregation functions) such as SUM, COUNT, AVERAGE, MAX, MIN, etc.

You can merge sheets in Excel using VBA. But this approach requires some skills and has a number of limitations. Or, you can try combining data from multiple worksheets with Power Query. However, this way is rather difficult and requires a long learning curve. Finally, you can use Excel's built-in Consolidate tool. Compared to the first two methods, it's quite easy to use, but it works only for summarizing numeric data.

Our toolkit offers many different ways to merge Excel files of any structure and size.

To merge several Excel files into one, use the Copy Sheets Wizard:

1. Click Copy Sheets on the Ablebits Data tab.
2. Choose what to copy:
   • Sheets from each workbook to one sheet.
   • Sheets with the same name to one sheet.
   • Selected sheets to one workbook.
   • Data from the selected sheets to one sheet.
3. Select the worksheets and, optionally, ranges to copy.
4. Choose additional copying options.
5. Click Copy.

See a more detailed description here.

To combine data from multiple worksheets into one, use the Combine Worksheets Wizard:

1. Click Combine Sheets on the Ablebits Data tab.
2. Select the worksheets.
3. Choose the columns to combine.
4. Select additional options if needed.
5. Specify where you want to place the result.
6. Click Combine.

See a more detailed description here.

To merge and summarize numerical data from different workbooks, use the Consolidate Worksheets Wizard:

1. Click Consolidate Sheets on the Ablebits Data tab.
2. Select the worksheets for consolidation.
3. Select the function to process your data with.
4. Choose to consolidate by column or row label, or by position.
5. Decide where to place the result.
6. Click Consolidate.

See a more detailed description here.

You are free to merge as many files as you need as long as the result table doesn't exceed Excel's limit for cells. These limits depend on the version of Excel you use.

That depends on you, actually :) Our tools will keep the formatting of each original table if you choose to do so. Only the Consolidate Sheets Wizard won't be able to do that since it calculates numbers and creates a new summary report for you.