AI & Python #13: How to Automate Your Excel Reporting with Python

Use openpyxl to automate your Excel reporting with Python

Made with Midjourney

Let’s face it; no matter what our job is, sooner or later, we will have to deal with repetitive tasks like updating a daily report in Excel. Things could get worse if you work for a company that doesn’t work with Python because you wouldn't be able to solve this problem by using only Python.

But don’t worry, you still can use your Python skills to automate your Excel reporting without having to convince your boss to migrate to Python! You only have to use a library to tell Excel what you want to do through Python.

Let’s start with this guide!

The Dataset

In this guide, we’ll use an Excel file with sales data that is similar to those files you have as inputs to make reports at work. You can download this file on Kaggle; however, it has a .csv format, so you should change the extension to .xlsx or just download it from this Google Drive link (I also changed the file name to supermarket_sales.xlsx)

Before writing any code, take a look at the file on Google Drive and familiarize yourself with it. That file is going to be the input to create the following report using Python.

Image by author

Let’s automate this Excel report with Python!

Make a Pivot Table with Pandas