Almost every dataset you encounter in the real world is messy. Data gets exported from systems with inconsistent formats, manually entered by people who don't follow the same conventions, and combined from multiple sources with different structures. Before you can analyse it, you need to clean it.

Here's how to tackle the most common data quality problems in Excel, step by step.

1. Remove duplicate rows

The first thing to check is whether any rows are exact duplicates — identical values in every column. These often appear when data is exported multiple times and concatenated, or when a system records the same event twice.

How to do it: Select your data range → Data tab → Remove Duplicates. Excel will ask which columns to check for duplicates. Usually you want all columns selected. It reports how many duplicates were found and removed.

Important: Run this before any other cleaning. Other steps may create duplicates or make genuine duplicates harder to find.

2. Find and handle missing values

Missing values in Excel appear as blank cells. To find them, use Ctrl+G → Special → Blanks. This selects all empty cells in the current selection, which you can then count, highlight, or fill.

What to do with missing values depends on why they're missing and how many there are:

  • Fill with a default: For missing categorical values that should have a known default (e.g., a country field where blank means "Unknown"), select all blanks (Ctrl+G → Special → Blanks), type the default value, and press Ctrl+Enter to fill all selected cells at once.
  • Fill with the column mean or median: Use =AVERAGE() or =MEDIAN() calculated on the non-blank cells, then paste as values.
  • Delete the row: If a required field is blank and the row can't be used without it, deleting is the right call.
  • Leave as-is: If missingness is informative (e.g., a field that's blank only for certain categories), document it and keep it.

3. Standardise text formatting

Inconsistent capitalisation and spacing are silent data quality killers. "United Kingdom", "united kingdom", and "UNITED KINGDOM" are the same place, but Excel will treat them as three different categories in a pivot table.

Excel has three functions for capitalisation:

  • =UPPER(A1) — converts to ALL CAPS
  • =LOWER(A1) — converts to all lower case
  • =PROPER(A1) — Title Case (capitalises first letter of each word)

For whitespace, =TRIM(A1) removes leading, trailing, and extra internal spaces. =CLEAN(A1) removes non-printable characters that sometimes sneak in from system exports.

After applying these, paste the results as values (Paste Special → Values) over the original column to replace the formulas with clean text.

4. Fix number-stored-as-text errors

A common import problem: a column of numbers where every value is stored as text. Excel shows a green triangle in the top-left corner of each cell and SUM() returns 0. This happens when numbers have leading apostrophes, are imported with text encoding, or come from a system that exports everything as strings.

Quick fix: Type the number 1 in an empty cell, copy it, select your "number" column, Paste Special → Multiply. Excel converts the text-stored numbers to actual numbers by multiplying by 1.

Alternative: Select the column, click the warning diamond → "Convert to Number".

5. Standardise date formats

Dates are among the messiest data types. The same date might appear as "01/07/2025", "July 1, 2025", "2025-07-01", or "01-Jul-25" in the same column.

The safest approach: convert all dates to a single ISO format (YYYY-MM-DD). Use =TEXT(DATEVALUE(A1), "YYYY-MM-DD") as a starting point, but you'll likely need to handle multiple formats separately since Excel's date parsing isn't fully consistent.

Once dates are in a consistent format, store them as actual Excel date serial numbers (not text) so you can use date functions, filter by date range, and sort correctly.

6. Identify and flag outliers

Use conditional formatting to visually identify extreme values in numerical columns. Select the column → Home → Conditional Formatting → Highlight Cells Rules → Greater Than / Less Than to flag values beyond a threshold.

For a more systematic approach, calculate the IQR (Q3 − Q1) for each numerical column and flag values below Q1 − 1.5×IQR or above Q3 + 1.5×IQR as potential outliers:

=OR(A1 < QUARTILE($A:$A, 1) - 1.5 * (QUARTILE($A:$A, 3) - QUARTILE($A:$A, 1)),
    A1 > QUARTILE($A:$A, 3) + 1.5 * (QUARTILE($A:$A, 3) - QUARTILE($A:$A, 1)))

This returns TRUE for outliers. Don't delete outliers automatically — review each one to decide whether it's a data error or a genuine extreme value.

7. Validate against expected constraints

Finally, check that values are within expected ranges. Ages should be positive and less than 130. Prices shouldn't be negative. Percentage values should be between 0 and 100. Order quantities should be whole numbers.

Use filtering and sorting to quickly spot impossible values. =COUNTIF(A:A, "<0") counts negative values that shouldn't exist. =SUMPRODUCT((A2:A1000=INT(A2:A1000))*1) checks that all values in a range are integers.

Save a clean copy

Once cleaning is complete, always save a separate clean copy of the dataset before doing any analysis. If a cleaning step introduced an error, you want to be able to go back to the cleaned version rather than starting from the raw data again.

Document what you did: a simple notes sheet in the workbook listing each cleaning step, how many rows were affected, and the decision made. This makes the process reproducible and auditable.

If cleaning at this level feels tedious, tools like Amridata automate most of these checks: duplicate detection, missing value counts, outlier identification, and type inconsistency flagging all happen automatically when you upload a file.