Data Cleaning in Excel: Step-by-Step Guide (and When to Switch Tools)

TLDR: Excel handles basic data cleaning well: TRIM for whitespace, Power Query for deduplication and splitting, Text-to-Columns for dates, and Find & Replace for casing. It struggles beyond a few hundred thousand rows. When files get large or you repeat the same steps monthly, a dedicated tool that saves the workflow as a reusable recipe is faster.

Searches for "data cleaning in excel," "cleaning up data in excel," and "data cleanup excel" together add up to about 15,000 monthly searches. The intent is the same: people who already live in Excel want to know how far it goes, and when it stops being enough.

This guide shows the Excel workflow step by step, with the exact functions and clicks, and where a dedicated cleaning tool takes over.

What Excel does well

Excel has three built-in layers for cleaning:

Formulas like TRIM, PROPER, UPPER, LOWER, TEXT, and DATEVALUE
Ribbon tools like Remove Duplicates, Text to Columns, Find & Replace, and Conditional Formatting
Power Query, the transformation engine behind Get & Transform Data, for repeatable cleaning

For small files and one-off fixes, that is often enough. For large or recurring files, each layer has limits you will hit.

Step 1: Trim whitespace

Hidden spaces break lookups and matching. A value like " maria garcia" looks fine but does not equal "maria garcia".

In a helper column: =TRIM(A2) removes leading, trailing, and extra internal spaces. Copy the result, Paste Special as Values over the original, then delete the helper column.

Power Query does it without a helper column: select the column, Transform, Format, Trim.

Rule: always trim first. There is never a reason to keep leading or trailing spaces.

Step 2: Standardize text casing

The same department appears as "Cardiology," "cardiology," and "ONCOLOGY." The data is correct, only the casing is inconsistent.

=PROPER(A2) for title case ("Cardiology")
=UPPER(A2) for codes
=LOWER(A2) for emails

Or use Find & Replace for a handful of known values. Power Query: Transform, Format, Capitalize Each Word.

Step 3: Standardize dates

Real exports mix 02/15/1989, 1985-11-23, 15-Feb-2024, and February 20, 2024 in one column. Convert everything to one format.

In Excel, select the column, Data, Text to Columns, pick Date, choose the source format. Or use =DATEVALUE(A2) and format the result as YYYY-MM-DD. ISO 8601 (YYYY-MM-DD) is the safest target: it sorts correctly as text and is understood by every database and API.

Step 4: Clean numbers and currency

Charge amounts like $ 2,100.00 and 2100.00 USD in one column need to become plain numbers.

Use Find & Replace to remove $, USD, commas, and extra spaces, then format the column as Number with two decimals. Power Query: Transform, Replace Values, then Change Type to Decimal Number.

Step 5: Handle missing values

There is no universal rule for nulls. It depends on the column:

Column	Missing means	Action in Excel
Status	Not set	Fill with "Pending"
Department	Unknown	Fill with "Unknown"
Phone / Email	Not provided	Leave blank
Date of birth	Entry error	Flag for review, don't guess

In Excel, filter the column for blanks (Data, Filter, uncheck Select All, check Blanks), type the fill value, then Ctrl+Enter to fill all visible blanks.

Step 6: Remove duplicates

Exact duplicate rows are usually data-entry errors. Use Data, Remove Duplicates, select all columns for exact row match. Be careful: two patients named "John Smith" are not duplicates, and a patient visiting twice is not a duplicate. Only exact, all-column matches should go.

When Excel is not enough

Excel starts to struggle in three places. Files beyond a few hundred thousand rows slow it down or crash it, and a tool with a real database engine handles millions. If you redo the same six steps every month, Excel makes you redo them by hand, while a dedicated tool lets you save the steps as a recipe and replay them on the next export. And Excel transformations are hard to document, while a recipe-based tool gives you a before-and-after view and a shareable record of what changed.

That is the point where teams switch. A browser-based tool like Mungr runs the same steps, entirely in the browser, with the file never leaving your machine, and lets you save the workflow for next month.

Frequently asked questions

Can Excel clean large CSV files?

For small files, yes. For files beyond a few hundred thousand rows, Excel becomes slow and hard to audit. A dedicated cleaning tool that handles large files and saves the workflow as a reusable recipe is more reliable.

What is the fastest way to clean data in Excel?

Use Power Query (Get & Transform Data) for repeatable steps: trim, split, change case, and remove duplicates without helper columns. For one-off fixes, TRIM, PROPER, and Find & Replace are the fastest.

How do I remove duplicates in Excel?

Select your data, go to Data, Remove Duplicates, and choose the columns to check. For exact row matches, select all columns. Review the count Excel reports before you save.

Do I need Power Query to clean data in Excel?

Not for small, one-off fixes, but Power Query is worth learning if you clean the same structure repeatedly. It records the steps so you can refresh the file next month without redoing the work.

When should I switch from Excel to a dedicated cleaning tool?

When files get large, when you repeat the same cleaning every month, or when you need an audit trail of what changed. If your data is also sensitive, a local tool that never uploads the file is the safer choice.

Bottom line

Excel covers the basics well: trim, fix casing, standardize dates, clean numbers, handle blanks, and remove duplicates. For large or recurring files, save those steps as a reusable recipe in a dedicated tool, so next month's file takes seconds instead of hours.

Try Mungr free — no upload, no code