The fastest way depends on how many files you have and whether the data is organized the same way

If you have two or three Excel files with the same columns, copy and paste is usually quickest — open both files side by side, select all the data in one, paste it into the other, then repeat for the rest. If you have more than three files, or if the files are large, use Excel's built-in consolidation tools instead. Excel can pull data from multiple files automatically without you copying anything manually.

The method you choose also depends on whether you want the data combined into a single sheet or kept separate by file. Most people want everything in one sheet, which is what this guide covers. If your files have different column layouts, you will need to rearrange them first so the columns match — Excel cannot guess which column in File A corresponds to which column in File B.

Key Takeaways

  • Copy and paste works for two to three files with matching columns, but becomes slow and error-prone with more files.
  • Excel's Data > Consolidate tool can combine data from multiple files automatically, as long as the column headers and structure are identical.
  • Power Query (in Excel 2016 and newer) can combine files from a folder without opening each one individually.
  • Before consolidating, check that all files have the same column names and data types in the same order.
  • After consolidating, sort and remove duplicates to catch any rows that were added twice by mistake.

Preparing your files before you consolidate

Open each file and check the column headers — they must match exactly, including capitalization and spacing. If one file has "Date" and another has "date", Excel will treat them as different columns. Rename any headers that do not match. Also check that the data types are the same: if one file has dates formatted as "01/15/2024" and another has "January 15, 2024", you may need to reformat them to match.

Delete any rows you do not need, such as blank rows or summary rows at the bottom of each file. These will be copied into your combined file and clutter your data. If a file has multiple sheets, decide which sheet contains the data you want to consolidate, and delete or ignore the others.

Save all files in the same folder and in the same format — either all .xlsx or all .xls. If you mix formats, some tools may not recognize all of them. Close all the files except the one where you want the combined data to end up. This is usually a new blank file or the largest file.

Copy and paste for small numbers of files

Open the first file you want to copy from. Select all the data by clicking the cell in the top-left corner of your data, then pressing Ctrl+Shift+End (on Windows) or Command+Shift+End (on Mac). This selects from where you clicked to the last cell with data. Copy this selection with Ctrl+C or Command+C.

Switch to the file where you want the data to go. Click the cell where you want the data to start — usually A1 if the file is empty. Paste with Ctrl+V or Command+V. The data will appear in your file. Repeat this process for each additional file, but click the cell below the data you just pasted before pasting the next file, so you do not overwrite anything.

After pasting all files, check for duplicates. Click any cell in your data, then go to the Data tab and select Remove Duplicates. Excel will show you which columns it will check — usually all of them is correct. Click OK and Excel will delete any rows that are identical to another row.

Using Excel's Consolidate tool for larger datasets

The Consolidate tool is built into Excel and works when all your files have the same structure. Open the file where you want the combined data to appear. Click a blank cell where you want the consolidated data to start — usually A1. Go to the Data tab and click Consolidate (on some versions of Excel, this is under Data > Tools > Consolidate).

A dialog box will open. In the "Function" dropdown, select "Sum" if you want to add up numbers, or "Count" if you just want to count rows. For most consolidation tasks, "Sum" is the right choice. Leave the other settings as they are for now.

Click the button next to "Reference" — it looks like a small grid icon. This lets you select the data from another file. Navigate to and open the first file you want to consolidate. Select all the data including headers, then click the grid icon again to return to the dialog. The file path and range will appear in the Reference field. Click Add to add this file to the list. Repeat for each additional file.

Before you click OK, check the box next to "Use labels in first row" if your data has headers. This tells Excel to match columns by name instead of by position. Click OK and Excel will combine all the data into your current file.

Using Power Query to combine files from a folder

Power Query is a tool in Excel 2016 and newer that can combine all files in a folder without you opening each one. Go to the Data tab and click Get Data (or New Query, depending on your Excel version). Select "From File" and then "From Folder".

Navigate to the folder where all your Excel files are stored and click Select Folder. Excel will show you a list of all files in that folder. Click the Combine button and select "Combine & Load" or "Combine & Edit". Excel will ask you which sheet to combine from — select the sheet that contains your data.

Power Query will combine all files and load the data into a new sheet. You can then save this as a new file or keep it in your current workbook. Power Query is faster than the Consolidate tool for large numbers of files, but it requires all files to have identical structure and the same sheet names.

Fixing common problems after consolidation

If your consolidated data has blank rows or columns, go to the Data tab and click AutoFilter. Click the dropdown arrow in any column header and uncheck "Blanks" to hide empty rows. You can then delete these rows if you want, or leave them hidden.

If numbers appear as text instead of numbers, select the column, go to the Data tab, and click Text to Columns. Click Next twice, then make sure the column format is set to "General" or "Number". Click Finish and Excel will convert the text to numbers.

If you notice duplicate rows that were not caught by Remove Duplicates, it usually means the rows are not identical — one might have extra spaces or different capitalization. Select the data, go to Data > Remove Duplicates again, and this time uncheck any columns that are not important for identifying duplicates. This will catch more duplicates but may also remove rows you wanted to keep, so check the results carefully.

Keeping your consolidated file updated when source files change

If the original files change and you need to update your consolidated file, the easiest approach is to delete all the consolidated data and repeat the consolidation process. This ensures you do not accidentally keep old data alongside new data.

If you used Power Query, you can refresh the data instead. Go to the sheet with the consolidated data, click any cell in the data, then go to the Data tab and click Refresh. Power Query will re-read all the files in the folder and update your consolidated sheet. This is faster than consolidating manually each time.

If you used the Consolidate tool, you will need to repeat the process each time the source files change. There is no automatic refresh option for the Consolidate tool, so manual consolidation is the only way to stay current.

Frequently Asked Questions

Can I consolidate files that have different column orders?

No, not without rearranging them first. Excel matches columns by position, not by name, unless you use the Consolidate tool with "Use labels in first row" checked. If your files have columns in different orders, open each file and move the columns so they are in the same order before consolidating.

What if one file has more rows than the others?

That is fine. Copy and paste, Consolidate, and Power Query all handle files with different numbers of rows. The consolidated file will simply have more rows. If you want to track which file each row came from, add a new column to each file before consolidating and fill it with the file name.

Can I consolidate files that are stored on OneDrive or Google Drive?

Copy and paste works if you have the files open in Excel. Power Query works with OneDrive files if you save them as .xlsx format. The Consolidate tool requires files to be on your computer or a network drive, not in cloud storage. Download the files to your computer first if you want to use Consolidate.

How do I consolidate files if they have different sheet names?

Rename all the sheets to the same name before consolidating. If File A has a sheet called "Sales" and File B has a sheet called "Data", rename one of them so they match. Power Query will then recognize them as the same sheet and combine them correctly.

What is the maximum number of files I can consolidate at once?

There is no hard limit, but consolidating more than 20 files manually becomes slow. Power Query can handle hundreds of files from a folder. If you have a very large number of files, Power Query is the best option.