Lesson 19 · Data analytics zero to hero

Automate Excel with Python | Data Analytics #19

Video nineteen of the 30-part series, and the start of an Excel block: building a real workbook, formulas, and formatting entirely with Python, no manual…

⬇ Download notebook ⬇ Notebook + data (.zip)Open in Colab ↗

CourseData analytics zero to hero
Lesson19 of 30
Video12 min
FormatJupyter notebook · 10 code cells
Data1 dataset

openpyxl pandas

What you'll learn

Preparing a Real Summary with pandas
Creating a Workbook and Writing Real Data
Writing Real Excel Formulas
Formatting Headers and Numbers
Conditional Formatting
Saving and Reading It Back
Wrap-Up: What You Learned

Datasets used in this lesson

Save these next to the notebook. In Google Colab, upload them with the 📁 icon on the left first.

superstore_sales.csv715.5 KB

📓 Full notebook

Download .ipynb

Data Analytics Zero to Hero, Video 19: Excel Automation with Python#

Video nineteen of the 30-part series, and the start of an Excel block: building a real workbook, formulas, and formatting entirely with Python, no manual spreadsheet work.
We're using openpyxl on a real summary computed from the Sample Superstore dataset.
Let's jump straight in.

Before You Start#

Open a new Jupyter Notebook in VS Code and select your Python interpreter as the kernel.
Install openpyxl if you haven't already: pip install openpyxl.
Place superstore_sales.csv in the same folder as this notebook.

Part 1: Preparing a Real Summary with pandas#

import pandas as pd
import openpyxl

df = pd.read_csv('superstore_sales.csv')
summary = df.groupby(['Region', 'Category'])[['Sales', 'Profit']].sum().round(2).reset_index()
print(summary.head(6))
print(summary.shape)

    Region         Category      Sales    Profit
0  Central        Furniture  163797.16  -2871.05
1  Central  Office Supplies  167026.42   8879.98
2  Central       Technology  170416.31  33697.43
3     East        Furniture  208291.20   3046.17
4     East  Office Supplies  205516.06  41014.58
5     East       Technology  264973.98  47462.04
(12, 4)

Part 2: Creating a Workbook and Writing Real Data#

wb = openpyxl.Workbook()
ws = wb.active
ws.title = 'Regional Summary'
print(ws.title)

Regional Summary

headers = list(summary.columns)
ws.append(headers)
for row in summary.itertuples(index=False):
    ws.append(list(row))
print(f'Wrote {ws.max_row} rows and {ws.max_column} columns')

Wrote 13 rows and 4 columns

Part 3: Writing Real Excel Formulas#

total_row = ws.max_row + 1
ws.cell(row=total_row, column=1, value='Total')
sales_col = headers.index('Sales') + 1
profit_col = headers.index('Profit') + 1
from openpyxl.utils import get_column_letter
sales_letter = get_column_letter(sales_col)
profit_letter = get_column_letter(profit_col)
ws.cell(row=total_row, column=sales_col, value=f'=SUM({sales_letter}2:{sales_letter}{total_row - 1})')
ws.cell(row=total_row, column=profit_col, value=f'=SUM({profit_letter}2:{profit_letter}{total_row - 1})')
print(f'Total formula row written at row {total_row}')

Total formula row written at row 14

Part 4: Formatting Headers and Numbers#

from openpyxl.styles import Font, PatternFill, Alignment

header_font = Font(bold=True, color='FFFFFF')
header_fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')
for cell in ws[1]:
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = Alignment(horizontal='center')
print('Real header formatting applied')

Real header formatting applied

for row in ws.iter_rows(min_row=2, min_col=sales_col, max_col=profit_col):
    for cell in row:
        cell.number_format = '$#,##0.00'
print('Real currency formatting applied to Sales and Profit')

Real currency formatting applied to Sales and Profit

for col_num in range(1, ws.max_column + 1):
    letter = get_column_letter(col_num)
    ws.column_dimensions[letter].width = 16
ws.freeze_panes = 'A2'
print('Real column widths and freeze panes set')

Real column widths and freeze panes set

Part 5: Conditional Formatting#

from openpyxl.formatting.rule import ColorScaleRule

rule = ColorScaleRule(start_type='min', start_color='F8696B', end_type='max', end_color='63BE7B')
profit_letter_range = f'{profit_letter}2:{profit_letter}{ws.max_row - 1}'
ws.conditional_formatting.add(profit_letter_range, rule)
print(f'Real color scale applied to {profit_letter_range}')

Real color scale applied to D2:D13

Part 6: Saving and Reading It Back#

wb.save('regional_summary.xlsx')
print('Real workbook saved as regional_summary.xlsx')

Real workbook saved as regional_summary.xlsx

check_wb = openpyxl.load_workbook('regional_summary.xlsx')
check_ws = check_wb.active
print(check_ws.title)
print(check_ws['A1'].value, check_ws['B1'].value)
print(f'Formula in {sales_letter}{total_row}:', check_ws.cell(row=total_row, column=sales_col).value)

Regional Summary
Region Category
Formula in C14: =SUM(C2:C13)

Wrap-Up: What You Learned#

Preparing a real summary with pandas groupby before handing it to openpyxl.
Creating a Workbook, renaming a worksheet, and writing real rows with append and cell.
Writing genuine Excel formulas as strings, computed live by Excel itself when opened.
Formatting: Font, PatternFill, Alignment, number formats, column widths, and freeze_panes.
Conditional formatting with a real ColorScaleRule.
Saving and reopening the file to confirm everything genuinely took.
All built on a real summary of the Sample Superstore dataset. Video twenty continues the Excel block with pivot-style reports and charts, still entirely in Python. Subscribe so it lands automatically see you there.

Found this useful?

All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.

☕ Buy me a coffee Other ways to help