You don't need to be a coder to use Python in Excel for data cleaning

Python in Excel makes data cleaning easier than formulas ever did—no coding background required.

You don't need to be a coder to use Python in Excel for data cleaning

[Python code in Excel.]

Image taken by Yadullah Abidi | No attribution required.

Yadullah Abidi

Mar 1, 2026, 7:00 AM EST

Yadullah Abidi is a Computer Science graduate from the University of Delhi and holds a postgraduate degree in Journalism from the Asian College of Journalism, Chennai. With over a decade of experience in Windows and Linux systems, programming, PC hardware, cybersecurity, malware analysis, and gaming, he combines deep technical knowledge with strong editorial instincts.

Yadullah currently writes for MakeUseOf as a Staff Writer, covering cybersecurity, gaming, and consumer tech. He formerly worked as Associate Editor at Candid.Technology and as News Editor at The Mac Observer, where he reported on everything from raging cyberattacks to the latest in Apple tech.

In addition to his journalism work, Yadullah is a full-stack developer with experience in JavaScript/TypeScript, Next.js, the MERN stack, Python, C/C++, and AI/ML. Whether he's analyzing malware, reviewing hardware, or building tools on GitHub, he brings a hands-on, developer’s perspective to tech journalism.

If you heard Python was coming to Excel and thought it was another thing you needed to learn to code, I've got good news. Yes, Python in Excel is a powerful way of cleaning up and organizing your data, but you don't need to be a programmer to use it at all.

Just like using basic VBA scripting in Excel makes it much better, knowing a few Python commands and how to use them can elevate your Excel experience. I've spent years watching Excel users struggle with data that looks like it was entered by five different people with five different ideas about formatting. And honestly, Python in Excel is one of the most beginner-friendly solutions for tackling these headaches.

###
Getting started is easier than you think

####
Microsoft hid the hardest parts and left you with the useful ones

[Inserting Python code in Excel sheet.]

Screenshot provided by Yasir. | No attribution required.

If you have a Microsoft 365 subscription (Business or Enterprise), you already have access to Python in Excel. No installation, no setup, no downloading mysterious packages from the internet.

To activate it, just click the Formulas tab and select Insert Python. Even simpler? Type =PY into any cell and press Tab. That's it. You're now in Python mode. There's no separate window, no black terminal screen, no intimidating command prompts. It's just Excel, but with Python superpowers.

Although there's a range of Python functions and syntax in Excel, you don't need to memorize any Python code. Microsoft has integrated Copilot with Python in Excel. You can literally type any tasks you want in plain English, like "remove duplicates from this list" or "fill missing sales values with the average," and Copilot writes the Python code for you. This is a great way of learning Python in Excel, as you can see the code and modify it as needed.

###
Using Python in Excel

####
Python handles messy data that would take dozens of formulas

When you select your data in Excel and use the xl() function, Python creates something called a DataFrame. Think of it as Excel creating a super-smart table that knows how to clean itself.

Let's say you have a customer list with 500 rows, and you suspect there are duplicates. In traditional Excel, you'd have to use the Remove Duplicates option in the Data menu, select columns, and hope for the best.

With Python in Excel, you select your data range and type two lines:

df = xl("A1:D500", headers=True)
df.drop_duplicates()

[Duplicate removal python code in Excel.]

Screenshot provided by Yasir. | No attribution required.

The same concept applies if you're dealing with missing data. Empty cells are the bane of every analyst's existence. Sometimes you want to fill them with zeros, sometimes with averages, and sometimes you want to delete empty rows entirely.

Let's say you want to fill missing values in a spreadsheet's sales column with the median value. Just run the following line:

df['Sales'].fillna(df['Sales'].median(), inplace=True)

[Median calculation Python code in Excel.]

Screenshot provided by Yasir. | No attribution required.

This single line finds all the empty cells in your sales column and fills them with the median value from that same column. Try that with standard Excel formulas and you'll end up with a mess of helper columns before you know it.

Another common example of Python's helpfulness in Excel is text cleanup. Ever received a dataset where someone typed "USA", "U.S.A", "United States", and "US" to mean the same thing? This inconsistency can break pivot tables, ruins charts, and makes analysis nearly impossible.

Python's replace() function handles this elegantly:

df['Country'].replace(['U.S.A.', 'United States', 'US'], 'USA', inplace=True)

[Abbreviation correction Python code in Excel.]

Screenshot provided by Yasir | No attribution required.

This finds every variation and standardizes them to "USA". You can handle dozens of replacements in seconds, something that would take multiple Find & Replace operations in regular Excel.

You can do this with dates as well. If you've got an Excel spreadsheet with dates entered in every imaginable format, you can standardize everything in seconds with a single line of code:

df['Date'] = pd.to_datetime(df['Date'])

[Python code for correcting date and time in Excel.]

Screenshot provided by Yasir. No attribution required.

Python's pandas library, which comes built-in with Python in Excel, automatically recognizes most date formats and converts them to a standard format. Once again, it'd take multiple Find & Replace operations and a ton of time to fix this otherwise.

Another function I love in Python for Excel is describe(). It gives you an instant statistical summary of your entire dataset.

df.describe()

[Data describing python code in Excel.]

Screenshot provided by Yasir | No attribution required.

This single command returns the count, mean, standard deviation, minimum, maximum, and quartiles for every numeric column in your data. It's like having Excel's Analysis ToolPak condensed into one command, making it incredibly easy to quickly spot outliers in your data.

###
Once you try it, there’s no going back

####
Python in Excel changes how you think about cleaning spreadsheets

Data cleaning with Python in Excel isn't about becoming a programmer. It's about having more powerful tools to handle the messy reality of real-world data. The aforementioned Python functions solve a majority of common data cleaning problems with simple, readable commands.

[Excel CPU pricing table above the XLOOKUP function]

The Excel functions I use most (and why they’re so useful)

I use these four excel functions every time I have to handle messy spreadsheets.

Jayric Maning

You already know Excel. Python in Excel just gives you a more direct way to tell your spreadsheet what you want—without clicking through twelve different menus or creating elaborate formula chains that break the moment someone adds a new row.

Start small, and before you know it, you'll be handling data cleaning tasks in seconds that used to take hours. And you still won't be a coder, just someone who knows how to use the right tool for the job.

####
Technology Explained

####
PC & Mobile

[HDTV on a TV stand, up against a wall]

HDR was supposed to make everything look better — but do you even notice it?

I thought I knew Linux… until I tried this distro

[Close-up of a braided USB-C cable.]

I stopped buying random USB‑C cables after learning what 'USB4' actually means

See More

[...]

Original source