Excel Data Cleaning and Validation Explained

Data cleaning in Excel is fixing data that is the wrong shape or the wrong type: text bunched into one cell, numbers stored as text, thousands separators that turn a number into a label. Data validation is the opposite direction: restricting what can be typed into a cell in the first place, with a dropdown list, a number range, or a rule of your own. The main tools are Text to Columns, Find and Replace, Flash Fill and, on the Data tab, Data Validation.

This guide shows each one, including a fact that surprises people: validation is a front door, not a lock, and there are ways around it that leave bad data sitting in a cell that looks protected. Every result was produced by running it in Microsoft Excel.

Text to Columns: splitting and converting

Text to Columns (Data, Text to Columns) is built for splitting one column into several at a delimiter such as a comma, but it does something else useful too: running it on a single column, choosing no delimiter, and pressing Finish converts text that looks like a number into a real number, which fixes the classic “numbers left-aligned and SUM shows 0” problem in one step.

Splitting a comma-separated cell:

Converting text numbers, with no delimiter chosen at all:

Find and Replace for cleaning

Ctrl + H (Find and Replace) removes a character everywhere it appears in a selection. A common use is stripping the thousands separator from imported numbers that were saved as text:

Cleaning spaces, non-breaking spaces and case are covered with TRIM, CLEAN, UPPER, LOWER and PROPER in the text functions guide of this series; this guide focuses on tools rather than formulas.

Data Validation: whole numbers, decimals, text length

Select a cell, open Data, Data Validation, and choose what is allowed: Whole number, Decimal, List, Date, Time, Text length, or a Custom formula. Each has an operator (between, greater than, equal to and so on) and one or two limit values. A whole number rule between 1 and 100, tested on two entries:

A text length rule limits how many characters are typed, useful for codes and reference numbers:

List validation: typed lists are case sensitive, range lists are not

A List rule can be typed directly (Yes,No) or point at a range of cells holding the allowed values (=$D$1:$D$2). They behave differently with case: a typed list is case sensitive, so lower-case yes fails against Yes,No, while a range list ignores case entirely:

If your users are inconsistent about capitalisation, point the list at a range instead of typing it.

Ignore Blank

Every validation rule has an Ignore blank checkbox, ticked by default. With it ticked, an empty cell always passes, even though it clearly is not “a whole number between 1 and 100”. Untick it to force an entry:

Validation can be bypassed, and how to catch it

Data Validation is enforced only while a person types into the cell through Excel’s own interface. A value that arrives by pasting, by an import, or by a macro or other automation setting the cell directly is accepted without any check, and the invalid value is simply stored:

This is exactly why Circle Invalid Data (Data, Data Validation, the small dropdown arrow next to the button) exists: it scans every cell that has a rule and draws a red circle around any that currently fails it, regardless of how the bad value got there. Clicking Clear Validation Circles removes them once fixed. Running it on the sheet above, which has one out-of-range cell, adds one circle:

Flagging duplicates instead of deleting them

Data, Remove Duplicates (covered in the tables, sorting and filtering guide) deletes rows outright. Sometimes you want to see duplicates first, and decide row by row. Conditional Formatting with a COUNTIF formula does that without touching the data: a cell is flagged when its value appears more than once in the range. IDs 101, 102, 101 and 103 in column A:

A column of IDs, with 101 repeated

=COUNTIF($A$1:$A$4, A1) > 1 in each row of column B

In Excel this same formula goes into Conditional Formatting, New Rule, Use a formula, so the flagged cells are highlighted directly rather than shown in a helper column.

A caution about deleting blank rows

A common cleaning step is removing rows that are entirely blank: select a column, Go To Special (F5, Special), Blanks, then delete the entire row. This is safe only when the blank cells you selected genuinely mark empty rows. If you select just one column and that column happens to be blank on a row that has data elsewhere, the whole row is deleted along with that data:

Check a row is blank across every column that matters before deleting it, for example by selecting the full width of the data, not just one column.

Common mistakes

Mistake 1: assuming validation cleans existing data

Adding a rule to a range does not check the values already there. Use Circle Invalid Data right after adding a rule to see what needs fixing.

Mistake 2: trusting validation on pasted or imported data

A colleague pasting a whole column over a validated one bypasses every rule silently. Validate again with Circle Invalid Data after any bulk paste or import.

Mistake 3: a typed list that does not match real-world capitalisation

If people will type in different cases, use a range-based list, which is not case sensitive, or add a rule that normalises the text with a formula.

Mistake 4: deleting blank rows from one column only

As shown above, this can remove rows that are blank in that one column but not blank overall. Select every relevant column first.

Cheat sheet

Try it yourself

Work out each answer first, then open the solution.

1. A1 holds the text 5,000 (with a comma). Clean it into a real number and double it in B1.

Show solution

	A	B
1	5000	10000

Replace the comma with nothing (Ctrl + H, or Text to Columns), which leaves a real number, so =A1*2 works.

2. You add a Data Validation rule to a column that already has 50 values in it. Does Excel warn you about the ones that break the rule?

Show solution

No. A validation rule only affects future typing. Use Data, Data Validation, Circle Invalid Data to find the existing values that break it.

3. A1 has a whole-number validation rule between 18 and 60. Test it with 15 and with 30.

Show solution

	A	B
1	30	FALSE
2		TRUE

15 fails (below 18), 30 passes.

4. A colleague says “my dropdown only allows Yes or No, but someone still managed to type YES in capitals.” What are two ways this could have happened?

Show solution

Either the list is a typed list (Yes,No), which is case sensitive, so “YES” in capitals is a different value from the list’s “Yes” and would actually be rejected by the dropdown, or the text was pasted or imported rather than typed through the dropdown, which bypasses the rule entirely and stores whatever was pasted.

Frequently asked questions

Does Excel Data Validation stop bad data already in a cell?

No. A validation rule only checks new typing from that point on. To find values that already break the rule, use Data Validation, Circle Invalid Data.

Can Data Validation be bypassed?

Yes. Pasting, importing, and values set by a macro or other automation are not checked, and the value is stored even if it breaks the rule.

Is a Data Validation list case sensitive?

A typed list, such as Yes,No, is case sensitive. A list that refers to a range of cells is not.

How do I make a dropdown required, so a blank is not allowed?

Open the validation rule and untick Ignore blank. With it ticked, which is the default, an empty cell always passes any rule.

How do I convert text numbers to real numbers in Excel?

Select the column, Data, Text to Columns, choose no delimiter (or Delimited with none checked), and click Finish. It also works with Paste Special, Multiply by 1.

How do I split one column into two or more?

Data, Text to Columns, choose Delimited (for a comma, tab or other character) or Fixed width, then Finish.

How do I highlight duplicate values without deleting them?

Conditional Formatting, New Rule, Use a formula, with a formula such as =COUNTIF($A$1:$A$100, A1) > 1.

Why did deleting blank rows remove data in another column?

Go To Special, Blanks selects blanks in the columns you selected. If you selected only one column, a row that is blank there but has data elsewhere is still deleted whole. Select every relevant column first.