Python pandas Excel Merge: Join Workbooks and Sheets

How-to basics · Updated November 18, 2026

To merge Excel data with Python, read each worksheet into a pandas DataFrame with pd.read_excel(), then use pd.merge() to match rows by a shared key; use pd.concat() when you want to stack rows or columns instead. Save the result with DataFrame.to_excel(). The right method depends on whether your data needs matching, appending, or both.

What “merge” means in pandas

An Excel workbook stores information in worksheets, while pandas works with in-memory DataFrames. A pandas merge does not edit two worksheets in place: it combines the DataFrames created from them. The usual sequence is to load the sheets, check that their columns and key values are suitable, combine the data, review the result, and write it to a new workbook or sheet.

There are two operations that people often call a merge. A relational join matches records using one or more shared columns, such as CustomerID or OrderNumber. In pandas, that is normally pd.merge() or the DataFrame method .merge(). Appending one table’s rows beneath another is a different operation, usually done with pd.concat(). Choosing the wrong one can create repeated rows or put values in the wrong structure.

For example, an orders sheet might contain a customer ID and an order total, while a customer sheet holds each ID’s name and region. A left merge can keep every order and add the matching customer details. If an ID is absent from the customer sheet, its added details will be missing rather than causing that order to disappear.

Choose merge, join, or concat

Use pd.merge() when rows in two tables should be matched using column values. It is the clearest choice for Excel data with an explicit key column. Use DataFrame.join() when the relationship is based on indexes, or when index-based joining makes the code easier to read. Use pd.concat() when worksheets have the same kind of records and you want to append them vertically, or when you are combining aligned DataFrames horizontally.

  • Match related records: merge orders to customers using a customer ID.
  • Append similar records: stack January and February sales tables that share the same columns.
  • Combine by index: use join when the index, rather than a regular column, identifies corresponding rows.

For append operations, pd.concat([jan, feb], ignore_index=True) places the rows from the second DataFrame after the first and creates a fresh integer index. If the column sets differ, pandas includes the columns from both inputs and fills unavailable values with missing values. That behavior may be useful, but it can also expose inconsistent worksheet layouts that should be corrected before combining.

Load the Excel sheets and prepare the key

Install pandas and an Excel-reading engine in the Python environment if they are not already available. For modern .xlsx files, pandas commonly uses the openpyxl package for reading. Then load the worksheets by name so the code does not depend on their position in the workbook:

import pandas as pd

customers = pd.read_excel("sales.xlsx", sheet_name="Customers")
orders = pd.read_excel("sales.xlsx", sheet_name="Orders")

Excel’s displayed appearance is not a reliable guide to the values pandas reads. A customer ID can be stored as a number in one sheet and text in another, or an ID with leading zeros can be changed if Excel stores it numerically. Check the columns and sample values before merging with print(customers.dtypes), print(orders.dtypes), and head(). For identifiers that must remain text, provide an appropriate dtype when reading, for example dtype={"CustomerID": "string"}, provided the source values are represented appropriately.

Also check for spaces, inconsistent capitalization, blank keys, and different naming conventions. If one file calls the field Customer ID and another calls it ClientID, identify those columns explicitly rather than assuming pandas knows they represent the same thing. Avoid converting a meaningful identifier to a number merely to make a merge run: numeric conversion can remove leading zeros and can fail on nonnumeric codes.

Merge two Excel tables by a shared column

This example starts with three customers and three orders. The order for customer 104 has no corresponding customer record. The merge keeps all orders and adds customer names where a match exists:

import pandas as pd

customers = pd.DataFrame({
    "CustomerID": [101, 102, 103],
    "Name": ["Ava", "Ben", "Cara"]
})

orders = pd.DataFrame({
    "CustomerID": [101, 101, 104],
    "Amount": [25, 40, 12]
})

result = pd.merge(
    orders,
    customers,
    on="CustomerID",
    how="left",
    validate="many_to_one",
    indicator=True
)

print(result)

The result has three rows: two orders for customer 101 receive the name Ava, and the order for customer 104 has a missing name. Its indicator value is left_only; the matched rows have both. This is a useful pattern when the left table is the main record set and the right table provides descriptive details. The on argument names the shared key, and how states which records to retain.

Specify on rather than relying on pandas to infer the key. If on is omitted, pandas can use the intersection of column names shared by both DataFrames, which may include unintended columns. If the key has different names, use left_on="CustomerID" and right_on="ClientID". You can also pass a list, such as on=["StoreID", "Date"], when a record is identified by a combination of fields.

Understand the join types

The how setting determines which key records survive. A left join keeps every row from the left DataFrame and adds matching values from the right. A right join does the reverse. An inner join keeps only keys found on both sides. An outer join keeps keys found on either side, filling columns with missing values wherever a match is absent.

Choose based on the question the output must answer, not simply on which option produces the most rows. A left join is common for enriching a transaction list without losing transactions. An inner join is appropriate when unmatched records should be excluded, but it can conceal data-quality problems if those exclusions are unexpected. An outer join can help compare two lists and identify records present in only one source.

With indicator=True, pandas adds a column named _merge that labels rows as left_only, right_only, or both. Review those categories when checking completeness. If you use the indicator, avoid already having a conflicting _merge column, or choose a custom indicator column name.

Prevent duplicate keys from multiplying rows

A merge can return more rows than either input when a key occurs multiple times on both sides. This is not necessarily a pandas error: every matching combination is represented. If customer 101 appears twice in the customer table and twice in the orders table, matching on that ID can produce four combinations for that key. Such a result may inflate totals if the intended relationship was one customer record per ID.

Check the key’s uniqueness when the data model expects it. For example, customers["CustomerID"].duplicated().sum() counts duplicate customer IDs. The validate argument can enforce expected relationships: "one_to_one" requires unique keys on both sides, "one_to_many" requires unique keys on the left, and "many_to_one" requires unique keys on the right. A failed validation raises an error instead of silently accepting an unexpected relationship.

Missing values deserve special attention too. pandas merge can match null key values with other null key values, unlike the behavior many people expect from SQL joins. If blank identifiers should never be considered a match, inspect or remove rows with missing keys before merging, then account for those rows separately. Decide this deliberately; dropping them without review may discard records that need investigation.

Merge multiple sheets or workbooks

For several worksheets with the same layout, read each sheet and concatenate the DataFrames. If a workbook has a small, known set of sheets, load them by name and combine them explicitly. For many similarly structured worksheets, sheet_name=None makes read_excel() return a dictionary of DataFrames, one per sheet; concatenate the dictionary’s values after confirming that the sheets really share a schema.

book = pd.read_excel("monthly_sales.xlsx", sheet_name=None)
all_sales = pd.concat(book.values(), ignore_index=True)

When combining separate workbooks, build a list of DataFrames and concatenate it, or merge selected tables one at a time on their keys. Keep track of where each row came from if that matters. For example, a source column can record the filename or worksheet name before concatenation. Do not concatenate unrelated sheets merely because their column labels happen to overlap; first decide whether their rows represent the same kind of entity.

For a chain of relational merges, name each intermediate result clearly and validate each relationship. This makes it easier to see whether a row increase occurred after joining products, customers, or another lookup table. It is often safer to inspect a small sample and row counts at each stage than to write one long expression that is difficult to audit.

Handle common errors and inspect the result

A “merge on object and int64 columns” error usually means the key columns have different data types. Inspect both dtypes and normalize them consistently, taking care not to destroy leading zeros or meaningful text. A result with unexpected missing values often indicates different key spelling, whitespace, case, or formatting, rather than a failure of the merge operation itself. Compare unmatched keys using an outer merge and an indicator to locate the discrepancy.

Before trusting a result, check its row count, key coverage, and representative records. Ask whether the count is expected for the chosen join and key relationship. Confirm that important totals have not changed because duplicate keys multiplied records. Review missing values in the newly added columns and check whether each unmatched key has a sensible explanation. These checks are particularly important when the output feeds a report or another automated process.

Column names that occur in both inputs but are not merge keys may receive suffixes such as _x and _y. That signals that both tables supplied a column with the same name. Rename columns before merging or pass meaningful suffixes to make the output understandable; do not assume the two versions contain identical values.

Write the merged data back to Excel

To create a new workbook, use DataFrame.to_excel(). The index=False argument prevents pandas from writing the DataFrame index as an extra worksheet column. For example:

with pd.ExcelWriter("merged_output.xlsx") as writer:
    result.to_excel(writer, sheet_name="Merged", index=False)

Use a new output filename when the original workbook should remain untouched. Writing a new Excel file is different from editing an existing workbook while preserving its formatting, charts, macros, and other workbook features; pandas is primarily designed to read and write tabular data. If those Excel-specific features must be retained, check the capabilities and limitations of the chosen Excel-writing library before changing the file. A worksheet is also limited to 1,048,576 rows in Excel, so an output that exceeds the worksheet limit cannot fit on one sheet.

For a result that needs both matched and unmatched review, write separate DataFrames to separate worksheet names in the same writer context. For example, filter the indicator column into matched and unmatched records before writing. This can make quality checks easier than placing every record in a single output sheet, but retain the original merged result or a clear audit trail if the categories matter downstream.

Frequently Asked Questions

How do I merge two Excel files in pandas?
Load each file with pd.read_excel(), specify the matching key and join type in pd.merge(), inspect the result, and save it with to_excel(). Use concat() instead when you want to append similarly structured rows.

What is the difference between merge and concat in pandas?
merge() matches records using key values, like a database join. concat() combines DataFrames along an axis, commonly stacking rows. They solve different problems, even though both can combine data loaded from Excel.

How can I merge when the key columns have different names?
Use left_on and right_on, such as pd.merge(left, right, left_on="CustomerID", right_on="ClientID"). Check that the two columns have compatible values and data types before joining.

Why did a pandas merge create extra rows?
One or both inputs probably contain repeated values for the merge key. Each matching combination can appear in the result. Check duplicates and use the appropriate validate setting to enforce the relationship you expect.

Can pandas merge Excel sheets in the same workbook?
Yes. Read each sheet with pd.read_excel(), using the same filename and different sheet_name values, then merge the resulting DataFrames by their shared key.

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.