Python vs Excel for merging files: when pandas is worth it

Side by side

Where Excel wins

For a few files of tens of thousands of rows, Excel is quicker from start to finish. Power Query's From Folder connector appends every file in a directory, its Merge Queries joins on a key, and the steps refresh with one click. Crucially, the person who inherits the workbook can see and edit each step without learning a language. See combining files from a folder with Power Query.

• No installation or IT request in most organisations.
• The result sits in a workbook where people already build pivots and charts.
• The step list documents what was done.

Excel is also the better place for the last mile: once the data is merged, people want to filter it, add a calculated column, build a pivot and send it on. Doing that in Python means writing more code or exporting to Excel anyway, which adds a step, so for a report that ends in a workbook, starting there is often simplest.

Where pandas wins

pandas handles files that would not fit in a worksheet, hundreds of files with inconsistent columns, and logic such as “take the latest row per customer, then join, then flag mismatches” in a few lines. Scripts can run from a scheduler at 6am with nobody watching and write the result anywhere.

import glob
import pandas as pd

sales = pd.concat(
    (pd.read_csv(f).assign(source=f) for f in glob.glob("exports/*.csv")),
    ignore_index=True,
)
customers = pd.read_excel("customers.xlsx")

merged = sales.merge(customers, on="customer_id", how="left", indicator=True)
print(merged["_merge"].value_counts())   # left_only = sales with no customer
merged.to_csv("merged.csv", index=False)

The indicator=True column is the piece Excel users miss most: it tells you exactly which rows matched. More patterns are in merging DataFrames in pandas.

Worked example: 36 monthly exports

Three years of monthly sales exports, about 40,000 rows each, need appending and joining to a customer table.

In Excel you can still do this by loading the Power Query result to the Data Model only, not to a sheet, and summarising with a PivotTable. You cannot see all rows in a grid. In pandas the 1.44 million rows load into memory comfortably on a typical laptop and you can write the result to CSV or Parquet. If you just need answers rather than a merged file, a SQL engine like DuckDB (which Analistable runs in the browser) handles the same volume without code.

Hidden costs on each side

• Excel: Power Query features differ between Windows and Mac versions, and very large queries can be slow to refresh.
• Excel: formulas copied over merged data break silently when rows are added outside their range.
• Python: someone has to maintain the environment, the package versions and the script.
• Python: a script nobody else can read is a single point of failure when its author leaves.

Python in Excel (available in some Microsoft 365 versions) runs pandas inside cells in Microsoft's cloud. It is useful for analysis on data already in the workbook but is not a replacement for a script that reads folders of files from your disk.

Handling messy columns

Real exports rarely line up. One month's file calls a column Cust ID, the next customer_id; one has an extra notes column; dates arrive as text. Power Query's folder combine uses the first file as a template, so columns that only appear in later files can be dropped unless you adjust the steps, and renamed columns turn into nulls. It can be fixed, but it takes care. See combining Excel files with different columns.

In pandas, pd.concat aligns columns by name and fills gaps with NaN, and a rename dictionary applied to every file before concatenating fixes inconsistent headers in one place:

renames = {"Cust ID": "customer_id", "CustomerID": "customer_id"}
frames = [pd.read_csv(f).rename(columns=renames) for f in glob.glob("exports/*.csv")]
sales = pd.concat(frames, ignore_index=True)
sales["order_date"] = pd.to_datetime(sales["order_date"], dayfirst=True)

Being explicit about dayfirst=True matters for UK dates; without it, 03/04/2026 may be read as 4 March.

Sharing the result

An Excel merge is shared by sending the workbook, and anyone can open it. A pandas merge usually ends by writing a CSV or Excel file, which is just as shareable, but the process that made it lives in a script on someone's machine or a repository. If the merge must be re-run by a colleague who does not use Python, document the command or hand them a Power Query version instead. For people who only need to ask questions of the combined data, a browser tool that reads the source files directly avoids sharing a merged copy at all.

A practical rule

1. Fits in a worksheet and maintained by Excel users: Power Query.
2. Over the row limit, hundreds of files, or scheduled and unattended: pandas.
3. Need answers across the files without building either: a SQL tool, or the free combine Excel files tool for a one-off merge.

The merge data in pandas, R and SQL hub covers each code route in more depth.

Frequently asked questions

Is pandas faster than Excel for merging?

For large data, generally yes, and it is not bound by the worksheet row limit. For a few small files the time to write a script outweighs any speed gain.

Can Power Query handle more than a million rows?

Yes, if you load the result to the Data Model rather than a worksheet. The worksheet itself is limited to 1,048,576 rows.

Do I need to learn Python to merge files?

No. Power Query handles most merging jobs without code. Python becomes worthwhile when size, volume or scheduling outgrow Excel.

Can Python read password-protected Excel files?

Not with pandas on its own. You need a separate library to decrypt the file first, or remove the password in Excel before reading it.

Should I use R instead of Python?

R with dplyr is equally capable for merging; choose whichever language your team already knows. The R merge guide covers the equivalent joins.

Is Power Query available on Mac?

Yes, in recent Microsoft 365 versions of Excel for Mac, though some connectors and editor features have arrived later than on Windows. Check your version if a step is missing.

What is the pandas equivalent of VLOOKUP?

df.merge(other, on="key", how="left"). Unlike VLOOKUP it returns every match, so check for duplicate keys first.

Related guides

• How to merge DataFrames in pandas
• Why R merge() drops rows unexpectedly, and how to keep them
• How to merge datasets in SAS
• How to join multiple tables in SQL
• Why a SQL join returns too many rows, and how to fix it