Lab Activity : Automating Excel Formulas

Objectives

By the end of this lab, students will be able to:

• Create an Excel file with Python.

• Add data into Excel using openpyxl.

• Write formulas (like multiplication, SUM, and percentages) automatically.

• Save and open the Excel file to check results.

step-by-step Instructions

Step 1: Setup

1. Open your Python environment (Jupyter Notebook, VS Code, PyCharm, or IDLE).

2. Install openpyxl if not installed yet:

   pip install openpyxl
   

Step 2: Create a New Python File

• Create a file named lab_excel_formulas.py.

Step 3: Add Sample Data

Type this code to create an Excel workbook and add some data:

from openpyxl import Workbook
from openpyxl.styles import Font

# Create new workbook and sheet
wb = Workbook()
ws = wb.active
ws.title = "Sales Data"

# Add sample data (header row + products)
data = [
    ["Product", "Price", "Quantity", "Total"],
    ["A", 19.99, 5],
    ["B", 29.99, 3],
    ["C", 9.99, 10]
]

for row in data:
    ws.append(row)

Checkpoint: Run the program and open the Excel file. You should see the headers and rows of products. (Ignore “Total” column for now—it’s empty).

Step 4: Automate Formulas

Now let’s add formulas for the “Total” column:

# Add formulas for Total = Price * Quantity
for i in range(2, 5):   # rows 2 to 4
    ws[f'D{i}'] = f"=B{i}*C{i}"

Checkpoint: Re-run the program and open the Excel file. Each row should now calculate automatically.

Step 5: Add Summary Formulas

At the bottom, add summary calculations:

# Add summary rows
ws['B6'] = "Subtotal:"
ws['C6'] = "=SUM(D2:D4)"
ws['B7'] = "Tax (10%):"
ws['C7'] = "=C6*0.1"
ws['B8'] = "Total:"
ws['C8'] = "=C6+C7"

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

Step 6: Save the File

# Save file
wb.save("lab_formula_demo.xlsx")
print("Excel file created successfully!")

 Student Tasks

1. Run the full program and open lab_formula_demo.xlsx.

2. Verify that:

   • The “Total” column auto-calculates.

   • Subtotal, Tax, and Total are correct.

3. Modify the Tax rate to 15% and check if Excel updates automatically.

4. Add a new product (D, Price = 49.99, Quantity = 2) and extend formulas to include it.

 Extra Challenge (Optional)

• Format the Price, Total, and Tax cells as currency.

• Change background color of the header row to make it stand out.