Python · Intermediate level

Intermediate level · Module 5: Automating Everyday Office Tasks with Python · Lesson 2 of 4

Creating Excel Reports with openpyxl

Generate formatted Excel workbooks from data so reports are produced in seconds instead of hours.

In this lesson you will

  • Create sheets, write rows and apply number formats
  • Add totals and a simple chart

Key concepts

Workbook/Worksheetcell formattingformulas in cellsopenpyxl charts

The lesson

Managers and donors usually want reports in Excel. openpyxl creates and edits .xlsx files from Python: add sheets, write values and formulas, format cells and insert charts.

Generating the workbook from data means the report is produced the same way every month in seconds, without copy-and-paste mistakes. Write formulas such as '=SUM(B2:B31)' into cells so the totals stay live if someone edits a value.

Formatting matters: bold headers, number formats with thousands separators, sensible column widths and a clear chart make a generated report look professional.

Step by step

  1. Create a workbook and one sheet per product category.
  2. Write headers and data rows from your sales data.
  3. Add a totals row with SUM formulas and format the numbers.
  4. Add a bar chart to a summary sheet and save the file.

Practice activity

Generate a monthly sales workbook with one sheet per product category, a totals row and a bar chart on a summary sheet.

Expected result: A workbook that opens in Excel with formatted data, totals and a chart.

Check your understanding

  1. Why write a SUM formula into the totals cell instead of the calculated number?

    Show answer

    The total stays correct if someone edits the data, and readers can see how it was calculated.

Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.