Stop Using VLOOKUP: How I Automate 4 Hours of Excel Work in 4 Seconds with Python

#excel #python #pandas #datascience

We have all been there. It's Friday afternoon, and you have to generate the "Weekly Sales Report."
This involves:
Opening 5 different CSV files sent by 5 different regional managers.
Copy-pasting them into one master sheet.
Cleaning up the date formats because John in Sales sent his as DD-MM-YYYY but the system needs MM-DD-YYYY.
Running a VLOOKUP against the "Product Master" sheet to get prices.
Creating a Pivot Table.
If you are lucky, this takes an hour. If you are unlucky, Excel crashes because you hit the 1,048,576 row limit.
In 2026, manual spreadsheet wrangling is obsolete. Here is how I replaced this entire workflow with a Python script using Pandas.
1. The Setup: Ingesting Data instantly
Instead of opening files one by one, we use Python's glob library to find all files in a folder and read them into a Pandas DataFrame.

import pandas as pd
import glob

Find all CSVs

files = glob.glob("sales_data/*.csv")

Read and combine them in one line

df = pd.concat([pd.read_csv(f) for f in files])

Result: 50 files merged in 0.5 seconds.

2. The Clean-Up: Handling Messy Data
In Excel, fixing date formats or removing duplicates requires filters and formulas. In Pandas, it is explicit and repeatable.

Convert text to actual datetime objects

df['Date'] = pd.to_datetime(df['Date'], errors='coerce')

Remove rows with missing Order IDs

df = df.dropna(subset=['OrderID'])

The "VLOOKUP" Killer: pd.merge VLOOKUP is slow and fragile. If you insert a column, it breaks. Pandas uses SQL-style joins. It is robust and incredibly fast.

products = pd.read_excel("product_master.xlsx")

Left Join sales with products

merged_df = df.merge(products, on="ProductID", how="left")

This joins millions of rows instantly without freezing your computer.

4. The Output: Formatted Excel
You might think, "But my boss needs an Excel file, not a Python script."
No problem. Python can write Excel files with formatting (bold headers, charts) using XlsxWriter.

merged_df.to_excel("Final_Report.xlsx", index=False)

Conclusion
The best part about this script? You write it once.
Next Friday, when the new files arrive, you don't do the work. You just run the script.
You have turned a 4-hour chore into a button press.
Hi, I'm Frank Oge. I build high-performance software and write about the tech that powers it. If you enjoyed this, check out more of my work at frankoge.com