Chapter 5: Working with Excel Formulas

So far, we've treated Python as a calculator, performing calculations and writing the final static numbers into Excel. This is powerful, but one of Excel's greatest strengths is its ability to handle dynamic calculations through formulas. When a number changes, formulas automatically update the result.

In this chapter, you'll learn how to write Excel formulas using Python, combining the automation power of your scripts with the dynamic capabilities of spreadsheets.

Getting Started: A Fresh Inventory Sheet

For this chapter, we will go back to the simple inventory.xlsx file we used in Chapter 3. This file should contain only the product, quantity, and price. If you don't have it, create a new Excel file named inventory.xlsx with the following data:

	A	B	C
1	Product	Quantity	Price
2	Laptop	15	1200
3	Mouse	50	25
4	Keyboard	30	75
5	Monitor	25	300
6	Webcam	40	50

Our goal is to add a "Total Value" column, but this time, instead of writing the calculated numbers, we'll write the formulas themselves.

Writing Formulas in a Loop

Just like in Chapter 3, we will loop through our data. Inside the loop, instead of calculating quantity * price in Python, we will construct a formula string like "=B2*C2" and assign it directly to the cell.

Create a new Python file named add_formulas.py and add the following code:

from openpyxl import load_workbook
from openpyxl.styles import Font

wb = load_workbook("inventory.xlsx")
ws = wb.active

# 1. Add and style the header for our new column
header_cell = ws['D1']
header_cell.value = "Total Value"
header_cell.font = Font(bold=True)

# 2. Loop through each row and write the formula
# We start from row 2 to skip the header
for row_num in range(2, ws.max_row + 1):
    # Construct the formula string dynamically
    # Example for row 2: "=B2*C2"
    formula = f"=B{row_num}*C{row_num}"

    # Write the formula to the cell in column D
    ws.cell(row=row_num, column=4).value = formula

# 3. Save the workbook
wb.save("inventory_with_formulas.xlsx")

print("Successfully created 'inventory_with_formulas.xlsx'")

Run this script. Now, open inventory_with_formulas.xlsx. Click on any cell in the "Total Value" column. You'll see the formula in the formula bar, not the static value! If you change a quantity or price, the total value for that row will update automatically.

Adding a Grand Total with the SUM Formula

Let's add one more common formula: a grand total for our "Total Value" column. We want to place a SUM formula in the cell just below the last item.

Modify your add_formulas.py script:

# ... (previous code for workbook loading and the first loop) ...

# 4. Add a grand total
last_data_row = ws.max_row
grand_total_row = last_data_row + 2

# Add a label for the total
total_label_cell = ws.cell(row=grand_total_row, column=3)
total_label_cell.value = "Grand Total"
total_label_cell.font = Font(bold=True)

# Construct the SUM formula
# It should sum everything from D2 to the last data row
sum_formula = f"=SUM(D2:D{last_data_row})"
ws.cell(row=grand_total_row, column=4).value = sum_formula
ws.cell(row=grand_total_row, column=4).font = Font(bold=True)

# 5. Save the final workbook
wb.save("inventory_with_formulas.xlsx")

print("Successfully created 'inventory_with_formulas.xlsx' with a grand total.")

Run the script one last time. Your new spreadsheet now contains a complete, dynamic table with a grand total that will automatically update if any data changes.

What You've Accomplished

You've learned a critical skill for advanced Excel automation: how to write formulas. By doing this, you create spreadsheets that are not just static reports but interactive and dynamic documents.

Now that our data is fully calculated and dynamic, we are ready to visualize it. In the next chapter, we will create charts and graphs.