How to Merge Multiple Excel Files into One with Python

Every month, the same thing happens in offices everywhere:
someone has to open 12 monthly reports and manually copy everything into a master file.

I built a Python tool that eliminates this entirely.

## What it does

python merge_excel.py ./reports

It finds every .xlsx file in the folder, reads them all, stacks the data,
and exports one clean formatted spreadsheet — with a "Source File" column
showing exactly where each row came from.

## The core — pd.concat

The key function is pandas' concat. It stacks DataFrames from different files
even when columns don't match perfectly — missing columns are filled automatically.

import pandas as pd
from pathlib import Path

def merge_files(folder):
    files = sorted(Path(folder).glob("*.xlsx"))
    frames = []

    for f in files:
        df = pd.read_excel(f, engine="openpyxl")
        df.insert(0, "Source File", f.name)
        frames.append(df)

    merged = pd.concat(frames, ignore_index=True, sort=False)
    merged.fillna("", inplace=True)
    return merged

That's the core. The rest is formatting and CLI handling.

## The full tool

The complete script adds:
- Auto-detection of files (folder or explicit list)
- Professional Excel formatting (headers, alternating rows, frozen pane)
- Clean terminal output showing progress

GitHub: github.com/grey-pv/excel-merger

## What's next

This is project #2 of a Python automation toolkit I'm building in public.
Each project solves one specific data problem. Next up: CSV Cleaner.