Automate Excel Sales Reports with Python

Build a Python script that turns raw CSV data into a formatted Excel report.

Introduction

30 Second Summary

Every office has that one report that takes hours to build by hand. You copy data into Excel, add formulas, format cells, resize columns, build a chart, then do it all again next week.

In this project, you will build a Python script that reads raw sales data from a CSV file and generates a professional Excel report in seconds. One command gives you styled headers, calculated totals, a regional summary, and an embedded bar chart.

What You'll Build

You run one command in the terminal and a polished, multi-sheet Excel workbook appears in your project folder, ready to open, share, or present to your team.

By the end of this project, you'll have:

  • A reusable script you can feed any raw sales CSV and get a formatted report back instantly.
  • A two-sheet workbook with calculated totals, a regional summary, styled blue-and-white headers, currency formatting, and auto-fitted columns.
  • A bar chart embedded directly in the spreadsheet that lets stakeholders see regional revenue at a glance.
  • Secret Mission: Add conditional formatting that automatically highlights high-revenue sales in green and low-revenue sales in red.

Are there any prerequisites?

This project assumes you know basic Python (loops, functions, variables). No prior experience with pandas, openpyxl, or working with classes is required.

Before We Start

Before We Start...

Before we dive in, let's lock in what you're about to build and why it matters.

You're going to create a Python script that takes raw sales data and turns it into a polished Excel report, complete with calculated totals, styled headers, and a bar chart.

Set Up Your Project

Before you can automate anything, you need a workspace and the right tools installed. This project uses two Python libraries: pandas for data processing and openpyxl for Excel formatting.

In this step, you'll create a project folder, install both libraries, and confirm everything works. Once that's done, you're ready to start writing your report script.

In this step, get ready to:
  • Create an excel-automation project folder.
  • Install pandas and openpyxl.
  • Verify both libraries are working.
Create the project folder
  • In VS Code, open the terminal by pressing Ctrl+` (Windows) or Ctrl+` (macOS). The backtick key is usually above Tab.
  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder by running:
mkdir excel-automation
  • Confirm the folder was created by running:
ls

You should see excel-automation listed in the output.

Don't see the folder?

Make sure you ran cd ~/Desktop first. If the Desktop path doesn't exist, try cd $HOME/Desktop instead.

Help me figure out why my folder wasn't created

Open the folder in VS Code
  • Click File in the top menu bar.
  • Click Open Folder.
  • Navigate to your Desktop, select the excel-automation folder, and click Select Folder.

VS Code reloads with the folder open. You should see excel-automation in the Explorer sidebar on the left.

  • Open a fresh terminal by pressing Ctrl+` (Windows) or Ctrl+` (macOS).

Why open the folder first?

VS Code's terminal always starts in the workspace root. By opening the excel-automation folder as your workspace, every new terminal session automatically lands in the right directory.

Install pandas and openpyxl

pandas handles reading CSV files and crunching numbers. openpyxl handles writing and formatting Excel files. You need both.

  • Install both libraries by running this command:
pip install pandas==3.0.4 openpyxl==3.1.5

What does this command do?

  • pip install tells Python's package manager to download and install libraries.
  • pandas==3.0.4 pins pandas to version 3.0.4 so your results match this project exactly.
  • openpyxl==3.1.5 pins openpyxl to version 3.1.5 for the same reason.

You'll see progress bars as pip downloads the packages. The install is complete when you see the terminal return to a blank prompt.

Seeing a permission error?

If you see a permissions error, try adding --user to the end of the command. This installs the packages for your user account only.

If pip is not recognized, make sure Python is added to your system PATH. You may need to close and reopen VS Code after installing Python.

Help me fix my pip install error

Verify the installation

Before you write any code, confirm both libraries import correctly and show the expected versions.

What do you expect to see when you import both libraries and print their versions?

  • Verify both libraries are installed by running:
python -c "import pandas; import openpyxl; print(pandas.__version__, openpyxl.__version__)"

You should see 3.0.4 3.1.5 printed with no errors. That confirms both libraries are installed and ready to use.

Seeing ModuleNotFoundError?

This means pip installed the packages to a different Python than the one VS Code is using. Make sure the Python interpreter selected in VS Code (shown in the bottom status bar) matches the one where you installed the packages.

Try running pip show pandas to check if the package is installed. If it shows version info, the issue is a Python path mismatch.

Help me fix my ModuleNotFoundError

Your project folder is ready and both libraries are installed. Next up, you'll create sample sales data and write your first lines of Python to read it.

Create Sample Data and Read It

Your tools are installed and ready to go. Now you need something for them to work on: realistic sales data that mirrors what you'd find in a real business spreadsheet.

In this step, you'll create a sample dataset and write a script that loads it with pandas. Seeing the data appear in your terminal is your first proof that the automation pipeline works.

In this step, get ready to:
  • Create a CSV file with 20 rows of sample sales data.
  • Write a Python script that reads the CSV into a DataFrame.
  • Run the script and see the data printed in the terminal.
Create the sample sales data

Every automation needs input. You'll create a CSV file that simulates six months of sales records across multiple regions and products.

  • In VS Code, click File in the top menu bar.
  • Click New File.
  • Name the file sales_data.csv and save it inside your excel-automation folder.
  • Paste the following data into the file:
Date,Product,Region,Units Sold,Price Per Unit
2026-01-15,Widget A,North,120,29.99
2026-01-22,Widget B,South,85,49.99
2026-02-03,Widget A,East,200,29.99
2026-02-14,Widget C,West,150,19.99
2026-02-28,Widget B,North,95,49.99
2026-03-05,Widget A,South,175,29.99
2026-03-18,Widget C,East,130,19.99
2026-03-25,Widget B,West,110,49.99
2026-04-02,Widget A,North,145,29.99
2026-04-15,Widget C,South,165,19.99
2026-04-22,Widget B,East,90,49.99
2026-05-01,Widget A,West,180,29.99
2026-05-14,Widget C,North,140,19.99
2026-05-28,Widget B,South,105,49.99
2026-06-03,Widget A,East,160,29.99
2026-06-15,Widget C,West,125,19.99
2026-06-22,Widget B,North,115,49.99
2026-06-30,Widget A,South,190,29.99
2026-07-05,Widget C,East,155,19.99
2026-07-12,Widget B,West,100,49.99

What's in this data?

This CSV has 20 sales records spanning January to July 2026. Each row has a date, product name, region, number of units sold, and price per unit.

The data covers 3 products (Widget A, B, C) across 4 regions (North, South, East, West). This gives you enough variety to produce meaningful summaries and charts later.

  • Save sales_data.csv.
  • Confirm the file appears in the Explorer sidebar on the left. You should see sales_data.csv listed inside your excel-automation folder.
Write the script to read the data

Now you need a Python script that loads this CSV into a DataFrame. A DataFrame is like a spreadsheet in memory, with labeled rows and columns.

  • Create a new file called report_generator.py in the same excel-automation folder.
  • Paste the following code into the file:
import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.chart import BarChart, Reference

# Read the raw sales data
df = pd.read_csv("sales_data.csv")
print("Raw data loaded:")
print(df.head())
print(f"\nTotal rows: {len(df)}")

What does this code do?

  • The first four lines import pandas and openpyxl classes. You only use pandas right now, but the openpyxl imports prepare you for formatting and charting in later steps.
  • pd.read_csv("sales_data.csv") loads your CSV file into a DataFrame called df.
  • df.head() prints the first 5 rows so you can confirm the data loaded correctly.
  • len(df) returns the total number of rows, which should be 20.
  • Save report_generator.py.
Run the script and verify the output

Before you run this, what do you think the terminal will show? Consider what df.head() does and how many rows your CSV has.

  • Run the script by entering this command in your terminal:
python report_generator.py

You should see a formatted table with the first 5 rows of your sales data, followed by the total row count of 20. The output looks like this:

Raw data loaded:
         Date   Product Region  Units Sold  Price Per Unit
0  2026-01-15  Widget A  North         120           29.99
1  2026-01-22  Widget B  South          85           49.99
2  2026-02-03  Widget A   East         200           29.99
3  2026-02-14  Widget C   West         150           19.99
4  2026-02-28  Widget B  North          95           49.99

Total rows: 20

Seeing a FileNotFoundError?

  • Make sure your terminal is in the excel-automation folder. Run dir and confirm sales_data.csv is listed.
  • Check that the filename is exactly sales_data.csv with underscores, not spaces or dashes.
  • If the file is in a different location, move it into the same folder where report_generator.py lives.

Why can't my script find the CSV file?

✔️ Awesome, I've got everything!

Great. Double check you've saved both files.

ⓧ I'd like to double check the full code

Here's the full report_generator.py at this point:

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.chart import BarChart, Reference

# Read the raw sales data
df = pd.read_csv("sales_data.csv")
print("Raw data loaded:")
print(df.head())
print(f"\nTotal rows: {len(df)}")

And here's the full sales_data.csv:

Date,Product,Region,Units Sold,Price Per Unit
2026-01-15,Widget A,North,120,29.99
2026-01-22,Widget B,South,85,49.99
2026-02-03,Widget A,East,200,29.99
2026-02-14,Widget C,West,150,19.99
2026-02-28,Widget B,North,95,49.99
2026-03-05,Widget A,South,175,29.99
2026-03-18,Widget C,East,130,19.99
2026-03-25,Widget B,West,110,49.99
2026-04-02,Widget A,North,145,29.99
2026-04-15,Widget C,South,165,19.99
2026-04-22,Widget B,East,90,49.99
2026-05-01,Widget A,West,180,29.99
2026-05-14,Widget C,North,140,19.99
2026-05-28,Widget B,South,105,49.99
2026-06-03,Widget A,East,160,29.99
2026-06-15,Widget C,West,125,19.99
2026-06-22,Widget B,North,115,49.99
2026-06-30,Widget A,South,190,29.99
2026-07-05,Widget C,East,155,19.99
2026-07-12,Widget B,West,100,49.99

Your data is loaded and pandas can read it. Next up, you'll process this raw data and generate an Excel report. But you'll quickly see that a plain export looks terrible without any formatting.

Process Data and Generate a Plain Report

Your script can load and display raw sales data. That proves the pipeline reads files correctly, but printing rows to a terminal is not something you can hand to a manager.

In this step, you will calculate totals, summarize performance by region, and export everything to an Excel file. The result will work perfectly, but you will notice a gap between "functional" and "professional."

In this step, get ready to:
  • Add a calculated "Total" column and summarize data by region.
  • Export both datasets to a multi-sheet Excel file.
  • Open the report and observe what is missing.
Add a calculated Total column and summarize by region

Managers care about totals and regional breakdowns, not 20 individual rows. You need to compute the revenue per row and then aggregate the numbers by region.

  • In report_generator.py, find this line at the bottom of the file:
print(f"\nTotal rows: {len(df)}")
  • Add the following code below that line to calculate each row's total revenue and create a regional summary:
# Process the data - add a calculated Total column
df["Total"] = df["Units Sold"] * df["Price Per Unit"]

# Create summary by region
region_summary = df.groupby("Region").agg(
    Total_Units=("Units Sold", "sum"),
    Total_Revenue=("Total", "sum"),
    Avg_Price=("Price Per Unit", "mean")
).round(2).reset_index()

print("\nRegional Summary:")
print(region_summary)

What does this code do?

  • df["Total"] = df["Units Sold"] * df["Price Per Unit"] multiplies two columns together and stores the result in a new "Total" column on every row.
  • df.groupby("Region").agg(...) groups all rows by their region, then calculates the sum of units, sum of revenue, and average price for each group.
  • .round(2).reset_index() rounds decimal values to 2 places and converts the grouped result back into a normal DataFrame with a clean index.
  • Save report_generator.py.
  • Run the script to confirm the regional summary prints correctly:
python report_generator.py

You should see the original 5-row preview, the row count, and a new table with 4 rows showing each region's total units, total revenue, and average price.

Seeing a KeyError or NameError?

  • A KeyError means a column name does not match the CSV header exactly. Check that your CSV has Units Sold and Price Per Unit (with spaces and capitals).
  • A NameError on df means the new code was added above the line that creates df. Make sure your new code sits below print(f"\nTotal rows: {len(df)}").
  • Why is my regional summary showing a KeyError on "Total"?
Write both DataFrames to an Excel file

Now that the data is processed, you need to export it to an Excel workbook with two separate sheets. pandas provides ExcelWriter to write multiple DataFrames to different sheets in a single file.

  • Add this code at the bottom of report_generator.py, below the print(region_summary) line:
# Write to Excel (plain, no formatting yet)
with pd.ExcelWriter("sales_report.xlsx", engine="openpyxl") as writer:
    df.to_excel(writer, sheet_name="Sales Data", index=False)
    region_summary.to_excel(writer, sheet_name="Regional Summary", index=False)

print("\nPlain report saved: sales_report.xlsx")

What does this code do?

  • pd.ExcelWriter("sales_report.xlsx", engine="openpyxl") creates an Excel file using openpyxl as the underlying engine.
  • df.to_excel(writer, sheet_name="Sales Data", index=False) writes the full dataset (with the new Total column) to a sheet called "Sales Data".
  • index=False prevents pandas from adding a numeric row index column that would clutter the report.
  • The with block automatically saves and closes the file when the block ends.
  • Save report_generator.py.
  • Run the script again:
python report_generator.py

You should see all the previous output plus a new line: Plain report saved: sales_report.xlsx.

  • Confirm the file was created by running:
ls

You should see sales_report.xlsx listed alongside sales_data.csv and report_generator.py.

Not seeing the .xlsx file?

  • Make sure you saved the file before running the script. Unsaved changes in VS Code do not execute.
  • Check that your terminal is in the excel-automation folder. If not, run cd ~/Desktop/excel-automation and try again.
  • Why is my Excel file not being created?
Open the report and observe the gap

The data is correct. But before you look at the file, ask yourself: what do you expect a professional Excel report to look like?

  • Open sales_report.xlsx in Excel, Google Sheets, or any spreadsheet viewer.
  • Click on the Sales Data sheet tab at the bottom and look at the columns.
  • Click on the Regional Summary sheet tab.

Both sheets have the right numbers. But the report looks like a raw data dump: no styled headers, no currency symbols on dollar values, and columns are too narrow to read their content.

Why does it look so plain?

pandas is a data processing library. It gets numbers into the right cells, but it has no concept of visual formatting. Making a report look professional requires a different layer.

That is exactly what openpyxl handles. In the next step, you will use openpyxl classes like Font, PatternFill, and Alignment to transform this data dump into something presentation-ready.

✔️ Awesome, I've got everything!

Great. Double check you have saved report_generator.py and that running it produces sales_report.xlsx with two sheets.

ⓧ I'd like to double check the full code

Here is the complete report_generator.py at this point. Make sure yours matches exactly:

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.chart import BarChart, Reference

# Read the raw sales data
df = pd.read_csv("sales_data.csv")
print("Raw data loaded:")
print(df.head())
print(f"\nTotal rows: {len(df)}")

# Process the data - add a calculated Total column
df["Total"] = df["Units Sold"] * df["Price Per Unit"]

# Create summary by region
region_summary = df.groupby("Region").agg(
    Total_Units=("Units Sold", "sum"),
    Total_Revenue=("Total", "sum"),
    Avg_Price=("Price Per Unit", "mean")
).round(2).reset_index()

print("\nRegional Summary:")
print(region_summary)

# Write to Excel (plain, no formatting yet)
with pd.ExcelWriter("sales_report.xlsx", engine="openpyxl") as writer:
    df.to_excel(writer, sheet_name="Sales Data", index=False)
    region_summary.to_excel(writer, sheet_name="Regional Summary", index=False)

print("\nPlain report saved: sales_report.xlsx")

Your script now turns raw CSV data into a two-sheet Excel report in seconds. The numbers are all there. Next up, you will use openpyxl to add styled headers, currency formatting, and auto-fitted columns so the report looks like a human spent an hour on it.

Format the Report with openpyxl

You have a working report, but you saw the problem when you opened it. Raw numbers dumped into cells with no headers, no currency symbols, and columns too narrow to read.

Professional reports need styled headers, currency formatting, and readable column widths. This is where openpyxl's styling classes come in. You'll create Python objects like Font, PatternFill, and Alignment that control how Excel renders every cell.

In this step, get ready to:
  • Load the workbook and create reusable style objects.
  • Apply header styling, currency formatting, and auto-fitted columns to both sheets.
  • See the transformation from raw data dump to polished report.
Load the workbook and define style objects

Before you can format cells, you need to load the saved Excel file back into Python as an openpyxl workbook. Then you create style objects that describe how you want headers to look.

  • In report_generator.py, find the last line that says print("\nPlain report saved: sales_report.xlsx").
  • Add the following code below it to load the workbook and define your style objects:
# Format the report with openpyxl
wb = load_workbook("sales_report.xlsx")

# Define styles
header_font = Font(bold=True, color="FFFFFF", size=11)
header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
header_alignment = Alignment(horizontal="center")

What does this code do?

Each line creates a Python object that describes a formatting rule for Excel cells.

  • load_workbook() opens the saved .xlsx file so openpyxl can modify it.
  • header_font creates a font that is bold, white (FFFFFF), and 11pt.
  • header_fill creates a solid blue (4472C4) background color.
  • header_alignment centers text horizontally in the cell.

What are Python classes and objects?

Font(bold=True, color="FFFFFF", size=11) is calling a class to create an object. Think of a class as a template ("what does a font need?") and an object as one specific instance filled in with your values.

You pass parameters like bold=True to customize each object. The object stores those settings and you can reuse it on as many cells as you want.

  • Save the file.
  • Run the script to confirm it still works with no errors:
python report_generator.py

You should see the same output as before (raw data, row count, regional summary, and "Plain report saved") with no errors at the end. The styles are defined but not applied to any cells yet.

Seeing a NameError?

If you see an error mentioning load_workbook or Font, check that the imports at the top of your file include from openpyxl import load_workbook and from openpyxl.styles import Font, PatternFill, Alignment. These were added in the previous step.

help me fix a NameError in my openpyxl formatting script

Format the Sales Data sheet

Now you'll apply those style objects to the Sales Data sheet. Three loops handle three jobs: styling headers, formatting currency columns, and auto-fitting column widths.

  • In report_generator.py, add the following code below the header_alignment line:
# Format the Sales Data sheet
ws = wb["Sales Data"]

for cell in ws[1]:
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = header_alignment

for row in ws.iter_rows(min_row=2, max_row=ws.max_row, min_col=1, max_col=ws.max_column):
    row[4].number_format = "$#,##0.00"
    row[5].number_format = "$#,##0.00"

for col in ws.columns:
    max_length = max(len(str(cell.value or "")) for cell in col)
    ws.column_dimensions[col[0].column_letter].width = max_length + 2

What does this code do?

  • ws[1] gives you the first row (the header row). The loop applies your font, fill, and alignment to every cell in that row.
  • iter_rows() loops through each data row. row[4] is the Price Per Unit column and row[5] is the Total column. Setting number_format to $#,##0.00 displays values like $5,998.00.
  • The third loop measures the longest value in each column and sets the column width to fit. The + 2 adds a small padding.
  • Save the file.
Format the Regional Summary sheet and save

The Regional Summary sheet needs the same header styling, but the currency columns are in different positions. You'll also save the workbook so the formatting persists to disk.

  • Add this code directly below the Sales Data formatting you just wrote:
# Format the Regional Summary sheet
ws2 = wb["Regional Summary"]

for cell in ws2[1]:
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = header_alignment

for row in ws2.iter_rows(min_row=2, max_row=ws2.max_row, min_col=1, max_col=ws2.max_column):
    row[2].number_format = "$#,##0.00"
    row[3].number_format = "$#,##0.00"

for col in ws2.columns:
    max_length = max(len(str(cell.value or "")) for cell in col)
    ws2.column_dimensions[col[0].column_letter].width = max_length + 2

wb.save("sales_report.xlsx")
print("Formatted report saved: sales_report.xlsx")

What does this code do?

  • The same three loops apply to the Regional Summary sheet. The difference is the currency columns: row[2] is Total_Revenue and row[3] is Avg_Price.
  • wb.save() writes all the in-memory formatting changes back to the .xlsx file on disk. Without this line, all your formatting would be lost.
  • Save report_generator.py.

Before you run the script, what do you think will be different about the Excel file this time?

  • Run the script:
python report_generator.py

You should see all the same output as before, plus a new final line: "Formatted report saved: sales_report.xlsx".

  • Open sales_report.xlsx in Excel, Google Sheets, or any spreadsheet viewer.

You should see blue headers with white bold text on both sheets. Monetary values now display with dollar signs (like $5,998.00). Columns are wide enough to read all content without truncation.

Headers not styled or columns still narrow?

  • Make sure you saved report_generator.py before running it. Unsaved changes won't execute.
  • Check that the wb.save("sales_report.xlsx") line is at the very end of your file. Without it, formatting stays in memory and never reaches the file.
  • If you opened the file in Excel before running the script, close it first. Excel locks the file and Python cannot overwrite it.

help me debug my openpyxl formatting

✔️ Awesome, I've got everything!

Great. Make sure you've saved report_generator.py and confirmed the formatted report opens correctly.

ⓧ I'd like to double check the full code

Scroll up and confirm your file contains these sections in order:

  • The imports at the top (pandas, load_workbook, Font, PatternFill, Alignment, BarChart, Reference).
  • The CSV reading, Total column calculation, and groupby summary.
  • The ExcelWriter block that writes both sheets.
  • The load_workbook call and three style object definitions (header_font, header_fill, header_alignment).
  • Sales Data sheet formatting (header loop, currency loop, column width loop).
  • Regional Summary sheet formatting (same three loops with different column indices).
  • wb.save("sales_report.xlsx") and the print statement at the very end.

Your report looks professional now. Blue headers, dollar signs, readable columns. Next up, you'll add a bar chart that visualizes the regional revenue comparison at a glance.

Add a Bar Chart

Your report now has professional formatting with styled headers, currency symbols, and readable column widths. The next step is adding a visual element that makes the data instantly understandable.

A bar chart lets stakeholders compare regional revenue at a glance without reading individual numbers. This is the visual punch that separates an automated report from a formatted data dump.

In this step, get ready to:
  • Create a bar chart object with a title, axis labels, and a style.
  • Define data ranges that tell the chart which cells to plot.
  • Insert the chart into the Regional Summary sheet.
Create the bar chart and define its data

The BarChart class from openpyxl creates a chart object you configure with properties like title and axis labels. The Reference class tells the chart which cells in your spreadsheet contain the data to plot.

  • In report_generator.py, find the wb.save("sales_report.xlsx") line near the bottom of the file.
  • Add the following code directly above that line:
# Add a bar chart to the Regional Summary
chart = BarChart()
chart.title = "Revenue by Region"
chart.style = 10
chart.y_axis.title = "Revenue ($)"
chart.x_axis.title = "Region"

data = Reference(ws2, min_col=3, min_row=1, max_row=ws2.max_row)
categories = Reference(ws2, min_col=1, min_row=2, max_row=ws2.max_row)
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
chart.shape = 4

ws2.add_chart(chart, "A" + str(ws2.max_row + 3))

What does this code do?

  • BarChart() creates a bar chart object. You set its title, style, and axis labels as properties.
  • Reference(ws2, min_col=3, min_row=1, max_row=ws2.max_row) points at column 3 (Total_Revenue) of the Regional Summary sheet. This is the data the bars represent.
  • Reference(ws2, min_col=1, min_row=2, max_row=ws2.max_row) points at column 1 (Region names) starting from row 2 to skip the header. These become the x-axis labels.
  • titles_from_data=True tells the chart to use the first cell in the data reference (the header "Total_Revenue") as the series name in the legend.
  • ws2.add_chart(chart, "A" + str(ws2.max_row + 3)) places the chart 3 rows below the last row of data so it does not overlap your summary table.
Run the script and verify the chart
  • Find the last line of the file:
print("Formatted report saved: sales_report.xlsx")
  • Replace it with this updated message:
print("Formatted report with chart saved: sales_report.xlsx")
  • Save report_generator.py.

Before you run the script, what do you expect to see in the terminal output compared to last time?

  • Run the script by entering this command in your terminal:
python report_generator.py

You should see the same data output as before, but the final line now reads Formatted report with chart saved: sales_report.xlsx.

  • Open sales_report.xlsx in Excel or Google Sheets.
  • Switch to the Regional Summary sheet and scroll down below the data table.

You should see a "Revenue by Region" bar chart with 4 bars, one for each region (East, North, South, West).

Don't see the chart?

  • Make sure you pasted the chart code ABOVE the wb.save() line. If the chart code comes after the save, it never gets written to the file.
  • Check that you used ws2 (the Regional Summary sheet variable) in the Reference() calls and add_chart(), not ws.
  • Close and reopen the Excel file. Some viewers cache the old version.

Why isn't my bar chart showing up in the Regional Summary sheet?

✔️ Awesome, I've got everything!

Great. Make sure you have saved report_generator.py.

ⓧ I'd like to double check the full code

Your report_generator.py file is now longer than 30 lines. Scroll through and confirm you have these sections in order:

  • Imports at the top (pandas, load_workbook, Font, PatternFill, Alignment, BarChart, Reference).
  • Read CSV, process data, create regional summary, write to Excel.
  • Load workbook, define styles, format Sales Data sheet, format Regional Summary sheet.
  • Bar chart creation: chart = BarChart() through ws2.add_chart(chart, ...).
  • wb.save("sales_report.xlsx") followed by the print statement "Formatted report with chart saved: sales_report.xlsx".

Your automated report now has formatted data AND a visual chart. One command, and you get a presentation-ready Excel file. Next up, you'll take on a secret mission to add conditional formatting that color-codes revenue values automatically.

Secret mission

Add Conditional Formatting

Your report looks professional, but stakeholders still have to read every number to spot the winners and losers. What if the spreadsheet could highlight them automatically? Add conditional formatting that turns high-revenue cells green and low-revenue cells red so patterns jump off the page.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally, so there are no ongoing costs. Decide whether to keep your project files for future use or delete them entirely.

Resources you used:

  • The excel-automation project folder on your Desktop (contains report_generator.py, sales_data.csv, and sales_report.xlsx).
  • Python packages installed via pip: pandas and openpyxl.

Keep everything running

No action needed. Choose this if you want to keep tweaking your report script or adapt it for your own data.

  • Your excel-automation folder stays on your Desktop with all files intact.
  • The pandas and openpyxl packages remain installed in your Python environment.
  • You can re-run python report_generator.py any time to regenerate the report.

Pause - I'll come back to this later

There are no running processes to stop. The script runs once and exits, so there is nothing to pause.

  • Your project folder and installed packages stay as they are.
  • When you come back, open the excel-automation folder in VS Code and run the script again.

Delete - I don't want to use this again

Remove all project files and uninstall the packages to start fresh.

  • Delete the project folder by running this command in your terminal:

macOS

cd ~/Desktop
rm -rf excel-automation

Windows

cd ~/Desktop
Remove-Item -Recurse -Force excel-automation
  • Uninstall the Python packages by running:
pip uninstall pandas openpyxl -y
  • This removes both libraries from your Python environment.

Nice Work!

Nice Work!

Nice work! You just built a Python automation script that transforms raw CSV sales data into a professional, formatted Excel report in seconds.

Here's what you learned:

  • Built a Python script that reads raw CSV sales data, calculates totals, aggregates by region, and generates a multi-sheet Excel report in one command.
  • Used pandas for data processing (reading files, adding calculated columns, groupby aggregation) and openpyxl for Excel formatting (styled headers with Font and PatternFill objects, currency number formats, auto-fitted column widths).
  • Added a bar chart to visualize regional revenue at a glance, turning a data table into a stakeholder-ready report.
  • Secret Mission: Added conditional formatting rules that automatically color-code revenue values green for high performers and red for underperformers.

Ready to quiz yourself?