Formulas and Calculations in Excel using openpyxl

Insert Excel Formulas Using OpenPyXL in Google Colab (Python Excel Automation)

Writing Formulas to Excel Cells

We can assign Excel formulas to cells just like values. The Excel application evaluates the formula when the file is opened.
In Excel, formulas are written using an equal sign (=) followed by the formula itself. OpenPyXL allows you to insert these formulas directly into cells.

from openpyxl import Workbook

wb = Workbook() # create workbook
ws1 = wb.active # work on default worksheet

ws1['A1'] = 10  # adding value to cell A1
ws1['A2'] = 20  # adding value to cell A2
ws1['A3'] = 30  # adding value to cell A3

ws1['A4'] = "=SUM(A1:A3)"  # formula to sum A1 to A3

my_path = "openpyxl_formulas_example.xlsx"  # file path
wb.save(my_path)  # saving the workbook

This will display the calculated result when opened in Excel. OpenPyXL itself does not evaluate formulas — Excel does.

Protected View Warning! When you download an Excel file created with OpenPyXL and open it in Excel, it may open in Protected View. In this mode, Excel disables editing, and formulas will not be executed. This is a built-in security feature designed to protect users from potentially unsafe files.

Solution: To see the results of the formulas, you need to click the Enable Editing button at the top of the Excel window. Once you do that, the formulas will be executed, and the results will be displayed.

Note: Always make sure that the source of the file is trusted before enabling editing.

Using Built-in Functions like SUM and AVERAGE

We can apply Excel functions directly in formula strings.

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

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

Another useful Excel formula is AVERAGE. OpenPyXL allows you to use this function to calculate the average of a given range of numbers.

from openpyxl import Workbook

wb = Workbook() # create workbook
ws1 = wb.active # work on default worksheet

ws1['A1'] = 85  # adding first number
ws1['A2'] = 78  # adding second number
ws1['A3'] = 92  # adding third number

ws1['A4'] = "=AVERAGE(A1:A3)"  # formula to calculate average

my_path = "openpyxl_average_example.xlsx"  # file path
wb.save(my_path)  # saving the workbook

Using IF Conditions in Excel

You can use IF statements to apply conditional calculations in Excel. For instance, you may want to label students as "Pass" or "Fail" based on their marks.

from openpyxl import Workbook

wb = Workbook() # create workbook
ws1 = wb.active # work on default worksheet

ws1['A1'] = 45  # student's marks
ws1['B1'] = '=IF(A1>=50, "Pass", "Fail")'# if marks greater than 50, label as pass

my_path = "openpyxl_if_condition_example.xlsx"  # file path
wb.save(my_path)  # saving the workbook

In this example, we are using the openpyxl library to read and modify an existing Excel file. The code loads a student data file, checks the marks in column 'D', and adds a "Status" column in column 'F'. The formula =IF(D{row}>=80, "Pass", "Fail") is applied to each row to determine whether the student has passed or failed based on the marks in column 'D'. The updated file is then saved with the new status values.

from openpyxl import load_workbook

# Load the student Excel file
file_path = 'D:\\testing3\\openpyxl\\student.xlsx'
#!wget https://www.plus2net.com/python/download/student.xlsx # Excel file download
wb = load_workbook(filename=file_path)
ws1 = wb.active # Work on the default sheet

# Assuming 'marks' are in column 'D' and you want to add status in column 'F'
ws1['F1'] = 'Status' # Adding a header for the new column

# Iterate through rows and apply the IF condition
for row in range(2, ws1.max_row + 1):  # Start from row 2 to skip header
    ws1.cell(row=row, column=6).value = '=IF(D{0}>=80, "Pass", "Fail")'.format(row) # Column D is for marks, Column E for status

# Save the updated file
wb.save(file_path)

Working with the CONCATENATE Function

In Excel, the CONCATENATE function allows you to combine multiple strings or text into a single cell. You can combine text and calculation results to generate informative text.

from openpyxl import Workbook

wb = Workbook() # create workbook
ws1 = wb.active # work on default worksheet

ws1['A1'] = 50  # student's score
ws1['B1'] = "John"  # student's name

ws1['C1'] = "=CONCATENATE(B1, ' scored ', A1, ' marks.')  # concatenating name and score

my_path = "openpyxl_concatenate_example.xlsx"  # file path
wb.save(my_path)  # saving the workbook

Reading Calculated Results using `data_only=True`

If you want to read the computed result (not the formula text), open the workbook with `data_only=True`.

from openpyxl import load_workbook
wb2 = load_workbook('openpyxl_formula_demo.xlsx', data_only=True)
ws2 = wb2.active

print("Sum Result:", ws2['A6'].value)
print("Average Result:", ws2['A7'].value)

⚠️ If Excel has not recalculated after saving, results might appear blank until the file is opened once in Excel.

Formula Across Worksheets

We can refer to cells from other sheets.

ws1 = wb.create_sheet('Sheet1')
ws2 = wb.create_sheet('Sheet2')

ws1['A1'] = 25
ws2['A1'] = "=Sheet1!A1*2"

wb.save('openpyxl_formula_sheet.xlsx')

Dynamic Formula Using Python Variables

We can dynamically build formula strings using Python variables.

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

Example: Generating a Marksheet Automatically

We can combine formulas with loops to generate reports.

students = [['Alex',80,90,85],['John',75,70,60],['Krish',90,95,92]]
for r, data in enumerate(students,2):
    ws.cell(r,1,data[0])
    for i in range(3):
        ws.cell(r,i+2,data[i+1])
    ws.cell(r,5,f"=AVERAGE(B{r}:D{r})")

Download the above full source code from Github or run the code in your Google colab platform.

OpenPyxl Formulas
https://github.com/plus2net/Python-basics/blob/main/openpyxl_5_formulas.ipynb

Generate Payroll Excel using OpenPyXL formulas
Reading Excel file Database table to Excel file Managing Worksheets Styles and formatting openpyxl Library
Next ➜ Using Pandas to Export Data to Excel
⟵ Back: Styles and Formatting

Download this source code: openpyxl_formulas.ipynb

Subhendu Mohapatra

Author

🎥 Join me live on YouTube

Passionate about coding and teaching, I publish practical tutorials on PHP, Python, JavaScript, SQL, and web development. My goal is to make learning simple, engaging, and project‑oriented with real examples and source code.