If you regularly fix duplicate rows, inconsistent names, missing values, or messy columns in Excel, you are probably doing work that Python can repeat for you. A small data-cleaning script can turn a tedious manual routine into a repeatable process.
1. Make a Copy of the Original File
Always keep the raw spreadsheet untouched. Create a working copy for the cleaning process and save the final output as a separate file.
This gives you a reliable source to compare against if something goes wrong and makes your workflow much safer when you are dealing with client or business data.
2. Identify the Problems Before Writing Code
Open a sample of the spreadsheet and list the issues you see. Common examples include duplicate records, extra spaces, inconsistent capitalisation, blank fields, different date formats, and phone numbers stored in several formats.
Writing down the rules first makes the script easier to maintain. Instead of building a giant script that tries to guess what is wrong, give each cleaning rule a clear purpose.
3. Load the Spreadsheet with Python
For many Excel workflows, the pandas library is a convenient starting point. It lets you load tabular data into a DataFrame, inspect columns, transform values, remove duplicates, and export the cleaned result.
Keep the first version simple. Load the file, print the column names, inspect a few rows, and confirm that Python is reading the data the way you expect before changing anything.
4. Standardise Text Fields
Names, company names, cities, and other text fields often contain unnecessary spaces or inconsistent capitalisation. Standardising these fields makes searching, matching, and deduplication much easier.
For example, you might trim leading and trailing spaces and apply a consistent capitalisation rule. The exact rule should depend on the type of data rather than blindly changing every value.
5. Remove Duplicates Carefully
Duplicate removal is useful, but it should be based on sensible identifying fields. Two rows with the same company name are not automatically duplicates, and two people at the same company are obviously not the same contact.
Choose a combination of columns that represents the record you are trying to identify. Then review a sample of the removed rows before relying on the process at scale.
6. Handle Missing Values with Rules
A blank value does not always mean the same thing. Sometimes it should remain blank; sometimes it can be filled from another column; and sometimes the record should be flagged for manual review.
Create explicit rules for each important field. This is much safer than filling every blank cell with a generic value that may later look like real information.
7. Export a Clean, Repeatable Result
Once the cleaning rules work, export the result to a new Excel file and keep the script. The real advantage is that you can run the same process again when the next batch arrives.
You can also add a simple log showing how many rows were loaded, how many duplicates were removed, and how many records still need attention. That turns a one-off script into a useful data-quality workflow.
8. Turn the Script into a Workflow
If the same files arrive every week, you can take the next step by adding a folder-based workflow, scheduled execution, validation checks, or a small desktop interface.
The aim is not to make the code complicated. It is to make the boring part repeatable so you can spend your time checking the results instead of repeatedly fixing the same spreadsheet problems.
Make the Cleanup Repeatable
The biggest benefit of Python data cleaning is consistency. Once your rules are clear and tested, the same process can be reused across hundreds or thousands of rows. Start with one spreadsheet, automate the repetitive fixes, and improve the workflow as your data grows.