Can You Merge Excel Spreadsheets? Methods That Work

How-to basics · Updated November 21, 2026

Yes. In Microsoft Excel for Microsoft 365 on Windows, you can combine spreadsheets by copying worksheets, appending rows with Power Query, joining matching records, or summarizing ranges with Consolidate. The right method depends on whether you want to stack records, bring sheets together, match information by a key, or calculate a summary.

What “merge spreadsheets” can mean

People use “merge” to describe several different tasks in Excel, and those tasks do not produce the same result. Decide what the finished workbook should look like before choosing a tool:

  • Put worksheets together: Move or copy tabs from separate workbooks into one workbook. The sheets remain separate.
  • Append records: Stack rows from multiple sheets or files into one longer table. Each source should have the same kinds of columns.
  • Join records: Add columns from one table to another by matching a shared value, such as a customer ID or product code.
  • Summarize values: Combine numeric data from ranges, often by adding corresponding values, rather than retaining every source row.

These meanings are easy to mix up. In particular, merging cells with Home > Merge & Center changes the layout of cells; it does not combine spreadsheet records. Joining two data tables is also different from appending them: a join matches keys across tables, while an append adds rows beneath existing rows.

Choose a method for the result you need

For a one-time task involving a few workbooks, copying worksheet tabs is often the simplest choice. If you need one clean table made from similarly structured lists, Power Query is generally easier to repeat and check. For a live formula-based result, a dynamic array formula may be suitable when the source ranges are controlled. Use Consolidate when the goal is a total or other summary across corresponding ranges.

Consider how often the source data changes and whether the merged result must preserve formulas, formatting, or only values. A copied sheet can preserve much of its layout and formulas, but it is not automatically a refreshed data connection. Power Query creates a repeatable import-and-transform process, while a formula result recalculates according to its references. These differences matter if a monthly report will be rebuilt many times.

Move or copy worksheets into one workbook

To bring entire sheets together, open the source and destination workbooks in Excel. In the source workbook, right-click a worksheet tab and choose Move or Copy. In the dialog, select the destination workbook under “To book,” choose where the sheet should appear, and select “Create a copy” if you want to keep the original sheet. Choose OK. Repeat for other sheets.

Check the copied sheets after moving them. Formulas may refer to other sheets or workbooks, named ranges may be workbook-specific, and charts or data connections can depend on their original sources. If you move a sheet instead of copying it, the source workbook no longer contains that sheet. Save the destination under a new name if you need to preserve an untouched original.

On Windows, Ctrl-click worksheet tabs to select multiple nonadjacent sheets, or Shift-click to select a range of tabs, then use Move or Copy. On Mac, use Command-click for nonadjacent tabs and Shift-click for a range; the tab context menu may be opened with a Control-click or a two-finger click, depending on the mouse or trackpad settings. Excel for the web has more limited sheet-management options than the desktop app, so use the desktop version if the Move or Copy command is unavailable.

Append tables with Power Query

Power Query is designed to import, shape, and combine data. It is a good fit when separate sheets or workbooks contain the same fields—for example, a sales list for each month with columns for Date, Item, Region, and Amount. Appending those lists creates one table with the records from each source. Columns are matched by their names, so consistent headings make the process more reliable.

For tables in the current workbook, first format each data range as an Excel table using Insert > Table, and give the tables clear names on the Table Design tab. Then use Data > Get Data > From Other Sources > Blank Query, or create queries from the tables using Data > From Table/Range. In the Power Query Editor, choose Home > Append Queries > Append Queries as New, select the tables or queries to append, and confirm. Review the preview for missing or unexpected columns, then choose Home > Close & Load to return the result to Excel.

For separate workbooks, use Data > Get Data > From File > From Workbook to connect to a workbook, select the relevant sheet or table in Navigator, and choose Transform Data if you need to inspect or clean it. You can create a query for each source and append them. When the source files are refreshed or replaced, use Data > Refresh All to update the query result, provided the connections still point to valid locations.

Power Query is not a magic repair tool. If a source calls a field “Customer ID” and another calls it “CustomerID,” Excel may treat those as different columns. Standardize headings and data types, remove repeated header rows, and check that each source represents the same kind of record before appending. Excel for Mac has Power Query features in supported Microsoft 365 versions, but availability and commands can differ by build; the Windows paths above may not match exactly. Excel for the web has a more limited Power Query authoring experience, so use desktop Excel if a required import or transformation command is missing.

Combine files from a folder

If each workbook in a folder follows the same layout—for example, one file per branch or month—you can use Power Query’s folder connector instead of importing every file separately. In Windows Excel, choose Data > Get Data > From File > From Folder, select the folder, and inspect the file list. Choose Combine & Transform Data to define how the files should be read and to review the resulting query before loading it.

This approach is useful for recurring reports: place new files in the source folder and refresh the query. It works best when the files have consistent column names and compatible layouts. Before combining, remove unrelated files, temporary copies, and old exports from the folder; otherwise, they may be included. Keep the folder path stable or update the query’s source if files move. A folder query is a connection to a location and its contents, not a permanent copy of every future file regardless of layout.

Join matching records by a key

Suppose one table lists orders and another lists customer regions. If both tables contain a unique Customer ID, you may want to add the region to each order rather than stack the customer list below the orders. In Power Query, load both tables as queries, then select Home > Merge Queries > Merge Queries as New. Choose the matching key column in each preview, select an appropriate join kind, and confirm. Expand the resulting table column to choose which fields to add.

The key needs careful checking. Duplicate IDs in the lookup table can produce multiple matches, and values that look identical may differ because one is stored as text and another as a number, or because of spaces. A left outer join is commonly used when you want to retain every row from the first table and add matching details when available. Review unmatched rows and duplicate keys rather than assuming a successful merge means every record matched. The query’s join kind determines which records remain in the output.

Use a formula for a simple stacked result

For a small, controlled set of ranges, the VSTACK function can place one range beneath another and spill the combined result into neighboring cells. It is available in Microsoft 365 and Excel 2024. It is not supported in older perpetual versions such as Excel 2019. Leave enough empty cells below and to the right of the formula for the result to spill.

With January data in A1:D100, including its header row, and February data in A1:D100 with the same four columns, enter the following in an empty cell. The first range supplies the header; the second starts at row 2 to avoid repeating it.

=VSTACK(January!A1:D100,February!A2:D100)

If January contains 99 data rows and February contains 99 data rows, the result has one header row plus 198 data rows: 199 rows total, assuming every row in those ranges is populated as expected. Adjust the ranges to match the actual data, or use Excel tables when the source lists grow and you want references that expand with them. VSTACK stacks ranges; it does not match rows by ID, remove duplicates, or clean inconsistent fields. For frequent imports or files whose sizes change, Power Query is usually easier to maintain.

Use Consolidate for summaries, not row-by-row merging

Excel’s Consolidate command combines values from ranges into a summary. Choose Data > Consolidate, select a function such as Sum or Average, and add the source references. If the ranges have matching labels, the “Top row” and “Left column” options can help match data by those labels. The exact choices depend on whether the source ranges share the same layout.

For example, several department sheets may show the same product names in the first column and monthly totals in adjacent columns. Consolidate can summarize corresponding values into a new range. It does not preserve every transaction as a separate row, so it is not the right choice for building a detailed master list. If you need an auditable record of every sale, append the source tables instead.

Prepare data and check the merged result

A few checks before and after combining can prevent subtle reporting errors. Keep a backup of the source workbooks, especially when editing or moving sheets. Confirm that column headings, date formats, units, and identifiers mean the same thing in every source. A column called “Amount” might represent dollars in one file and units sold in another; a successful append will not detect that difference.

  • Make sure each table has one header row and no blank title rows inside the data.
  • Check that IDs are consistent in type and spelling, and remove accidental leading or trailing spaces when appropriate.
  • Decide whether blank cells, duplicate records, and subtotal rows belong in the final output.
  • Compare source and result row counts, and check a few records from each source.
  • For numeric reports, compare totals before and after combining to catch omissions or duplicate imports.
  • Refresh a Power Query result and confirm it still reads the intended files and sheets.

Keep source data and the combined output clearly separated, such as in different worksheets or folders. This makes it easier to trace an unexpected result back to its origin. If the data contains confidential information, use a trusted local workbook or an organization-approved storage location and review who has access before sharing. Do not upload sensitive spreadsheets to an unfamiliar online merger just to avoid using Excel’s built-in tools.

Frequently Asked Questions

Can I merge two Excel files without copying and pasting?
Yes. Use Move or Copy to bring whole worksheet tabs into one workbook, or use Power Query to import and append data from both files. Power Query is preferable when the lists should become one table or the task will be repeated.

What is the difference between append and merge in Excel?
Append stacks rows from similar tables. Merge, in Power Query’s terminology, matches rows from two tables using one or more key columns and can add fields from the matching table. Choose based on whether you need more records or more columns.

Will merging spreadsheets keep formulas and formatting?
Copying a worksheet usually retains its formulas and much of its formatting, though references and workbook-specific features should be checked. Power Query loads transformed data into a result table; it does not reproduce the source sheets’ full layout or formulas as copied worksheets do.

Can Excel automatically combine files every month?
Power Query can connect to a folder of consistently structured files and update the result when you refresh the query. Keep the folder contents and file layouts predictable, and check the refreshed output when new files are added.

Why are some rows or columns missing after combining?
Possible causes include different column headings, a selected range that excludes data, filters or transformation steps, mismatched join keys, or files that do not follow the expected layout. Inspect the Power Query preview, source ranges, and join settings, then compare row counts with the original files.

Can I merge cells from different spreadsheets into one cell?
Excel’s Merge & Center combines selected cells’ display area, not their contents from separate workbooks. To bring records together, use a worksheet copy, append, join, or formula method; choose based on the structure you need.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.