The fastest way depends on which program you use and how many files you have

If you have two or three Excel files and they use the same column layout, the quickest route is usually to copy and paste the data by hand — open both files side by side, select the data from the first file, paste it into the second, then save. This takes five to ten minutes and requires no new software.

If you have more than three files, or if the files are large, manual copying becomes tedious and error-prone. At that point, your choice depends on what program you already have. Microsoft Excel has a built-in consolidation tool. Google Sheets can pull data from multiple spreadsheets using a formula. LibreOffice Calc, the free alternative, works similarly to Excel. Each method has different privacy implications depending on whether your files live on your computer, in cloud storage, or on a company server.

Key Takeaways

  • Copy and paste works for two or three files with matching columns and takes less time than learning a new tool.
  • Excel's Data > Consolidate feature lets you combine up to 255 files at once without moving data between programs.
  • Google Sheets formulas like IMPORTRANGE pull data from multiple sheets, but require all files to be in your Google Drive and shared with your account.
  • If your files contain sensitive information, merging on your own computer (Excel or LibreOffice) keeps data off cloud servers, while Google Sheets stores everything in Google's data centers.
  • Before merging, check that column headers match across all files — misaligned columns are the most common reason merges fail or produce garbage data.

Merging files in Microsoft Excel using the Consolidate tool

Open the Excel file where you want the merged data to appear. This becomes your destination file. Go to the Data tab in the ribbon at the top, then click Consolidate (it may be under Data Tools or a dropdown menu, depending on your Excel version). A dialog box opens asking you to specify the ranges you want to combine.

Click Browse next to the first Reference field, then navigate to and select the first file you want to merge. Excel will ask you to highlight the cells containing your data — usually this is the entire data range including headers. Click and drag to select, then press Enter. Repeat this step for each additional file. When you have added all files, click OK. Excel combines the data into your destination file.

This method works on your computer only — the files stay where they are, and Excel does not upload them anywhere. If your files are stored on OneDrive or SharePoint, Excel can still access them, but the consolidation happens locally on your machine. The merged result stays in the file you opened, so save it when you are done.

Merging files in Google Sheets using IMPORTRANGE

If your files are already in Google Drive, you can use a formula to pull data from multiple sheets into one. Open the destination sheet (the one where you want all data to appear). Click on a cell where you want the first file's data to start, then type a formula like =IMPORTRANGE("spreadsheet_url", "Sheet1!A:Z"). Replace "spreadsheet_url" with the actual URL of the file you want to import, and "Sheet1!A:Z" with the sheet name and column range you need.

Google Sheets will ask you to authorize the connection the first time you use it. Click Allow. The data appears in your destination sheet. If you need data from a second file, click a cell below the first import and repeat the formula with the second file's URL. The formulas update automatically if the source files change, which is useful if you are working with live data.

The privacy trade-off: all your files must be stored in Google Drive, and Google's servers process the formulas. If your data is sensitive or your organization has rules about cloud storage, this method may not fit your comfort level. Google can see the contents of your spreadsheets while the formulas run, though Google's terms say they do not use this data for advertising or other purposes.

Merging files in LibreOffice Calc (the free option)

LibreOffice Calc, which you can read free from libreoffice.org, works much like Excel. Open the file where you want the merged data. Go to Sheet > Link to External Data, then select the files you want to combine. LibreOffice creates links to those files rather than copying the data outright, which means if the source files change, your merged file updates automatically.

If you prefer a copy rather than a link, use the same copy-and-paste method described above — it works identically in LibreOffice. Like Excel, LibreOffice runs on your computer, so your data never leaves your machine unless you explicitly save the file to cloud storage. This is the most private option if you are working with sensitive information and want to avoid any cloud processing.

Preparing your files so the merge does not fail

Before you start merging, open each file and check that the column headers are identical. If one file has "Date" and another has "date" or "Date Created", the merge will treat them as separate columns and your data will not line up correctly. Rename headers to match exactly — capitalization matters.

Check that the data types are consistent too. If one file stores dates as "01/15/2024" and another stores them as "January 15, 2024", the merge will work, but sorting or filtering later may behave unexpectedly. Decide on one format and convert the others before merging. Similarly, if one file has blank rows between data sections and another does not, remove the blank rows first — they can confuse consolidation tools into skipping data.

If your files have subtotals or summary rows at the bottom, move those to a separate sheet or delete them before merging. Consolidation tools often treat subtotals as data and include them in the merge, which inflates your totals. A few minutes spent cleaning up headers and removing junk rows saves you from spending an hour fixing the merged result.

What to do if the files have different structures

If your files do not use the same columns — for example, one file tracks sales by region and another tracks sales by product — you cannot use the straightforward consolidation tools. You have two options: restructure the files to match before merging, or use a pivot table or database tool to combine them intelligently.

Restructuring is usually faster if you have only two or three files. Open each file, add or remove columns as needed so they all match, then use the consolidation method above. If you have many files with different structures, or if restructuring would take hours, consider using a free tool like OpenRefine (openrefine.org) or a paid service like Zapier. These tools can map mismatched columns and combine files automatically. The privacy cost is that your data goes to a third-party server, so only use this route if your data is not sensitive.

Saving and organizing your merged file

Once the merge is complete, save your file with a clear name that indicates it is the merged version — something like "Sales_2024_Merged" rather than "Data" or "Final". If you used formulas (Google Sheets or LibreOffice links), the file will update automatically when source files change. If you used copy-and-paste or Excel Consolidate, the merged file is a snapshot — it will not update if the originals change.

Decide whether you need to keep the original files. If the merge is a one-time task, you can delete the originals once you have verified the merged file is correct. If you need to re-merge periodically (for example, monthly sales reports), keep the originals in a folder labeled "Source Files" so you know not to edit them directly. Any changes should go into the merged file or back into the source files before the next merge.

Frequently Asked Questions

Can I merge files that are stored in different locations (some on my computer, some on OneDrive)?

Yes, Excel and LibreOffice can both access files from different locations. When you browse for files to consolidate, navigate to each location separately. Excel will handle the rest. Google Sheets requires all files to be in your Google Drive, so you would need to move files there first or use a different method.

What happens if I merge the same file twice by accident?

The data from that file will appear twice in your merged result. Check your consolidation settings or formula before running the merge, and verify the result by counting rows or spot-checking data. If you have already merged, undo the action (Ctrl+Z or Cmd+Z) and try again.

Is it safe to merge files that contain passwords or sensitive information?

If you use Excel or LibreOffice, the merge happens on your computer and the data stays there. If you use Google Sheets, the data is stored in Google's data centers. If your files contain passwords, do not include those columns in the merge — remove them first. If the files contain personal information like Social Security numbers or medical records, check your organization's data handling rules before merging.

Can I merge files with different numbers of rows?

Yes. Consolidation tools and formulas handle files of different sizes. If one file has 100 rows and another has 50, the merged result will have 150 rows (assuming no duplicate headers). Just make sure the column headers match across all files.

What if I need to merge files regularly, like every month?

Set up the consolidation or formula once, then save the destination file as a template. Each month, open the template, update the source file references if needed, and run the consolidation again. Google Sheets formulas update automatically, so you only need to refresh the sheet. Excel Consolidate requires you to re-run the tool each time, but the process takes just a few clicks once you have done it once.