Yes. In Excel for Microsoft 365 on Windows, you can merge two spreadsheets by copying worksheets, stacking rows with Power Query, or matching records between tables with a lookup or a Power Query merge. The right method depends on whether you want to combine whole sheets, append similar lists, or bring related columns together.
What “merging” spreadsheets can mean
People use “merge” to describe several different jobs, and choosing the wrong one can produce a workbook that looks combined but contains incorrect or duplicated data. First decide what the finished result should look like.
- Combine worksheets: Put both sheets in one workbook while keeping them as separate tabs. This preserves their layouts and is often the simplest choice for unrelated information.
- Append rows: Put records from one list under records from another list. This works when both lists have similar columns, such as monthly sales with the same headings.
- Match records: Use a shared identifier, such as an order number or customer ID, to add columns from one table to another. This is useful when one sheet has customer details and the other has orders.
- Consolidate figures: Combine totals from similarly arranged ranges, such as summing the same budget categories across departments.
These operations are not interchangeable. Appending two tables does not match customers; it simply adds rows. A lookup or join does not necessarily retain every row from both lists. Identify the intended result before changing either source file.
Prepare the workbooks before combining them
Make backup copies of both files and check their structure. A clean source makes the merge easier to verify and reduces the chance of silently losing information. For lists, use one header row, one record per row, and one type of value per column. Remove blank rows within the data and check that headings are consistent, including spelling and spaces.
For a row append, the two lists should describe the same kind of record. For example, both might contain Date, Product, Region, and Sales. The columns do not have to be in the same order when using Power Query, but matching names and meanings make the result clearer. If one sheet calls a field “Client ID” and the other calls it “Customer ID,” decide whether those are truly the same field before renaming anything.
For a record match, inspect the key column in both files. IDs should have compatible formats: the number 123 and the text value “123” may not match as expected. Check for duplicate keys, leading or trailing spaces, and missing IDs. A key that is not unique can cause a lookup to return only one of several possible records, so determine how duplicates should be handled.
Append similar lists with Power Query
Power Query is a good choice for combining repeated exports or periodic lists because it can save the steps as a query and refresh the result later. In Excel for Microsoft 365 on Windows, first save each source workbook. In the destination workbook, use Data > Get Data > From File > From Workbook, choose the first file, select the relevant sheet or table in Navigator, and select Transform Data. Repeat for the second file so both sources appear as queries.
In the Power Query Editor, select Home > Append Queries > Append Queries as New. Choose the two tables or queries to append and confirm. Power Query aligns columns by their header names, rather than simply placing the first column from one source beneath the first column from the other. If a column exists in only one source, the other source’s rows will have blank values for it. Review the preview, then select Home > Close & Load to put the result into Excel.
For example, append January and February sales tables that both use Order ID, Date, and Amount. The result is a longer table with records from both months. Appending does not remove duplicate orders automatically, so check whether the sources overlap. When source files change, refresh the query using Data > Refresh All. A refresh can fail if a file moves, its name changes, or its structure changes, so keep the source location stable or update the connection.
Match related records with a lookup
If one list should gain information from another, match them on a shared key. For a one-column return in Microsoft 365, XLOOKUP can find a key and return a related value. Suppose the destination sheet has an order’s customer ID in A2, while a sheet named Source has customer IDs in A2:A4 and corresponding totals in C2:C4. If the Source IDs are 101, 102, and 103, and the totals are 240, 175, and 310, respectively, the formula in the destination cell is:
=XLOOKUP(A2,Source!$A$2:$A$4,Source!$C$2:$C$4,"Not found")
If A2 contains 102, this returns 175. The fourth argument supplies a result when there is no match. XLOOKUP is available in Microsoft 365 and Excel 2021 or later; older Excel versions may require another approach, such as INDEX and MATCH. Confirm that each key is unique if you expect exactly one result: XLOOKUP returns a matching result, not a report of every duplicate.
A formula-based merge keeps the returned values linked to the source cells while the formula remains in place. If you need a fixed snapshot, copy the results and use Home > Paste > Paste Values. For many columns or large recurring imports, Power Query’s merge operation is often easier to manage than filling numerous lookup formulas.
Join tables with Power Query
Power Query can also join tables side by side using a shared column. Import both sources as queries, then in the Power Query Editor select one query and use Home > Merge Queries or Home > Merge Queries as New. Choose the second query, select the matching column in each preview, and choose a join kind. The column values and data types need to agree for matches to work reliably.
A Left Outer join keeps every row from the first table and adds matching information from the second; unmatched rows remain, with blank values for the added fields. An Inner join keeps only rows that have a match in both tables. Other join kinds are available for different requirements. After merging, select the expand button on the new column to choose which fields to bring into the result. Check the row count and unmatched records before loading it.
For instance, joining an orders table to a customer table on Customer ID can add customer names to orders. If a customer ID appears more than once in the customer table, the join may create multiple matching rows for an order. Resolve duplicate or conflicting customer records first if the intended result is one customer per order.
Copy sheets or consolidate matching ranges
If the goal is simply to keep two sheets together, open both workbooks, right-click the sheet tab you want to move, choose Move or Copy, select the destination workbook, and check Create a copy if the original should remain unchanged. This copies a worksheet rather than combining its rows with another sheet. Review formulas, charts, named ranges, and links afterward; references to other sheets or workbooks may still point to their original locations.
For similarly laid-out ranges where you want totals rather than a longer list, Excel’s Consolidate feature may fit. On Windows, select the destination cell and use Data > Consolidate. Choose a function such as Sum, add each source range, and decide whether to use labels in the top row or left column when appropriate. This is designed for summarizing corresponding ranges, not for joining individual records by a unique ID. Always verify that the selected references cover the intended cells.
Common problems and how to check the result
Some merge errors are visible; others appear plausible until someone compares the result with the source. Use checks that fit the operation rather than assuming a successful command means the data is correct.
- Rows disappear: An inner join excludes records without matches. Choose a left or full outer join if retaining unmatched records is important, and inspect the join kind before loading.
- Rows multiply: Duplicate keys in the lookup table or joined table can create ambiguous results or repeated joined rows. Count duplicates and decide which record is authoritative.
- Values do not match: Check text-versus-number formats, extra spaces, inconsistent punctuation, and leading zeros in IDs. Do not convert identifiers carelessly; a code such as 0017 may not be equivalent to 17.
- Columns are blank: In an append, headings that differ are treated as separate columns. Standardize column names and check the query preview for null values.
- Unexpected totals: Compare source and output row counts, and compare sums for important numeric fields. A change in totals may indicate duplicates, excluded rows, or an incorrect column mapping.
Keep a copy of the original data and document the matching key, join type, and any filters. If the output will support reporting or operational decisions, sample-check records from both sources and test unmatched cases, not just the first few successful matches.
Which method should you choose?
Use the simplest method that produces the structure you actually need. Copy tabs when you only need both sheets in one file. Append with Power Query when the files contain similar records and you want one longer list that can be refreshed. Use XLOOKUP for a straightforward formula-driven return from a small related table, or Power Query Merge for repeatable joins involving multiple fields or larger imports. Use Consolidate for corresponding totals, not detailed record-level matching.
Excel for the web supports many workbook tasks, but Power Query authoring and refresh capabilities can vary by platform and connection type. On Mac, ribbon labels and available Power Query features may differ from Windows. If a workflow depends on a particular connector or refresh option, confirm that it is available in your Excel version before relying on it.
Frequently Asked Questions
Can I merge two Excel files without losing the original data?
Yes. Save copies first, then import the data into a new workbook or use Move or Copy with the copy option enabled. Avoid overwriting source files until you have checked the result.
Can Excel merge two spreadsheets automatically?
Power Query can repeat an append or merge when you refresh its query, provided the source files and their structure remain accessible. Excel will not know which operation you intend until you configure the query and specify the relevant columns.
What is the difference between append and merge in Excel?
Append stacks rows from tables with similar fields. Merge joins tables using one or more matching columns, adding related fields or filtering records according to the selected join kind.
Can I combine spreadsheets with different column orders?
Yes. Power Query append aligns fields by column names, so matching headers are important even if their order differs. Review the preview and standardize headings when the same field has different names.
Why are some records missing after a merge?
The join type may exclude unmatched records, or the key values may differ because of formatting, spaces, missing IDs, or inconsistent text. Check the join kind and inspect unmatched rows in the query before loading.
Related
- Excel Border Shortcuts: Apply, Change, and Remove Borders
- Bluebeam to Excel: Export Markups and Quantities
- Bluebeam Quantity Link to Excel: Setup and Limits
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Merge Across vs. Merge Cells: Key DifferencesMerge Across makes a separate merged cell for each row in your selection, while Merge Cells combines the…
- Excel Text to Columns: How to Split DataIn Excel for Microsoft 365 on Windows, select the cells you want to split, then choose Data >…
- How to Unmerge Cells in Excel and Keep DataTo unmerge cells in Excel for Microsoft 365 on Windows, select the merged cell or range, then choose…
- Insert One Row Above in Excel: Shortcut and MethodsIn Excel for Microsoft 365 on Windows, select the row where the new row should go, then press…
- Delete Duplicates in Excel: Keep One Copy of Each RowTo delete duplicates in Excel and keep one copy, select the entire data range, choose Data > Remove…
- Company Cash Flow Template Excel: Build and Use ItA company cash flow template in Excel is a worksheet that tracks when money is expected to enter…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.