Excel openpyxl formulas

Skill cxcscmu/SkillLearnBench/skills/b1-one-shot-claude-sonnet-4-6/weighted-gdp-calculation/excel-openpyxl-formulas

[COLM'26] SkillLearnBench is the first benchmark for evaluating continual learning methods that automatically generate agent skills.

Install

npx -y skills add cxcscmu/SkillLearnBench --skill excel-openpyxl-formulas

Assembled from the repository path, not quoted from the project. Check it against their README if it does not work.

What its author says it does

Copied from the file, not written here

Editing Excel files with openpyxl to insert formulas while preserving all existing formatting, styles, colors, and structure.

SKILL.md

3.0 KB, as published. Nobody here has run it

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.