Automating Excel Formulas with Python

 Learning Goals

• Understand how to create an Excel file with Python.
• Learn how to insert formulas in Excel using Python.
• Practice automating calculations like totals, subtotals, and tax.

 Step 1: Import Required Library

We use openpyxl to work with Excel in Python.

from openpyxl import Workbook
from openpyxl.styles import Font

Step 2: Create Workbook and Worksheet

wb = Workbook()
ws = wb.active

• Workbook = an Excel file.
• Worksheet = a sheet inside the workbook.

 Step 3: Add Sample Data

We add products, prices, and quantities.

data = [
    ["Product", "Price", "Quantity", "Total"],
    ["A", 19.99, 5],
    ["B", 29.99, 3],
    ["C", 9.99, 10]
]

for row in data:
    ws.append(row)

This creates a small table.

Step 4: Add Formulas

We want Excel to auto-calculate totals.

for i in range(2, 5):   # Rows 2–4
    ws[f'D{i}'] = f"=B{i}*C{i}"  # Price × Quantity

👉 Formula is same as typing =B2*C2 directly in Excel.

Step 5: Add Summary Section

ws['B6'] = "Subtotal:"
ws['C6'] = "=SUM(D2:D4)"   # Adds all totals

ws['B7'] = "Tax (10%):"
ws['C7'] = "=C6*0.1"       # 10% of subtotal

ws['B8'] = "Total:"
ws['C8'] = "=C6+C7"        # Subtotal + Tax

 Step 6: Formatting (Bold Text)

for cell in ['B6', 'B7', 'B8']:
    ws[cell].font = Font(bold=True)

 Step 7: Save File

wb.save("formula_demo.xlsx")
print("Created Excel file with automated formulas")

Complete Code

from openpyxl import Workbook
from openpyxl.styles import Font

wb = Workbook()
ws = wb.active

data = [
    ["Product", "Price", "Quantity", "Total"],
    ["A", 19.99, 5],
    ["B", 29.99, 3],
    ["C", 9.99, 10]
]

for row in data:
    ws.append(row)

for i in range(2, 5):   # Rows 2–4
    ws[f'D{i}'] = f"=B{i}*C{i}"  # Price × Quantity

ws['B6'] = "Subtotal:"
ws['C6'] = "=SUM(D2:D4)"   # Adds all totals

ws['B7'] = "Tax (10%):"
ws['C7'] = "=C6*0.1"       # 10% of subtotal

ws['B8'] = "Total:"
ws['C8'] = "=C6+C7"        # Subtotal + Tax

for cell in ['B6', 'B7', 'B8']:
    ws[cell].font = Font(bold=True)


wb.save("formula_demo.xlsx")
print("Created Excel file with automated formulas")

 What You Learned

• How to create Excel sheets with Python.
• How to write formulas automatically.
• How to format important cells.

 Activity for Students

1. Change tax from 10% → 15% and check results.
2. Add a new product row (D, with price & quantity).
3. Modify formula ranges to include the new product.