Excel openpyxl formulas

openpyxl: Insert Formulas While Preserving Formatting

Critical Rule: Load Without data_only

from openpyxl import load_workbook

# CORRECT: preserves formulas and formatting
wb = load_workbook('file.xlsx')

# WRONG: replaces formulas with cached values on save
wb = load_workbook('file.xlsx', data_only=True)

Basic Formula Insertion

wb = load_workbook('file.xlsx')
ws = wb['Sheet1']

ws['H12'] = '=INDEX(Data!$H$21:$M$40,MATCH($D12,Data!$B$21:$B$40,0),MATCH(H$10,Data!$H$4:$M$4,0))'

wb.save('file.xlsx')

Iterating Over Cell Ranges

When applying similar formulas to a grid of cells, iterate using row/column indices:

from openpyxl.utils import get_column_letter

# Fill a range (e.g., H12:L17) with formulas
start_row = 12
end_row = 17
start_col = 8   # H = column 8
end_col = 12    # L = column 12

for row in range(start_row, end_row + 1):
    for col in range(start_col, end_col + 1):
        col_letter = get_column_letter(col)
        cell = ws.cell(row=row, column=col)
        cell.value = f'=INDEX(Data!$I$21:$M$40,MATCH($D{row},Data!$B$21:$B$40,0),MATCH({col_letter}$10,Data!$I$4:$M$4,0))'

Preserving Cell Formatting

openpyxl preserves existing cell styles when you only change value. Setting a new value does NOT erase fill color, font, borders, etc.:

cell = ws['H12']
cell.value = '=SUM(A1:A10)'  # Only changes the value; style is preserved

Row and Column Reference Conversion

from openpyxl.utils import get_column_letter, column_index_from_string

get_column_letter(8)    # → 'H'
get_column_letter(12)   # → 'L'
column_index_from_string('H')  # → 8
column_index_from_string('L')  # → 12

Recalculating Formulas After Editing

After saving, run recalc.py to update cached formula values:

python recalc.py file.xlsx

Check output for errors:

{"status": "success", "total_errors": 0, "total_formulas": 42}

If errors found:

{"status": "errors_found", "error_summary": {"#REF!": {"count": 2, "locations": ["Task!H12"]}}}

Full Pattern for Multi-Range Formula Insertion

from openpyxl import load_workbook
from openpyxl.utils import get_column_letter

wb = load_workbook('gdp.xlsx')
ws = wb['Task']

# Fill H12:L17 with INDEX&MATCH formulas
for row in range(12, 18):
    for col in range(8, 13):  # H=8 to L=12
        col_letter = get_column_letter(col)
        formula = f'=INDEX(Data!$I$21:$M$40,MATCH($D{row},Data!$B$21:$B$40,0),MATCH({col_letter}$10,Data!$I$4:$M$4,0))'
        ws.cell(row=row, column=col).value = formula

wb.save('gdp.xlsx')

Verifying Column Mapping Before Writing

Always verify your column letter → index mapping:

• A=1, B=2, ..., H=8, I=9, J=10, K=11, L=12, M=13

Excel rows are 1-indexed; openpyxl uses the same 1-based indexing. Pandas DataFrames use 0-based indexing, so Excel row N = pandas row N-1.