Python Excel Automation for Beginners

Excel is good at a great many things and bad at exactly one: doing the same thing twice. Macros exist, and they solve this in theory. In practice, VBA is a language from 1993 that breaks when the file moves to a different machine, when the column order changes, or when someone opens the file in Google Sheets and saves it back.

Python reads Excel files without opening Excel. The script does not care where the file lives, what it is called, or how many columns moved since last month. It reads what is there, does what you tell it, and writes a new file. The original stays untouched.

That last point matters more than it sounds. A script that overwrites your source data is a script you will eventually regret. The ones in this guide always read from one file and write to another.

The library is called openpyxl for .xlsx files, or pandas if the job involves filtering and summarising. Both install with a single terminal command and both have been stable for years.

A script that reads an Excel file, keeps only the rows where column B is above a threshold, and writes the result to a new file is about fifteen lines. That is not a simplification. It is fifteen lines, including the lines that open and save the file.

The structure is always the same: open the file, loop through the rows, keep the ones that match a condition, write them out. Changing the condition is changing one line. Changing which column to check is changing one letter. Once you have this template, every spreadsheet task you do by hand becomes a question of 'is this worth fifteen minutes of editing a script?', and the answer is almost always yes if you do the task more than twice.

Filtering is the easy part. Cleaning is where the real time goes.

Duplicate rows that should not be there. Dates in three different formats within the same column. Leading spaces that make two identical-looking values fail a comparison. Currency symbols pasted into number cells. Empty rows scattered through the middle of the data.

All of this is fixable by hand, and all of it is fixable faster by script. The advantage is consistency as much as speed. A script that strips leading spaces will strip every leading space, every time, in every row. A human doing it by hand will miss the one in row 847.

The pandas library has specific functions for each of these: drop_duplicates(), to_datetime(), str.strip(). They read like instructions because that is what they are.

The task that justifies learning this is not the single file. It is the twelve files that arrive every month, each one requiring the same transformation.

A script that processes one file processes twelve with a loop. The loop reads every .xlsx file in a folder, applies the same cleaning and filtering, and writes each result to an output folder. Twelve files that took eight minutes each, an hour and a half of mechanical work, now take four seconds and zero attention.

This is the same directory-walking pattern used in organising files by type. The structure is identical: find the files, check a condition, do something. The only difference is that the 'something' here is reading rows instead of moving files.

Once you are comfortable reading and writing Excel files, the next question is usually 'can I get the data into the spreadsheet automatically?' instead of copying it from a web page or an email.

That is a different problem, pulling data from a web page, but it connects directly. The web scraping script writes to a CSV or Excel file. The cleaning script reads that file. Chain them together and you have a pipeline that runs on a schedule, from raw data to clean report, with no human touching anything in between.

That pipeline is four scripts total, each under thirty lines. It sounds like programming. It is, technically. But it is closer to plumbing than engineering: connecting things that already exist in the right order.

Pick the spreadsheet task you dread most. The one that arrives regularly, takes too long, and is never interesting. Write the fifteen-line script for that one task.

Do not try to automate everything. Do not build a dashboard. Write one script for one file, run it, and see if the forty minutes come back. If they do, the second script is easier. If they do not, nothing was lost. Fifteen minutes is a reasonable bet against forty recurring ones.