Append New Rows to a Master Excel File Without Duplicates

A common reporting setup: a master workbook that collects every invoice, ticket or shipment, and a weekly extract from a system that covers "the last 14 days" — so each extract overlaps the previous one. Appending each extract in full doubles the overlapping rows; appending only rows after the last date misses late-arriving records with earlier dates. The reliable approach is to compare on a key — the invoice number, the ticket ID — and append only rows whose key is not already in the master. Two refinements make it production-ready: deciding what to do with rows that already exist but have changed, and appending without destroying the master's formatting, which a full rewrite with pandas would. This page belongs to Merging and Joining Excel DataFrames.

Prerequisites

pip install pandas openpyxl

master_invoices.xlsx holds three invoices in a formatted Excel table named Invoices. extract_week39.xlsx is this week's system extract: it repeats INV-1002 (now paid) and INV-1003 (amount corrected from 980 to 1,010), adds two new invoices — and lists INV-1005 twice, because extracts are not always clean.

Step 1 — Find the new rows with an anti-join

import pandas as pd

master = pd.read_excel("master_invoices.xlsx", sheet_name="Invoices")
extract = pd.read_excel("extract_week39.xlsx")
KEY = "Invoice"

dupes_in_extract = extract[extract.duplicated(KEY, keep=False)]
print("duplicate keys inside the extract:", dupes_in_extract[KEY].unique().tolist())
extract = extract.drop_duplicates(KEY, keep="last")

flagged = extract.merge(master[[KEY]], on=KEY, how="left", indicator=True)
new_rows = flagged[flagged["_merge"] == "left_only"].drop(columns="_merge")
print(new_rows.to_string(index=False))

First the extract is de-duplicated on its own key — otherwise INV-1005 would be appended twice. Then a left merge with indicator=True marks each extract row as present in the master (both) or not (left_only); the left_only rows are the new ones. This is an anti-join, the same technique used in Find Rows in One Excel File Missing from Another. Comparing on the key rather than on whole rows matters: INV-1003 changed amount, and a whole-row comparison would treat it as new and add a second INV-1003.

Step 2 — Detect rows that changed

Existing keys are not necessarily identical rows. Compare the overlapping rows column by column to see what changed:

compare_cols = ["Date", "Customer", "Amount", "Status"]
both = extract.merge(master, on=KEY, suffixes=("_new", "_old"))
changes = []
for col in compare_cols:
    diff = both[both[f"{col}_new"] != both[f"{col}_old"]]
    for _, r in diff.iterrows():
        changes.append({KEY: r[KEY], "Column": col, "Old": r[f"{col}_old"], "New": r[f"{col}_new"]})
changes = pd.DataFrame(changes)
print(changes.to_string(index=False))

INV-1002's status moved from Open to Paid; INV-1003's amount went from 980 to 1,010. Whether the master should take these updates is a policy decision: status changes are usually wanted, amount corrections in a finance master usually need review. Keeping them in a separate change list lets the job apply the safe ones and route the rest to a person; the general comparison technique is in Compare Two Excel Files for Differences with Python.

Step 3 — Append with openpyxl to keep the formatting

Rewriting the master with to_excel would discard the header colours, the number formats and the table. Append the new rows below the existing data instead, copying the formats from the last existing row, and extend the table's range:

from copy import copy
from openpyxl import load_workbook

def append_rows(path, sheet, frame, table_name):
    wb = load_workbook(path)
    ws = wb[sheet]
    template_row = ws.max_row
    for row in frame.itertuples(index=False):
        values = [v.to_pydatetime() if isinstance(v, pd.Timestamp) else v for v in row]
        ws.append(values)
        for col, cell in enumerate(ws[ws.max_row], start=1):
            src = ws.cell(template_row, col)
            cell.number_format = src.number_format
            cell.font, cell.alignment = copy(src.font), copy(src.alignment)
    table = ws.tables[table_name]
    first, _ = table.ref.split(":")
    table.ref = f"{first}:{ws.cell(ws.max_row, ws.max_column).coordinate}"
    wb.save(path)
    return ws.max_row - 1

rows_now = append_rows("master_invoices.xlsx", "Invoices", new_rows[master.columns], "Invoices")
print("rows in master:", rows_now)
print(load_workbook("master_invoices.xlsx")["Invoices"].tables["Invoices"].ref)

ws.append writes after the last used row; copying number format, font and alignment from the row above keeps dates as dd mmm yyyy and amounts with two decimals. The table's ref is widened from A1:E4 to A1:E6, so the new rows join the table — its banding, filters and any formulas that use structured references. Column order is forced with new_rows[master.columns], so a reordered extract cannot shift values into the wrong columns. Table resizing is covered in more detail in Append Rows to an Excel Table and Resize It.

Step 4 — Apply approved updates in place

For the changes the policy allows — here, status changes — update the matching cells in the master directly, again without rewriting the sheet:

def apply_updates(path, sheet, changes, key_col="A", allowed=("Status",)):
    wb = load_workbook(path)
    ws = wb[sheet]
    header = {c.value: c.column for c in ws[1]}
    rows = {ws[f"{key_col}{r}"].value: r for r in range(2, ws.max_row + 1)}
    applied = []
    for ch in changes.itertuples(index=False):
        if ch.Column in allowed and ch.Invoice in rows:
            ws.cell(rows[ch.Invoice], header[ch.Column], ch.New)
            applied.append((ch.Invoice, ch.Column, ch.New))
    wb.save(path)
    return applied

print("applied:", apply_updates("master_invoices.xlsx", "Invoices", changes))
print("needs review:", changes[changes["Column"] != "Status"].to_dict("records"))

INV-1002 becomes Paid in the master; INV-1003's amount correction is reported for review instead of being applied. Mapping keys to row numbers and header names to column numbers makes the update independent of where things sit in the sheet.

Step 5 — Make reruns harmless and keep a log

Jobs get rerun — after a failure, by a colleague, by a scheduler that fired twice. Because new rows are found by key, a rerun finds nothing new:

import datetime as dt
import os

master_after = pd.read_excel("master_invoices.xlsx", sheet_name="Invoices")
rerun_new = extract[~extract[KEY].isin(master_after[KEY])]
print("new rows on rerun:", len(rerun_new), "| duplicate keys in master:", master_after[KEY].duplicated().sum())

log = pd.DataFrame([{"Run at": dt.datetime(2026, 10, 2, 7, 30), "Extract": "extract_week39.xlsx",
                     "Appended": len(new_rows), "Status updates": 1, "For review": len(changes) - 1}])
log.to_csv("master_update_log.csv", mode="a", index=False, header=not os.path.exists("master_update_log.csv"))
print(log.to_string(index=False))

The rerun appends zero rows and the master contains no duplicate invoice numbers — the property worth asserting in a test. The log records what each run did, which is what someone needs when a figure in the master is questioned weeks later. For locking against two runs at the same moment, see Prevent Overlapping Runs of an Excel Report Job.

Performance notes

Reading the master's key column and anti-joining a weekly extract is fast at any realistic size. The cost that grows is the openpyxl load and save of the master: a workbook of a few thousand rows takes well under a second, one with several hundred thousand rows takes many seconds and a lot of memory every week, because openpyxl rewrites the whole file to add two rows. When a master grows that large, it has outgrown Excel as a store — keep the records in a database or Parquet file, append there, and generate the workbook from it as a report, as in Load an Excel File into a SQL Database with pandas.

Common pitfalls

• Comparing whole rows. A changed row looks new and is appended a second time; compare on the key.
• Trusting the extract to be unique. De-duplicate it on the key before anything else.
• Appending by date. Late-arriving rows with earlier dates are missed; use keys.
• Rewriting the master with to_excel. Formatting, tables and other sheets are lost; append with openpyxl.
• Keys that differ only in formatting. INV-1004 and INV-1004 do not match; normalise keys first, as in Merge on Keys That Differ in Case and Spacing with pandas.

Conclusion

To update a master workbook from overlapping extracts, de-duplicate the extract on its key, anti-join it against the master to find genuinely new rows, and list existing rows whose values changed. Append the new rows with openpyxl below the existing data, copying formats and widening the table, apply only the changes your policy allows, and send the rest for review. Because everything is keyed, reruns add nothing, and a short log records what every run did.

Frequently asked questions

How do I append only new rows to an existing Excel file with pandas? Read the master and the new data, merge the new data against the master's key column with how="left" and indicator=True, keep the rows marked left_only, and append those rows to the master.

How do I avoid duplicate rows when appending to Excel? Compare on a unique key such as an invoice number rather than whole rows, remove duplicate keys inside the new data first, and check after appending that the master has no duplicated keys.

How do I append rows without losing the master workbook's formatting? Load the master with openpyxl, write the new rows with ws.append below the existing data, copy the number formats from the row above, extend any Excel table's ref to include the new rows, and save.

What should happen to rows that already exist but have changed? That depends on the column: status updates are usually applied automatically, while corrections to amounts often need review. Compare the overlapping rows column by column and apply only the changes your policy allows.

Related

• Up one level: Merging and Joining Excel DataFrames — combining data from several sources.
• Append a DataFrame to an Existing Excel File with pandas — the pandas writer options for appending.
• Update One Excel File from Another with pandas — updating values by key.
• Upsert Excel Rows into a Database Table — the same pattern when the master is a database.