How to Merge Multiple Excel Files: A Complete Step-by-Step Guide

When we work with several books and sheets, there comes a time when we want to put things in order and gather everything in one place. Merge multiple Excel files It can save you time, reduce errors, and facilitate corporate reporting, especially if you manage branch-level expenses, inventories, or regional sales that you later want to consolidate globally.

Now, it is not always a good idea to mix everything into a single file; Excel allows you to link data between workbooks with formulas, which sometimes saves you from opening huge files and speeds up your work. In this guide, you'll see when you're interested in consolidating, how to combine by position or category, how to use Power Query to append or cross-reference data, a real-life example with CSV files by serial number, a VBA macro to merge workbooks in a folder, and, if you prefer, web tools to merge Excel on the fly (with its privacy pros and cons).

When to merge and when to link

Excel is a Swiss Army knife: it's equally useful for managing home accounting and for monitoring business metrics with thousands of rows, formulas, charts, and even external data. The more information you accumulate in a single book, the larger it will be and the longer it will take to open, something that is especially noticeable on computers with limited resources.

If yours is a large project with several areas, it may be worth leaving the sheets separate and pull formulas that reference other files. This way you reduce the number of visible tabs and still maintain a clean “command center.” But if you need a common overview or to prepare deliverables for management, it does make sense to unify everything into a single workbook (or consolidate into a single one). master sheet).

Consistency is also important: if each team names columns differently or places data in different positions, you'll have to decide whether to consolidate by position or by category. The consistency of labels and structure marks the difference between a clean merge and a collection of columns with null values.

In professional environments, a practical tip: when moving pages between books, it is wiser copy them instead of moving them to preserve the originals. And if you're going to reorganize a lot of tabs, remember to use Shift to select contiguous ranges and Ctrl to select individual sheets as you wish.

Native Excel methods for combining sheets and workbooks

Before getting into the nitty-gritty with Power Query and macros, it's worth remembering what the program itself offers. Excel allows you to transfer sheets from one workbook to another. with two clicks and also consolidate data from multiple sheets into one, either by position or category, depending on how they are structured.

Move or copy sheets between workbooks

Ideal for grouping tabs from multiple files into a single workbook. Works without relying on plugins and it is fast for a few files.

1. Open the source and destination books. Place yourself on the sheet you want to carry.
2. Right-click on the sheet tab and choose “Move or Copy.” You can also go to Home > Cells > Format.
3. Choose the destination book (existing or “new book”). Decide the insertion position with respect to existing leaves.
4. Select “Create a copy” to avoid losing the original sheet, and confirm with OK. Repeat for all sheets you need.
5. To move multiple tabs at once, select multiple tabs with Shift or Ctrl and repeat the process. It's a shortcut that saves a lot of time.

Consolidate by position

Use this method when the data in each sheet shares the same layout (same rows and columns in the same order). It is crucial that there are no blank rows or columns within the ranges.

1. Open each source sheet and verify that the table is in the same location and with the same shape. Homogeneity is everything here.
2. In the destination sheet, select the top left cell where you want the consolidated data to appear. That will be the anchor.
3. Go to Data > Consolidate. The box with the list of functions will open availables.
4. Under “Function,” choose how to consolidate (Sum, Average, etc.). Think about whether you need totals, averages, minimums...
5. In each source sheet, select the data range. The route and range will be added to “All references”.
6. Once you've added all the references, click OK. You will get a unified table according to the position.

Consolidate by category

Perfect when the leaves share the same row or column labels, but the data isn't in the same location. This is where the label text matters: if you put "Avg." in one sheet and "Average" in another, it won't match.

1. Open all source sheets. Check that the labels match exactly in name and spelling.
2. In the destination workbook, select the starting cell for the consolidation. Place the cursor where you want the summary.
3. Go to Data > Consolidate. Choose the consolidation function (Sum, Average…).
4. Mark where the labels are in “Use labels in”: top row, left column, or both. This tells Excel how to match categories.
5. Select the ranges of each sheet including the chosen labels. The routes will appear in “All references”.
6. Confirm with Accept to create the master sheet. Excel will join by category name, not by position.

Note: Consolidating and creating pivot tables are not the same thing. Pivot tables allow you to rearrange categories at will; consolidation fixes the outcome according to the labels and function chosen, without the analytical flexibility of a dynamic.

Join and Transform with Power Query

Power Query is Excel's modern "data engine." It lets you import, transform, append, and combine data from multiple sources, refresh with a click, and automate steps. If you handle large volumes or want repeatable processes, is your best ally.

To combine files from a folder into a single table, do this in a guided manner: Upload the folder, filter it, and use “Combine Files”.

1. Data > Get Data > From File > From Folder. Select the containing folder from your files.
2. You will see the list of files in the folder and subfolders. Check that the ones you need appear.
3. Click “Transform Data” to open Power Query with a view of all files. It is the “engine room”.
4. Filter columns like Extension or Folder Path to keep only the right types (for example, .xlsx or .csv). This is how you avoid mixing apples with pears.
5. Select the Contents column (binary, usually the first one) and go to Home > Combine Files. The “Combine Files” box will open..
6. Choose “Sample File” from the drop-down menu if you don’t want to use the first one. Power Query analyzes it to detect columns and connectors.
7. Optionally check “Skip files with errors” to exclude them from the result. Useful if there are files with faulty structure.
8. You accept. A query that consolidates data will be automatically generated of each file in a single table.

The result is not magic, but almost: Power Query creates auxiliary queries and steps based on the chosen command, including the application of common transformationsIf you add more files to the folder tomorrow, just Update to add them to the set.

• Automation: updates with new files without repeating the process.
• Ease: No programming required; everything is visual.
• Unlimited: Handles large volumes better than traditional copy/paste.

Is your case to merge two CSVs by a common column, for example? “Serial number” as a keyHere it is important to distinguish between “Append” and “Merge”:

• Append stacks tables one on top of the other. If the headers don't match 100%, you'll see many columns with null values. This isn't what you want for cross-referencing information by key.
• To combine joins tables by key, as in a union databases (left, inner, right…). This is appropriate for “join by Serial Number”.

Recommended steps for your example with two CSVs (one from Apple Business Manager with 13 columns and 8.743 rows, and one from MDM with 24+ columns and 6.012 rows): import both CSVs into Power Query, set correct data types and use “Combine Queries”.

1. Data > Get Data > From File > From Text/CSV, import ABM and the MDM. Open each one in the editor.
2. Verify that “Serial Number” is the same type on both (Text is usually the safest). Change type if necessary.
3. With the ABM query active, Home > Merge Queries > Merge Queries as New. Select MDM as the table to join.
4. Select the “Serial Number” column in both tables. Sets the union type:
   • Left outer (left): Keeps all ABMs and brings matches from the MDM.
   • Inner (intersection): only rows with serial number present in both.
   • Right/Full: other variants, depending on your needs.
5. Expand the resulting table column to bring in only the MDM columns you are interested in (check/uncheck). You avoid noise and redundant columns.
6. Apply extra transformations if applicable (remove columns, normalize labels). Then “Close and Load”.

If you tried Append and got a lot of columns with “null,” it was to be expected: you were stacking different structures. To cross by key, use Merge with the appropriate join. This works in O365 on both macOS (including Apple Silicon) and Windows, and you'll also have the advantage of being able to update in one click when new CSVs arrive.

Merge workbooks with a VBA macro

If you prefer not to rely on the interface and are a bit more comfortable with macros, you can automatically copy all the sheets from all the workbooks in a folder into a single file. The macro opens each workbook in read-only mode, copies its sheets and closes it, in a loop.

1. Open a new book (destination). Press ALT + F11 to open the VBA Editor.
2. Insert a Module (Insert > Module) and paste this code adjusting the path and file mask if applicable:

Sub UnirHojasDeCarpeta()
  Dim ruta As String, nombre As String
  Dim sh As Worksheet
  ruta = "C:\MisDatos\Origen\"
  nombre = Dir(ruta & "*.xlsx")
  Do While nombre <> ""
    Workbooks.Open Filename:=ruta & nombre, ReadOnly:=True
    For Each sh In ActiveWorkbook.Sheets
      sh.Copy After:=ThisWorkbook.Sheets(1)
    Next sh
    Workbooks(nombre).Close SaveChanges:=False
    nombre = Dir()
  Loop
End Sub

Run with F5 and wait for it to finish. You will get all the sheets in your destination book of the .xlsx files in the folder, inserted after the first tab. You can adapt the wildcard to “*.xlsm” or “*.xlsb,” and of course the path. Remember to enable macros and trust access to the VBA object model if your security is high.

Online solutions for merging Excel: ASPOSE, DOCSOSO, and GROUPDOCS

If you don't want to open Excel or you work from Mac, Linux, Android o iOS, you can use web fusion services. They are fast and cross-platform, but keep privacy in mind: when you upload spreadsheets with sensitive data, you run the risk of exposing information on third-party servers.

There are online applications that combine multiple Excel files and also allow you to save them in a multitude of formats. Among its typical functions you will find:

• Merge spreadsheets: XLSX, XLS, XLSB, XLSM, XLTX, XLT, XLTM, ODS.
• Save in the desired format: PDF, combine Excel with Word, DOCX, POWERPOINT, PPTX, XLS, XLSX, XLSM, XLSB, XLT, XLTX, XLTM, ODS, OTS, FODS, NUMBERS, ET, CSV, TSV, HTML, WEBP, SVG, TIFF, XPS, ZIP, MHTML, SQL, TEXT, TXT.
• Other advantages: fast way to merge, combine different formats in one, easily save as PDF, images or HTML, merge files Opendocument, choose the order of the joined files and decide if the result goes in multiple sheets or in a single one.

ASPOSE offers a suite of web-based document utilities. In your Excel tool you can upload files From your computer, use Dropbox, or paste a URL. You can choose whether you want to combine multiple sheets or dump everything into a single sheet; under "Save As," choose XLSX; press Merge, and in a few seconds, You will download the result or send it to Dropbox. It also supports working with other office formats (Word, PDF, images, etc.).

DOCSOSO is very straightforward: you log in, upload your Excel files, and click “Merge Excel.” It has a box to collect everything on a single sheet. if it suits you. For simple tasks, it's more than adequate.

GROUPDOCS also integrates multiple document utilities. You can select files from the explorer or drag and drop on the interface; then you press the merge button and, when finished, downloads the final file. Before deleting the originals, open the resulting document and confirm that the merge is as you expected; if not, you can always native Excel function or Power Query, which are more controllable.

Other simple methods and an “expert” tool

For specific cases, the traditional procedure works: copy and pasteOpen each workbook, Ctrl + A to select, Ctrl + C to copy, and paste into the destination sheet. Repeat. It's quick if you have two or three files; with dozens, it becomes tedious.

You can also rely on the Excel option “Move or copy" explained above: Select the sheets you're interested in and bring them into the final workbook without opening the Query Editor or programming anything. To reorganize views and keep the originals, select "Create a copy."

In the field of dedicated software, there are solutions that ensure the ability to combine several Excel without opening them beforehandSome present themselves as "the only tool on the market" capable of doing this with filters, advanced options, and a free trial mode so you can see how the merging process works before you decide.

1. Install the application and select the “Combine Excel” option. It is the entrance to the fusion wizard.
2. Import individual files or folders with “Add Files” and “Add Folders.” The tool will display the hierarchy.
3. Select the files you want to include and click “Next.” This is how you fine-tune the batch.
4. Choose between “Individual Worksheets” (multiple tabs) or “Single Worksheet” and define the output path. You decide the final structure.
5. Click “Merge” and wait for it to finish. When finished, click OK and check the result.

If you consider manual methods, remember their limits: They are not ideal for giant files, they require repeated steps, are time-consuming, require proficiency with Excel/VBA, and run the risk of losing hierarchies or data if you make a mistake. For large workloads, Power Query or an automated process (macro or dedicated tool) is often more reliable.

The key idea is that you have a range of options depending on your case: consolidate by position or category if the structure is aligned; moving/copying sheets if you reorganize workbooks; Power Query to append or merge by key with automatic updates; VBA macros when you want to bulk ingest dozens of files; and web services or specialized software if you prioritize speed and don't handle sensitive data. Choosing well will save you hours and headaches, and will allow you to keep reports under control without sacrificing performance or data quality.

Related article:

How to Integrate Excel into PowerPoint: Complete Guide and All Methods

Isaac

Passionate writer about the world of bytes and technology in general. I love sharing my knowledge through writing, and that's what I'll do on this blog, show you all the most interesting things about gadgets, software, hardware, tech trends, and more. My goal is to help you navigate the digital world in a simple and entertaining way.