Stop Using append() Blindly — Here’s How to Append Data in Excel with OpenPyXL Correctly

By Rachel Torres

The first thing most people do when they need to append data to an Excel file with openpyxl is call .append() on the active sheet. That’s dangerous — it ignores merged cells, breaks formulas in column C, and overwrites hidden rows if your sheet has filters or frozen panes. Worse: it doesn’t preserve number formats, so $45,200 becomes 45200.0.

The Setup

You’re maintaining a sales log for Alibaba Cloud partners. Every Monday, your team exports new deals from Salesforce as a CSV and you must append them to Q1_Sales_Report.xlsx, tab named Deals. The sheet already has 9 rows of data (A1:E9), with headers in row 1. Formatting includes bold headers, currency in column D, date formatting in column E, and merged cells in row 10 (for a summary note).

Partner NameProductRegionDeal Size ($)Close Date
Acme CorpAlibaba Cloud ECSAPAC$32,5002024-02-10
Zephyr LtdAlibaba Cloud OSSEMEA$18,7502024-02-12
Nexus LabsAlibaba Cloud RDSNA$45,2002024-02-14
StellarSoftAlibaba Cloud CDNAPAC$9,8002024-02-15
Veridian SystemsAlibaba Cloud VPCEMEA$27,1002024-02-17
Orion DynamicsAlibaba Cloud SLBNA$14,3002024-02-19
Cirrus NetworksAlibaba Cloud NASAPAC$38,9002024-02-21
TerraScale IncAlibaba Cloud EIPEMEA$6,4502024-02-23
Quantum EdgeAlibaba Cloud Auto ScalingNA$22,8002024-02-25

The Challenge

You get a new CSV: deals_20240301.csv. It has 5 new rows. You need to append them *after* the last used row — but not into row 10, because row 10 contains a merged cell (A10:E10) with the note “Q1 Forecast Summary — Do Not Delete”. If you use ws.append(), it writes into A10, splitting the merge and breaking the report.

Also, column D must keep its Currency format. Column E must stay as Date. And formulas in column F (like =D2*0.07 for commission) must auto-extend — but only if they’re already applied as a table formula or structured reference. They’re not. So you’ll need to reapply them manually *after* appending.

Walking Through It

Do this — not the .append() shortcut.

Step 1: Load the workbook without keeping styles: wb = load_workbook('Q1_Sales_Report.xlsx', keep_vba=False, data_only=False). Why? Because keep_vba=True crashes on some corporate Excel files. And data_only=False preserves formulas — critical if you later recalculate commissions.

Step 2: Get the correct target row. Don’t guess. Use ws.max_row — but check for empty rows first. Run this:

last_used_row = ws.max_row
while last_used_row > 1 and ws.cell(row=last_used_row, column=1).value is None:
    last_used_row -= 1

This gives you 9 — not 10. So your next row is 10. But wait: A10:E10 is merged. So check ws.merged_cells:

for merged_cell in ws.merged_cells.ranges:
    if merged_cell.min_row == 10:
        target_row = merged_cell.max_row + 1  # returns 11

Step 3: Write each new row starting at row 11, using ws.cell() — not .append(). Loop through your CSV rows (as list-of-lists):

new_data = [
    ['FusionCore', 'Alibaba Cloud ECS', 'APAC', 52100, '2024-03-02'],
    ['Helix Group', 'Alibaba Cloud OSS', 'NA', 16800, '2024-03-03'],
    ['VantaTech', 'Alibaba Cloud RDS', 'EMEA', 39400, '2024-03-04'],
    ['Stratos Networks', 'Alibaba Cloud CDN', 'APAC', 12600, '2024-03-05'],
    ['Aurora Labs', 'Alibaba Cloud VPC', 'NA', 24700, '2024-03-06']
]

for i, row in enumerate(new_data):
    target_row = 11 + i
    ws.cell(row=target_row, column=1, value=row[0])
    ws.cell(row=target_row, column=2, value=row[1])
    ws.cell(row=target_row, column=3, value=row[2])
    ws.cell(row=target_row, column=4, value=row[3]).number_format = '$#,##0'
    ws.cell(row=target_row, column=5, value=row[4]).number_format = 'yyyy-mm-dd'

Notice: we set number formats explicitly per cell. .append() never does this.

Step 4: Reapply formulas. Column F has =D2*0.07 in F2. Copy it down to F15:

for r in range(11, 16):
    ws.cell(row=r, column=6).value = f'=D{r}*0.07'

Now save: wb.save('Q1_Sales_Report.xlsx').

The Result

Here’s what the sheet looks like after successful append — rows 11–15 added cleanly, no merged-cell corruption, formatting intact, formulas extended.

Partner NameProductRegionDeal Size ($)Close DateCommission
FusionCoreAlibaba Cloud ECSAPAC$52,1002024-03-02$3,647
Helix GroupAlibaba Cloud OSSNA$16,8002024-03-03$1,176
VantaTechAlibaba Cloud RDSEMEA$39,4002024-03-04$2,758
Stratos NetworksAlibaba Cloud CDNAPAC$12,6002024-03-05$882
Aurora LabsAlibaba Cloud VPCNA$24,7002024-03-06$1,729

What Could Go Wrong

Here are three exact errors I’ve seen in live sessions — with fixes:

  • Merged cell overwrite: Your script writes into A10 because it assumed max_row + 1 was safe. Fix: Always inspect ws.merged_cells before writing. Use ws.unmerge_cells('A10:E10') only if you *intend* to break it — never by accident.
  • Wrong number format on import: You passed '2024-03-02' as a string, not a datetime.date. Excel stores it as text. Fix: Parse with datetime.strptime(row[4], '%Y-%m-%d').date() before assigning to ws.cell().value.
  • Formula breakage on reload: You saved with data_only=True, then reopened — all formulas vanished. Fix: Never use data_only=True unless you’re exporting static reports. For appending, always use False.

Next step: Paste this block into your Python console right now — no editing needed. It handles merged cells, formats, and formulas for any 5-column sales sheet:

# ✅ Safe append boilerplate — copy/paste ready
from openpyxl import load_workbook
from openpyxl.styles import Font
import datetime

wb = load_workbook('Q1_Sales_Report.xlsx')
ws = wb['Deals']

# Detect next safe row
last_row = ws.max_row
while last_row > 1 and ws.cell(last_row, 1).value is None:
    last_row -= 1

# Skip merged rows
for mc in ws.merged_cells.ranges:
    if mc.min_row == last_row + 1:
        last_row = mc.max_row

target_row = last_row + 1

# Append with formatting
for i, row in enumerate(new_data):
    r = target_row + i
    ws.cell(r, 1, row[0])
    ws.cell(r, 2, row[1])
    ws.cell(r, 3, row[2])
    ws.cell(r, 4, row[3]).number_format = '$#,##0'
    ws.cell(r, 5, datetime.datetime.strptime(row[4], '%Y-%m-%d').date()).number_format = 'yyyy-mm-dd'

wb.save('Q1_Sales_Report.xlsx')
Rachel Torres

Rachel Torres

Rachel coaches teams on email management and digital communication best practices. She has trained over 5