OpenPyXL Formulas: Write Excel Formulas with Python

OpenPyXL can write Excel formulas directly into worksheet cells. A formula is stored as a string beginning with an equal sign =.

For example:

ws['A4']='=SUM(A1:A3)'

When the workbook is opened in Excel or another compatible spreadsheet application, the spreadsheet application can calculate the formula and store the result.

Write an Excel Formula with OpenPyXL 🔝

Assign an Excel formula to a cell in the same way that you assign a normal value. The formula string must begin with =.

from openpyxl import Workbook

wb=Workbook()
ws=wb.active

ws['A1']=10
ws['A2']=20
ws['A3']=30

ws['A4']='=SUM(A1:A3)'

wb.save(
    'openpyxl_formula.xlsx'
)

The formula stored in cell A4 is:

=SUM(A1:A3)

After Excel calculates the workbook, the displayed result is:

60

Using SUM and AVERAGE Formulas 🔝

Excel functions such as SUM() and AVERAGE() can be written directly as formula strings.

from openpyxl import Workbook

wb=Workbook()
ws=wb.active

for i in range(1, 6):
    ws.cell(
        row=i,
        column=1,
        value=i * 10
    )

ws['A6']='=SUM(A1:A5)'
ws['A7']='=AVERAGE(A1:A5)'

wb.save(
    'openpyxl_sum_average.xlsx'
)

The values in A1:A5 are:

10
20
30
40
50

Excel calculates:

SUM = 150
AVERAGE = 30

AVERAGE Example

from openpyxl import Workbook

wb=Workbook()
ws=wb.active

ws['A1']=85
ws['A2']=78
ws['A3']=92

ws['A4']='=AVERAGE(A1:A3)'

wb.save(
    'openpyxl_average.xlsx'
)

The calculated average is:

85

Arithmetic Formulas 🔝

A formula does not have to use an Excel function. Normal arithmetic operators can be used with cell references.

from openpyxl import Workbook

wb=Workbook()
ws=wb.active

ws['A1']=25
ws['B1']=4

ws['C1']='=A1+B1'
ws['C2']='=A1-B1'
ws['C3']='=A1*B1'
ws['C4']='=A1/B1'

wb.save(
    'openpyxl_arithmetic.xlsx'
)

These formulas perform addition, subtraction, multiplication and division using values stored in worksheet cells.

Using the Excel IF Formula 🔝

The Excel IF() function can return one value when a condition is true and another value when it is false.

from openpyxl import Workbook

wb=Workbook()
ws=wb.active

ws['A1']=45

ws['B1']='=IF(A1>=50,"Pass","Fail")'

wb.save(
    'openpyxl_if.xlsx'
)

Because the value in A1 is below 50, Excel returns:

Fail

If A1 contains 50 or more, the result is Pass.

Add Formulas to Multiple Rows 🔝

Python loops are useful when the same formula pattern must be inserted into many Excel rows.

In this example, marks are stored in column D and the formula-generated status is stored in column F.

from openpyxl import load_workbook

file_path='student.xlsx'

wb=load_workbook(
    file_path
)
ws=wb.active

ws['F1']='Status'

for row in range(
    2,
    ws.max_row + 1
):
    ws.cell(
        row=row,
        column=6
    ).value=f'=IF(D{row}>=80,"Pass","Fail")'

wb.save(
    'student_with_status.xlsx'
)

For row 2, Python creates:

=IF(D2>=80,"Pass","Fail")

For row 3:

=IF(D3>=80,"Pass","Fail")

The formula row number changes automatically through the Python f-string.

Combine Text and Cell Values 🔝

Excel formulas can combine text with the contents of worksheet cells.

Using the & Operator

from openpyxl import Workbook

wb=Workbook()
ws=wb.active

ws['A1']=50
ws['B1']='John'

ws['C1']='=B1&" scored "&A1&" marks."'

wb.save(
    'openpyxl_text_formula.xlsx'
)

Excel displays:

John scored 50 marks.

Using CONCATENATE()

The same result can be created using the Excel CONCATENATE() function.

ws['C1']='=CONCATENATE(B1," scored ",A1," marks.")'

The text values inside the Excel formula use double quotation marks.

Read the Formula Stored in a Cell 🔝

By default, load_workbook() returns the formula stored in a formula cell.

from openpyxl import load_workbook

wb=load_workbook(
    'openpyxl_formula.xlsx'
)
ws=wb.active

print(
    ws['A4'].value
)

=SUM(A1:A3)

This is the Excel formula itself, not the calculated result.

Read Cached Formula Results with data_only=True 🔝

When Excel or another spreadsheet calculation engine calculates and saves a workbook, the file can also contain a cached result for a formula cell.

OpenPyXL can read this cached result by loading the workbook with data_only=True.

from openpyxl import load_workbook

wb=load_workbook(
    'openpyxl_formula.xlsx',
    data_only=True
)

ws=wb.active

print(
    ws['A4'].value
)

Formula Mode vs Cached-Result Mode

Formula Across Worksheets 🔝

A formula can refer to a cell on another worksheet.

from openpyxl import Workbook

wb=Workbook()

ws1=wb.active
ws1.title='Data'

ws2=wb.create_sheet(
    'Report'
)

ws1['A1']=25

ws2['A1']='=Data!A1*2'

wb.save(
    'openpyxl_cross_sheet.xlsx'
)

The formula stored in Report!A1 uses the value from Data!A1.

Worksheet Names Containing Spaces

If a worksheet name contains spaces, put single quotation marks around the sheet name in the Excel formula.

ws2['A1']="='Student Data'!A1*2"

Build Dynamic Formulas with Python Variables 🔝

Python variables can be inserted into formula strings with an f-string.

start_row=2
end_row=6

ws['B1']=f'=SUM(A{start_row}:A{end_row})'

The resulting Excel formula is:

=SUM(A2:A6)

Use max_row

If the number of rows changes, the last row can be read from the worksheet.

last_row=ws.max_row

ws['B1']=f'=SUM(A2:A{last_row})'

This is useful when reports contain a different number of rows each time they are generated.

Copy and Translate Formulas 🔝

If a formula has relative references, OpenPyXL's Translator can move the formula to another location while adjusting those references.

from openpyxl import Workbook
from openpyxl.formula.translate import Translator

wb=Workbook()
ws=wb.active

ws['A1']=10
ws['B1']=20

ws['C1']='=A1+B1'

ws['C2']=Translator(
    ws['C1'].value,
    origin='C1'
).translate_formula(
    'C2'
)

print(
    ws['C2'].value
)

=A2+B2

The relative references move from row 1 to row 2.

Generate a Student Marksheet with Formulas 🔝

This practical example creates a worksheet containing student marks and lets Excel calculate the average, total and pass/fail status.

from openpyxl import Workbook

wb=Workbook()
ws=wb.active
ws.title='Marksheet'

headers=[
    'Name',
    'Math',
    'Science',
    'English',
    'Average',
    'Total',
    'Status'
]

ws.append(headers)

students=[
    ['Alex', 80, 90, 85],
    ['John', 75, 70, 60],
    ['Krish', 90, 95, 92]
]

for row, data in enumerate(
    students,
    start=2
):
    ws.cell(row=row, column=1, value=data[0])
    ws.cell(row=row, column=2, value=data[1])
    ws.cell(row=row, column=3, value=data[2])
    ws.cell(row=row, column=4, value=data[3])

    ws.cell(
        row=row,
        column=5,
        value=f'=AVERAGE(B{row}:D{row})'
    )

    ws.cell(
        row=row,
        column=6,
        value=f'=SUM(B{row}:D{row})'
    )

    ws.cell(
        row=row,
        column=7,
        value=f'=IF(E{row}>=50,"Pass","Fail")'
    )

wb.save(
    'student_marksheet.xlsx'
)

Python creates the workbook and formula expressions. Excel then calculates the formulas when the workbook is recalculated.

Add Formulas to an Existing Excel Workbook 🔝

Use load_workbook() instead of Workbook() when you want to modify an existing Excel file.

You can use the Plus2net sample workbook:

from openpyxl import load_workbook

file_path='student.xlsx'

wb=load_workbook(
    file_path
)
ws=wb.active

ws['F1']='Status'

for row in range(
    2,
    ws.max_row + 1
):
    ws[f'F{row}']=(
        f'=IF(D{row}>=80,"Pass","Fail")'
    )

wb.save(
    'student_formula.xlsx'
)

The updated workbook is saved under a new filename, leaving the original file unchanged.

Common OpenPyXL Formula Mistakes 🔝

1. Forgetting the Equal Sign

This writes ordinary text:

ws['A1']='SUM(B1:B5)'

This writes a formula:

ws['A1']='=SUM(B1:B5)'

2. Expecting OpenPyXL to Calculate the Formula

OpenPyXL stores the formula but does not contain Excel's formula calculation engine.

If Python needs the result immediately, calculate it in Python instead.

values=[
    10,
    20,
    30
]

total=sum(values)

print(total)

60

3. Assuming data_only=True Calculates Formulas

data_only=True only reads the cached result already stored in the workbook.

4. Incorrect Quotation Marks Inside an Excel Formula

Correct:

ws['A1']='=IF(B1>=50,"Pass","Fail")'

The outer Python string uses single quotation marks, while the Excel text values use double quotation marks.

5. Worksheet Names Containing Spaces

Quote the worksheet name:

ws['A1']="='Student Data'!B2"

6. Using an Excel Formula When Python Should Calculate the Value

Use an Excel formula when you want the generated workbook to remain dynamic and recalculate when worksheet cells change.

Use Python when the Python program itself needs the calculated value immediately.

Summary of OpenPyXL Formulas 🔝

• Excel formulas are assigned to OpenPyXL cells as strings beginning with =.
• SUM(), AVERAGE() and IF() can be inserted directly into worksheet cells.
• Arithmetic formulas can use Excel references such as =A1*B1.
• Python loops can generate formulas for multiple worksheet rows.
• Python f-strings are useful for formulas containing dynamic row numbers.
• Formulas can reference cells on another worksheet.
• Worksheet names containing spaces should be quoted in cross-sheet formulas.
• OpenPyXL does not calculate Excel formulas.
• Normal load_workbook() returns the formula text from a formula cell.
• data_only=True returns a cached formula result when one exists.
• A workbook that has not been calculated by a spreadsheet application may not contain a usable cached result.
• Translator can move formulas while adjusting relative cell references.
• Use Python calculations when the result is required immediately inside the Python program.

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: