Automate Excel Reports with Visible Formulas Using XlsxWriter in Python

Automate Excel report creation with Python by writing explicit formulas that any Excel user can read.

Samir Saci

Published in

TDS Archive

5 min readAug 16, 2022

A group of three people sitting around a wooden table with laptops. One person is working on a laptop displaying a calendar or scheduling tool in an Excel-like interface. The individual is holding a coffee cup while taking notes. This image illustrates a collaborative working environment where team members are discussing or reviewing data on their laptops. — Photo by Windows on Unsplash

Learn how to create user-friendly Excel reports with Python using the XlsxWriter library.

By incorporating explicit Excel formulas, your reports can be easily read and edited by anyone without any programming experience.

Problem Statement

The main issue with an Excel automation script written using Python Pandas is users' experience without programming skills.

Your tool can be seen as a black box that takes data from an Excel file, processes it on the back end and exports it to another Excel file.

Some users cannot trust a report if they can’t access and modify the formula to populate the results.

As a data scientist, how you can improve the transparency of your tools?

Therefore, replacing Excel with Python can impact the user acceptance of your solutions.

Objective

In this article, I propose an alternative to overcome this problem with the Python library xlswriter.

How do you build Excel automation tools with Python?

Situation: Sales Analytics in a Fashion Retail Company

You are a data analyst in a fashion retail company responsible for sales reporting.

Reports are generated by the system that manages the point of sales in stores.

In these reports, you have

Sales quantity in pieces
Items code with a total of 50 items
Date and week covering a full year

Task: Create Sales Reports by Family

For reporting purposes, you need to process these reports and map the dataset with additional features that will be used to analyze sales trends.

These additional features are linked with the item code

Item Family: Leather Goods, Accessories, Ready-to-wear or Others
Scope: boolean value to inform if the item is in the scope of analysis

Objective: Automate the Report Creation

As you need to perform this task more than a hundred times per month, you are looking for a solution to automate the process.

🏫 Discover 70+ case studies using Python to automate reporting and support business optimization 🏪 in this Cheat Sheet

Solutions: Pandas vs. XlsxWriter

Initial solution using Python Pandas

Initially, you built a Python solution that automated the processing using pandas.

Data is imported from several Excel files using pandas_read excel, processed by your script and exported to another Excel file.

he image titled ‘Design Automation Tools for Excel Users’ illustrates four steps to create Excel automation tools for sales analytics using Python. The first step is ‘Data Analysis to Provide Insights’ represented by a chart icon. The second step is ‘Automate the Calculation Using Python,’ symbolized by a calculator. The third step shows ‘Export Your Python Code in Executable File (.exe)’ with a robot icon, and the fourth step is ‘Share Your File with Your Colleagues’ with a sharing icon. — Initial Solution — (Image by Author)

This script was converted to an executable file (.exe), so your colleagues can use it without your support.`

Design and test your Python script
Use py installer to export your Python script in an executable file
Share your .exe file (with detailed instructions) with your colleagues

For more information on how to build this solution,

Build Excel Automation Tools with Python

Design of Excel Automation Tools for Sales Analytics ready to be used by your colleagues without any prior knowledge of…

medium.com

Issues with user acceptance when you use pandas

Some users were complaining that they could not access the formula used to process the data.

Hello Samir, why can’t we see the formulas in the final report? Are you sure that you take the right columns?

This issue raised some concerns among your colleagues who were questioning the tool's accuracy as they couldn’t check it.

New solution with xlsxwriter for transparent reports

The idea is to use the Python library xlsxwriter to perform the calculation in the Excel file.

You can write formulas in Excel cells that the users of the output file can read (and modify).

Screenshot of an Excel sheet with a VLOOKUP formula being used. The formula in cell E2 is =VLOOKUP(B2,’Product Family’!A:B,2,FALSE) and returns ‘Leather Goods’ in the ‘Product Family’ column. The data includes columns for ‘date,’ ‘week,’ ‘item,’ ‘sales,’ and ‘Product Family.’ This showcases a typical use of VLOOKUP in Excel to match and return data from another table — Example of a formula generated by xlsxwriter — (Image by Author)

Thus, you keep the automation capabilities of Python while providing more visibility to your users who are only familiar with Excel.

Implementation of the solution with Python’s xlsxwriter

If you look at the documentation of xlsxwriter, you will find several methods to create formulas on Excel cells.

You can install this library using Pip

pip install xlsxwriter

You can write formulas applied to a single cell,

Also, apply your formula to an array,

Screenshot of an Excel sheet showing a formula in cell G2 that calculates ‘Sales Scope’ as =F2*D2, multiplying the ‘Scope’ value by the ‘sales’ value. The columns include ‘date,’ ‘week,’ ‘item,’ ‘sales,’ ‘Product Family,’ ‘Scope,’ and ‘Sales Scope.’ This illustrates how formulas can be applied in Excel to calculate values based on existing data. — Final results — (Image by Author)

You can then build a solution for our easy-processing task

Screenshot of an Excel sheet showing a table with sales data for ‘Leather Goods.’ The columns include ‘date,’ ‘week,’ ‘item,’ ‘sales,’ ‘Product Family,’ ‘Scope,’ and ‘Sales Scope.’ The data displays daily sales over several weeks, with values in the ‘Sales Scope’ column reflecting calculations based on sales and scope. This example demonstrates how detailed sales data is organized and processed in Excel. — Final Results — (Image by Author)

The three additional columns are generated using Excel formulas that can be read on each cell.

Conclusion

To wrap up, XlsxWriter offers a user-friendly approach to automating Excel reports with Python.

By providing users with access to the formulas used to process data, we have addressed the issue of user acceptance that can arise when using Python for automation.

Low processing speed

However, because of the structure of the library and the computing power needed to create formulas in an Excel file, you will lose processing speed.

Limited functionalities

Beyond the limitations of Excel formulas, you also need to cope with the limited functions of the xlsxwriter.

For instance, you cannot build pivot tables with it.

For advanced calculation and processing, you must educate your users and bring transparency using another way to get their trust.

About Me

Let’s connect on Linkedin and Twitter. I am a Supply Chain Engineer who uses data analytics to improve logistics operations and reduce costs.

For consulting or advice on analytics and sustainable supply chain transformation, feel free to contact me via Logigreen Consulting.

If you are interested in Data Analytics and Supply Chain, look at my website.

Samir Saci | Data Science & Productivity

A technical blog focusing on Data Science, Personal Productivity, Automation, Operations Research and Sustainable…

samirsaci.com

💌 New articles straight in your inbox for free: Newsletter