How to Clean Data in Excel: Every Step

Cleaning data in Excel is the work of turning a messy export into a table you can trust. Unlike a single find-and-replace, it follows an order: remove duplicates, trim spaces, split and standardize columns, then fix formats. This guide covers how to clean data in Excel step by step — and shows a browser route for CSV files that never touches a desktop app.

Start with a copy and a cleaning order

The first move is not a cleaning step at all: duplicate the sheet so you always have the raw data to fall back on. Several cleaning commands overwrite or delete cells with no undo once the file is saved and closed, so a backup tab is cheap insurance.

Then work in a fixed order, because each step is easier on the output of the last. Microsoft's own guide frames data cleaning as roughly ten recurring tasks — spell check, remove duplicates, trim spaces, change case, find and replace, split columns, merge columns, fix numbers stored as text, fix dates, and match tables [1]. You do not need all ten every time, but running them in a sensible sequence saves rework. Remove duplicate rows first so every later formula runs on fewer rows. Trim spaces next so matching and sorting behave. Split and combine columns after that, and fix formats last, once the values are stable.

One habit makes the whole process reversible: add an index column of 1, 2, 3… before you sort or rearrange anything. If a step scrambles the order, you can sort back. This is the same discipline that keeps removing duplicates in Excel from losing the original sequence of records you need to trace.

Remove duplicate rows first

Duplicate rows are the flaw that quietly corrupts every count, sum, and lookup downstream, so clear them before anything else. Excel has a built-in command for this.

Select your range, then choose Data > Remove Duplicates, and under Columns tick the fields that define a duplicate. Match on the column that should be unique — an email address or an ID — not every column at once, because a single stray space in one cell makes two otherwise-identical rows look distinct. Microsoft is blunt about the consequence: after you click OK, "the duplicate data is permanently deleted," which is exactly why the copy from the previous step matters [3].

Remove Duplicates deletes in place and keeps the first occurrence of each match. If you would rather see which rows repeat before deleting, apply conditional formatting to highlight duplicates first, or work on a CSV where you can preview the change. To dedupe a raw export without the round trip through a workbook, you can remove duplicate rows in the browser and see the result before you commit to it.

Trim spaces and clear hidden characters

Stray spaces are the most common reason a lookup returns nothing and a sort groups "Acme" apart from "Acme ". Trimming them is a two-function job.

In a helper column, type =TRIM(A2) and fill it down. TRIM "removes all spaces from text except for single spaces between words," which fixes leading, trailing, and doubled spaces in one pass [2]. Wrap it as =TRIM(CLEAN(A2)) to also strip non-printing characters that arrive with copied web data. Then copy the helper column and paste it back over the original as Values, and delete the helper.

One trap: TRIM leaves the non-breaking space (character 160) untouched, and that character is everywhere in text copied from web pages [2]. If TRIM looks like it did nothing, run Find & Replace, paste the non-breaking space into the Find box (or use CHAR(160) logic), and replace it with a normal space before trimming again. Getting whitespace consistent early is what makes the split-and-combine steps that follow reliable.

Split, combine, and standardize columns

Once values are trimmed, reshape the columns so each holds exactly one kind of data. A column that mixes first and last name, or one that jams city and country together, breaks filtering and imports.

To split a column, select it and use Data > Text to Columns, choosing a delimiter such as a space or comma; to merge columns, use CONCAT or the & operator [1]. For the common jobs, a purpose-built tool skips the formula fiddling entirely:

• Full names — separate one "Full Name" column into first and last with the split-names guide or the browser name splitter.
• Combined fields — stitch first and last name, or address parts, back together the way combining columns in Excel describes, and do it on a CSV with the join-columns tool.
• Phone numbers — force every number into one shape (E.164, for example) with a phone number formatter instead of nesting SUBSTITUTE calls.
• Email lists — lowercase, de-space, and dedupe an address column with the email cleaner.

Standardize as you reshape: convert text case with UPPER, LOWER, or PROPER so "USA", "usa", and "Usa" stop counting as three values [1].

Find, replace, and fix formats

The last pass fixes the values that are technically present but formatted wrong — labels, punctuation, dates, and numbers stored as text. Find & Replace does most of the heavy lifting.

Open Find & Replace with Ctrl+H to strip repeated labels ("Ltd.", stray hyphens, currency symbols) across the whole sheet in one action [1]. For dates that imported as text, and numbers Excel stored as text (flagged by a small green triangle), select the range and use the error menu or Format Cells to convert them to real numbers and dates so they sort and calculate correctly. If two source tables need to line up, VLOOKUP or INDEX/MATCH reconciles them by a shared key [1].

When you find yourself replacing the same tokens on every export, that is a sign the cleanup belongs in a repeatable tool rather than manual Find & Replace each week — the find-and-replace tool runs the same substitutions on a CSV without reopening the workbook. Once the columns are clean, a quick chart of the CSV is a fast way to spot the outliers a format fix missed.

Clean a CSV in the browser with nothing uploaded

If the file you are cleaning is a CSV bound for another system, opening it in Excel is often the step that introduces new errors, not the one that removes them. Excel drops leading zeros from zip codes, reformats dates on open, and turns long IDs into scientific notation.

A CSV file is plain text, so it can be cleaned as text without ever loading into a spreadsheet grid. Sigmera's browser-based cleaning tools run entirely on your device — the file is read inside the tab and never uploaded to a server, which keeps names, emails, and phone numbers off the wire and makes the cleanup GDPR-safe by default. That matters most for exactly the personal data an import-bound CSV tends to hold.

The practical order is the same one this guide follows, minus the Excel damage: drop duplicate rows, trim stray values, split or combine the fields the importer validates, standardize formats, then export. Every tool stays free to preview; only exporting a finished file counts against a plan, which you can check on the pricing page. Use Excel when the data belongs in a workbook and stays there — and the browser route when the file is really a CSV in transit.