Automating Multi‑Sheet Excel Sales Analysis with Python

Programmers familiar with Excel or Tableau know these tools are handy for data analysis, but manual operations become cumbersome when handling many files. The author was asked to compute the top‑5 brands by total sales over the past year across 20 brands and 128 categories, each represented by a separate spreadsheet.

Processing each sheet manually would require 128 separate aggregations and a final merge, a time‑consuming task. Instead, a colleague demonstrated a Python solution using only a few lines of code.

The workflow begins by importing necessary modules and opening each spreadsheet. Pandas is used to calculate sales where a direct sales column is missing, by multiplying visitor count, conversion rate, and average order value.

After aggregating sales per brand within each category, the results are combined to obtain total sales for each brand over the year. To avoid overwriting files, the original filenames (without extensions) serve as unique identifiers.

Using os.listdir to iterate over all files and time to measure performance, the script processes all 128 sheets in under three seconds, averaging 0.02 seconds per file.

A preview step confirms the data, and a formatting adjustment restores scientific notation values to regular numbers. The author concludes that while Excel is useful, Python can achieve the same analysis orders of magnitude faster.