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: trim spaces, split and standardize columns, then deduplicate on the cleaned matching fields. 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. The order is the same one that applies outside a spreadsheet — the general anatomy of a data-cleaning pass explains why matching fields need normalization before deduplication, and why validation at the source is what stops the file arriving dirty again next month. Trim spaces and standardize the matching fields before the final duplicate pass. Split or combine columns where the destination requires a different layout, then check the result before importing it.

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 duplicates after normalizing the key

Duplicate rows distort counts, sums, and lookups. Before using Excel’s Remove Duplicates command, trim and standardize the key columns using the methods below. Otherwise, different spellings of the same contact can survive as separate rows.

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. If the file lives in a browser tab rather than a desktop workbook, the Google Sheets deduplication methods differ enough — three commands, three outcomes — to be worth reading before you pick one. Empty rows are worth clearing in the same pass, because they break the range Remove Duplicates reads: deleting the blank rows first with Go To Special leaves one solid block for every later step to work on.

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. The dedicated walkthrough of how to remove spaces in Excel with TRIM goes further on this one step — which spaces each method catches, how to make the result permanent, and why trimmed numbers stop adding up until you convert them back.

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. The rules are worth knowing before you pick a target shape, because what the E.164 standard actually requires varies by country — most drop the national trunk zero, Italy keeps it.
• Email lists — lowercase, de-space, and dedupe an address column with the email cleaner.
• A table written the wrong way round — when the field names run down column A instead of across the top, no amount of trimming helps until the grid is rotated; transposing rows into columns in Excel covers the Paste Special, formula, and Power Query routes and when each one is worth it.

Standardize as you reshape: convert text case with UPPER, LOWER, or PROPER so "USA", "usa", and "Usa" stop counting as three values [1]. PROPER is the one to watch on a name column — it flattens "McLeod" to "Mcleod" and capitalizes the letter after an apostrophe, which the walkthrough of proper case in Excel covers row by row.

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]. Set the scope before you type, though — the options that decide what Find and Replace actually changes are what stop a five-cell fix turning into a hundred-cell surprise. 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. To move the finished sheet out of the workbook without Excel's Save As rewriting the delimiter to match your locale, convert the Excel file to CSV in the browser — the output is comma-delimited UTF-8 every time. Going the other way, when the person receiving the file wants a workbook rather than raw text, you can turn the cleaned CSV into an .xlsx instead of double-clicking it into Excel and letting the default import settings reformat the columns again. And when the recipient is not going to edit the file at all — a list to sign off, print, or attach to an email — exporting the cleaned rows straight to a PDF table skips the spreadsheet step entirely, with the header row repeated on every page.

Make the cleanup repeatable with Power Query

Everything above is a one-time pass. When the same export lands every week, Excel has a feature that records the cleanup once and replays it: Power Query.

Power Query imports external data and reshapes it in a dedicated window called the Power Query Editor, where you can "remove a column, change a data type, or filter rows" [4]. What matters for recurring work is what happens behind the scenes: each transformation is recorded and labelled as a step, and "when you refresh a query, each step runs automatically" [4]. Next month's file gets cleaned by pointing the query at it and clicking Refresh, not by repeating five manual passes.

Reach for it when the cleanup recurs, when the raw file has to stay untouched — the query writes its output to a new table and leaves the source alone — or when several exports need combining, since queries can be merged or appended to pull "data from multiple sources to get a unique view" [4]. It runs in Excel for Windows, Excel for Mac, and Excel for the Web [4]. Skip it for a true one-off: the Editor is a second interface to learn, and Text to Columns is faster for a single split.

One ceiling is worth knowing before committing to any Excel route. A worksheet holds a maximum of "1,048,576 rows by 16,384 columns" [5]. A CSV bigger than that will not open in full — the extra rows are truncated without warning, so a dedupe or a row count is wrong before the cleaning starts. For a file that large, read it as text rather than loading it into a grid; plotting the columns in a browser tab is one way to sanity-check a file Excel refuses to show you whole.

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.

If you would rather start from one place, the free data cleaning tool set opens in the browser with nothing to install and one dedicated tool per job. The practical order is the same one this guide follows, minus the Excel damage: trim stray values, split or combine the fields the importer validates, standardize matching fields, remove duplicates, then validate and 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.

Change case with UPPER, LOWER and PROPER

Use a helper column so the source stays intact: =UPPER(A2) produces uppercase, =LOWER(A2) lowercase, and =PROPER(A2) capitalizes the start of each word. Fill down, inspect the results, then copy and Paste Special > Values if the output should replace the source.

PROPER follows text boundaries, not a person's preferred spelling. Check names such as McDonald and de la Cruz, acronyms and company names manually. Do not apply it to email addresses or identifiers. For sentence case, =UPPER(LEFT(A2,1))&LOWER(MID(A2,2,LEN(A2))) capitalizes the first character, but also lowercases acronyms and proper nouns later in the cell.

For recurring files, select the text column in Power Query and use Transform > Format > lowercase, UPPERCASE or Capitalize Each Word. Keep the original column until you have reviewed the change. Casing is a matching policy, not proof that two records belong to one person.

Remove spaces and non-breaking spaces

=TRIM(A2) removes leading and trailing ordinary spaces and reduces repeated internal spaces to one. It preserves the gap between first and last names. To delete every ordinary space instead, use =SUBSTITUTE(A2," ",""); use that only when spaces are invalid, such as in a particular identifier format.

Text copied from a website can contain non-breaking spaces. Use =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) to convert those to ordinary spaces before trimming. CLEAN handles common ASCII control characters, but does not remove every invisible Unicode character. A remaining mismatch needs inspection rather than another blind Replace All.

To remove spaces through the interface, select the intended range, open Find & Replace, enter one space in Find and leave Replace empty. Check the scope before Replace All: removing spaces from a name changes its meaning. After using a helper formula, paste values only when the result is correct. Convert actual numeric measures separately; preserve phone numbers, postcodes and IDs as text.

Use Flash Fill for patterns you can review

Suppose A2 holds Ada Lovelace and you want a first-name column. Type Ada in B2, select the next output cell and choose Data > Flash Fill (Ctrl+E in desktop Excel). If Excel cannot infer the intended pattern, supply a second example and try again. Use a separate empty output column so existing data is not overwritten.

Flash Fill can extract, combine or reformat text from examples. It produces values, not formulas linked to the input: changing a source cell afterwards does not update the filled result. Inspect compound surnames, initials, missing parts and exceptional rows. A plausible-looking preview is not enough for a mixed contact list.

If automatic suggestions do not appear, use the ribbon command and check the desktop Excel setting for automatic Flash Fill. For repeatable imports, prefer explicit formulas or Power Query steps. Google Sheets has a separate feature called Smart Fill; do not assume the same keyboard shortcuts or inference rules.

Transpose rows and columns safely

For a one-time copy, select the source range and Copy, then choose an empty destination area and Paste Special > Transpose. Use Copy rather than Cut, leave enough empty cells, and avoid overlapping the source. If Transpose is unavailable for an Excel table, use a separate output range or deliberately convert the table to a range first, checking which table features you will lose.

For an output that follows source edits, enter =TRANSPOSE(A1:C4) in an empty area. In Excel versions with dynamic arrays, the result spills into adjacent cells; clear obstructions if it reports a spill error. Older Excel versions require selecting the destination range and confirming an array formula with Ctrl+Shift+Enter. Google Sheets also supports TRANSPOSE.

For repeated imports, Power Query provides Transform > Transpose. Review headers after reshaping: the first data row should not accidentally become column names. Check the destination's column limit before turning a very tall table sideways. A CSV import normally expects one record per row, so transpose only when the receiving system requires it.

Merge files before cleaning the combined table

Appending stacks rows from similarly shaped tables; joining matches rows on a key and brings in columns. Decide which operation you need first. A join on a repeated key can multiply rows, so an unexpected row count does not always mean you appended instead. Preserve each input file and a source-file column for checking the result.

For recurring Excel exports, use Power Query's Data > Get Data > From File > From Folder route. Keep the intended inputs in a dedicated folder, inspect the list of files and align headers and data types before combining. Review missing fields after the append; different header names can create separate, partially empty columns. For a key-based join, use Merge Queries and inspect duplicate or blank keys before expanding matches.

In Google Sheets, IMPORTRANGE links a range from another spreadsheet after access is granted. It is a live dependency, not a frozen combined export. VSTACK appends ranges where supported. Command-line users can use csvkit's csvstack for CSV inputs; write output to a new file, never an input file.

Sigmera does not stack whole files or join datasets on a key. Its column combiner concatenates values within a row. After combining files elsewhere, normalize the matching fields, review repeated records and validate counts. Repeated purchases by one customer should remain separate when the table represents transactions.

Fix numbers and dates stored as text

A value that looks right and behaves wrong is usually text. Excel gives you two tells: a date stored as text is left-aligned in its cell instead of right-aligned, and a number stored as text carries a small green triangle in the corner. Until both are converted, sorting puts 10 before 9, SUMIF skips rows, and a date filter returns nothing.

Dates with two-digit years are the quickest fix. Select the range, click the error indicator that appears, and take the conversion option — Excel's own error checking recognizes text-formatted dates and offers to convert them. For everything else, work in a helper column: =DATEVALUE(A2) converts a date stored as text to the serial number Excel recognizes as a date. The result arrives as a bare number, so copy the helper column, Paste Special > Values over the original, and apply a date format. =VALUE(A2) does the equivalent for numbers, though Excel converts most of those automatically and keeps VALUE mainly for compatibility with other spreadsheet programs.

Two cautions before converting a whole column. Check which day-month order Excel assumed: 03/04/2026 is ambiguous, and a locale mismatch turns March into April silently across thousands of rows. And leave identifiers alone — zip codes, account numbers, and phone numbers are text on purpose, and converting them to numbers drops the leading zeros for good.

That last point decides the route for an import-bound file. If the CSV is in transit to a CRM or database, the importer wants the literal string that was exported, and converting it in a worksheet rewrites it to your machine's date format instead. Clean the file as text rather than opening it in a grid — what Excel changes the moment a CSV opens covers which columns get rewritten and how to stop it, and exporting the workbook back to CSV in the browser produces comma-delimited UTF-8 regardless of your locale.

Check the cleaned columns with Data Validation

Every pass above changes values. None of them tells you whether a column now holds only what it should, and a single stray entry (a country code in the postcode column, a date typed as text after the conversion) is enough to fail an import. Data Validation turns that check into a rule Excel can test.

Select the column, go to Data > Data Validation, and pick what the cells are allowed to contain. Microsoft describes the feature as a way to "restrict the type of data or the values that users enter into a cell", and the Allow box offers Whole Number, Decimal, List, Date, Time, Text Length, and Custom for a formula of your own (Microsoft Support). Text Length set to exactly 5 suits a US ZIP column, and a List built from your agreed values suits a status or country column you just standardized.

The rule does not audit the data that is already there. Microsoft is plain about it: "Excel does not automatically notify you that the existing cells contain invalid data." The check you want is Data > Data Tools > Data Validation > Circle Invalid Data, which draws a red circle around every cell in the range that breaks the rule (Microsoft Support). Fix the circled cells, and the circles go as each one becomes valid.

Treat it as a check you rerun, not a lock. The same page notes that validation is designed to stop invalid entries "only when users type data directly in a cell", and that when data is copied or filled the messages do not appear. Pasting the next export over a validated column brings the dirty values straight back with no warning, so run Circle Invalid Data again after every paste. For a file that is a CSV in transit rather than a workbook you maintain, the faster check is the export itself: plot the cleaned CSV as a chart, where a stray category or an out-of-range value shows up as its own bar instead of hiding on row 4,000.