Cleaning Messy Data Without Programming: A Practical Spreadsheet Guide

Messy data makes even simple reporting difficult.

Customer names may be written in several different ways. Dates may be stored as text. Product codes can contain hidden spaces. Duplicate records inflate totals, while blank cells and inconsistent categories make filters and pivot tables unreliable.

You do not need to learn Python, SQL or another programming language to fix many of these problems.

Microsoft Excel, Google Sheets and Power Query include practical tools for cleaning, standardising and validating data through menus, formulas and repeatable steps. With the right process, you can turn an untidy spreadsheet into a dependable dataset without writing traditional code.

This guide explains how to clean messy data without programming, including duplicates, inconsistent text, incorrect dates, missing values, broken numbers and badly structured tables.

What Is Messy Data?

Messy data is information that is difficult to analyse because it is inconsistent, incomplete, duplicated or incorrectly structured.

Common examples include:

• Names written with different capitalisation
• Extra spaces before or after text
• Dates stored in several formats
• Numbers stored as text
• Missing customer or product details
• Duplicate transactions
• Several values placed in one cell
• Inconsistent categories
• Blank rows inside a table
• Merged cells
• Totals mixed into raw transaction data
• Errors such as #N/A or #VALUE!
• Hidden characters copied from websites
• Different spellings for the same item

A spreadsheet may look acceptable visually while containing serious data-quality problems underneath.

Why Data Cleaning Matters

Poor data can cause:

• Incorrect totals
• Misleading charts
• Broken pivot tables
• Failed lookups
• Duplicate customer records
• Missing transactions
• Inaccurate forecasts
• Reporting delays
• Import failures
• Compliance problems

Data cleaning improves more than appearance. It increases confidence in the decisions made from the spreadsheet.

The Best Order for Cleaning Data

Do not begin by changing values randomly.

Use a controlled process:

1. Preserve the original data.
2. Understand the purpose of each column.
3. Remove structural problems.
4. Standardise text.
5. Correct dates and numbers.
6. handle missing information.
7. identify duplicates.
8. validate the cleaned result.
9. document the changes.
10. automate repeatable steps where possible.

This order reduces the risk of losing information or cleaning the wrong records.

Always Keep an Untouched Original

Before changing anything, create a backup.

A simple workbook structure might include:

• Original Data
• Working Copy
• Cleaned Data
• Issues Log

Do not clean directly inside the only copy.

Keeping the original allows you to:

• Reverse mistakes
• Compare before and after
• Confirm whether values were changed correctly
• Investigate disputed records
• Repeat the process later

For important business data, also keep a separate file backup outside the workbook.

Turn the Range Into a Proper Table

In Excel, click inside the dataset and press:

Ctrl + T

Confirm that My table has headers is selected.

An Excel Table provides:

• Automatic filters
• Clear column headings
• Expanding ranges
• Consistent formulas
• Easier sorting
• Better pivot-table sources
• Structured references

In Google Sheets, apply a filter through:

Data > Create a filter

A proper table makes cleaning much easier than working with a loose collection of cells.

One Row Should Represent One Record

Each row should describe one complete item or transaction.

For example:

Avoid spreading one record across several rows.

Also avoid placing several records in one row.

A clean dataset follows this rule:

• One row per record
• One column per attribute
• One header row
• No decorative totals inside the data

Remove Blank Rows and Columns

Blank rows interrupt filtering, sorting and range detection.

To find them in Excel:

1. Select the data.
2. Open Home > Find & Select > Go To Special.
3. Choose Blanks.
4. Review the selected cells carefully.
5. Delete genuinely empty rows or columns.

Do not automatically delete every row containing a blank cell. A valid record may have one optional field missing.

Delete only rows that are entirely empty or clearly accidental.

Remove Merged Cells

Merged cells are useful for titles but harmful inside raw data.

They can break:

• Sorting
• Filtering
• Pivot tables
• Imports
• Formulas
• Copying and pasting

Unmerge them through:

Home > Merge & Center > Unmerge Cells

Then repeat the intended value in each relevant row.

Keep decorative titles outside the dataset.

Remove Duplicate Records

Duplicates can inflate sales, customer counts and stock totals.

In Excel:

1. Select the table.
2. Open Data > Remove Duplicates.
3. Choose the columns that define a duplicate.
4. Select OK.

Do not always select every column.

A duplicate may be defined by:

• Invoice number
• Customer ID
• Order ID
• Email address
• Date plus transaction number

Before deleting anything, create a duplicate flag.

For example:

=COUNTIF($A:$A,A2)>1

This identifies repeated values in column A.

For duplicates based on several columns:

=COUNTIFS($A:$A,A2,$B:$B,B2,$C:$C,C2)>1

Review duplicates before removing them. Two similar transactions may be legitimate.

Remove Extra Spaces

Copied data often contains spaces that are difficult to see.

Examples include:

"Northside Ltd "

" Amy"

"Product  A"

Use Excel’s TRIM function:

=TRIM(A2)

TRIM removes unnecessary spaces while keeping single spaces between words.

After checking the results:

1. Copy the cleaned column.
2. Use Paste Special > Values.
3. Replace the original where appropriate.

Remove Hidden Non-Printing Characters

Data copied from websites, PDFs and older systems may contain hidden characters.

Use:

=CLEAN(A2)

For both hidden characters and extra spaces:

=TRIM(CLEAN(A2))

This combination solves many mysterious matching and lookup problems.

Some imported data may contain a non-breaking space that TRIM does not remove.

Use:

=TRIM(SUBSTITUTE(A2,CHAR(160)," "))

This replaces non-breaking spaces with normal spaces before trimming.

Standardise Capitalisation

Names and categories may appear as:

• NORTHSIDE LTD
• northside ltd
• Northside ltd

Useful functions include:

Proper Case

=PROPER(A2)

Result:

Northside Ltd

Uppercase

=UPPER(A2)

Useful for:

• Postcodes
• Product codes
• Department abbreviations

Lowercase

=LOWER(A2)

Useful for:

• Email addresses
• System identifiers
• Web addresses

Review PROPER results carefully. It may alter names such as:

• McDonald
• O’Neill
• eBay
• iPhone

Automatic formatting is a starting point, not a substitute for review.

Standardise Categories

The same category may appear in several forms:

• North
• NORTH
• Northern
• North Region
• N

Choose one approved value, such as:

North

You can use Find and Replace for simple changes.

In Excel:

Ctrl + H

Replace each variation with the approved term.

For a more controlled method, create a mapping table.

Then use XLOOKUP:

=XLOOKUP(A2,Mapping!A:A,Mapping!B:B,A2)

This returns the standard value when a match exists and keeps the original when it does not.

A mapping table is safer and more repeatable than dozens of manual replacements.

Use Data Validation to Prevent New Inconsistencies

Cleaning existing data is only half the job.

Prevent new inconsistencies using dropdown lists.

In Excel:

1. Select the category cells.
2. Open Data > Data Validation.
3. Choose List.
4. Select the approved category list.
5. Add an error message where useful.

This prevents users from entering variations such as:

• In progress
• In Progress
• In-Progress
• Ongoing

Instead, they choose one approved status.

Split Combined Information Into Separate Columns

A cell may contain:

Amy Smith - North Region - 0330 123 4567

This is difficult to filter and analyse.

Use Text to Columns in Excel:

1. Select the column.
2. Open Data > Text to Columns.
3. Choose Delimited.
4. Select the correct separator.
5. Preview the result.
6. Choose the destination.
7. Select Finish.

Possible delimiters include:

• Comma
• Tab
• Semicolon
• Space
• Hyphen
• Custom character

Use a copy of the data first so existing columns are not overwritten accidentally.

Use TEXTSPLIT in Modern Excel

Modern versions of Excel include TEXTSPLIT.

For comma-separated content:

=TEXTSPLIT(A2,",")

For a hyphen separator:

=TEXTSPLIT(A2," - ")

This can split one cell into several columns automatically.

Google Sheets provides:

=SPLIT(A2,",")

Combine Data Correctly

Sometimes information is split unnecessarily.

For example:

• First Name
• Surname

To combine them:

=A2&" "&B2

Or:

=TEXTJOIN(" ",TRUE,A2,B2)

TEXTJOIN is useful when some fields may be blank.

Do not delete the original columns until you have checked the combined result.

Extract Parts of Text

Useful text functions include:

Left Characters

=LEFT(A2,3)

Right Characters

=RIGHT(A2,4)

Characters From the Middle

=MID(A2,5,6)

Find the Position of a Character

=FIND("-",A2)

Extract Text Before a Separator

=TEXTBEFORE(A2,"-")

Extract Text After a Separator

=TEXTAFTER(A2,"-")

These functions are helpful for cleaning:

• Product codes
• Invoice references
• Email domains
• Account numbers
• Prefixed identifiers

Standardise Email Addresses

Email addresses should usually be:

• Lowercase
• Free from extra spaces
• Stored in one dedicated column

Use:

=LOWER(TRIM(A2))

You can perform a basic validity check using:

=AND(ISNUMBER(SEARCH("@",A2)),ISNUMBER(SEARCH(".",A2)))

This does not prove the address exists. It only checks for a basic structure.

For a cleaner report, flag invalid-looking emails with conditional formatting.

Standardise Telephone Numbers

Telephone numbers are difficult because formats vary.

Examples include:

• 03300430069
• 0330 043 0069
• +44 330 043 0069
• (0330) 043-0069

First decide the purpose.

For human-readable UK formatting, you may keep spaces.

For system imports, digits-only may be preferred.

To remove spaces:

=SUBSTITUTE(A2," ","")

To remove hyphens as well:

=SUBSTITUTE(SUBSTITUTE(A2," ",""),"-","")

Telephone numbers should usually be stored as text so leading zeros are preserved.

Do not automatically convert UK numbers to numerical values.

Standardise Postcodes

UK postcodes should normally be uppercase.

Use:

=UPPER(TRIM(A2))

If all spaces have been removed, you can insert a space before the final three characters:

=LEFT(A2,LEN(A2)-3)&" "&RIGHT(A2,3)

Apply this only after removing unnecessary spaces and confirming the value is intended to be a UK postcode.

Numbers Stored as Text

Numbers imported from another system may look normal but fail to add correctly.

Signs include:

• A green warning triangle
• Values aligned differently
• SUM ignoring some cells
• Pivot tables using Count instead of Sum

Possible fixes include:

Convert Using the Warning Icon

Select the affected cells and choose Convert to Number.

Multiply by One

Enter 1 in an empty cell, copy it, select the text numbers and use:

Paste Special > Multiply

Use VALUE

=VALUE(A2)

Use NUMBERVALUE

=NUMBERVALUE(A2)

NUMBERVALUE is especially useful when decimal and thousands separators differ between regions.

Remove Currency Symbols Stored as Text

A value such as:

£1,250

may be text rather than a number.

Use:

=VALUE(SUBSTITUTE(SUBSTITUTE(A2,"£",""),",",""))

A better long-term structure is to store:

1250

as the underlying value and apply currency formatting.

This keeps the cell numerical and usable in formulas.

Correct Decimal and Thousands Separators

International data may use:

1,250.50

or:

1.250,50

Use NUMBERVALUE to specify the separators.

For European-style input:

=NUMBERVALUE(A2,",",".")

This tells Excel:

• Comma is the decimal separator
• Full stop is the group separator

Check the source format carefully before conversion.

Clean Percentages Stored as Text

A value such as:

25%

may be text.

Use:

=VALUE(SUBSTITUTE(A2,"%",""))/100

Then apply Percentage formatting.

The underlying value should be:

0.25

not:

25

unless the source system has a different convention.

Fix Dates Stored as Text

Dates are one of the most common data-cleaning problems.

A cell may display:

02/08/2026

but remain text.

Possible fixes include:

DATEVALUE

=DATEVALUE(A2)

Then apply date formatting.

Text to Columns

1. Select the date column.
2. Open Data > Text to Columns.
3. Choose Delimited.
4. Continue without selecting a delimiter.
5. In the final step, choose Date and the correct order.
6. Select Finish.

This is useful for large imported date columns.

Be Careful With UK and US Dates

The date:

03/08/2026

means:

• 3 August 2026 in the UK
• 8 March 2026 in the US

When importing data, confirm the source convention.

Avoid automatic conversion when dates are ambiguous.

Where possible, use the international format:

2026-08-03

This is much clearer for data exchange.

Build Dates From Separate Columns

If Day, Month and Year are stored separately:

=DATE(C2,B2,A2)

Where:

• A2 contains the day
• B2 contains the month
• C2 contains the year

The DATE function creates a genuine Excel date.

Remove Times From Dates

A cell may contain:

02/08/2026 14:35

when only the date is needed.

Use:

=INT(A2)

Then apply a date format.

Excel stores dates as whole numbers and times as fractions of a day.

Extract Dates and Times Separately

For the date:

=INT(A2)

For the time:

=MOD(A2,1)

Apply suitable date and time formatting to the results.

Standardise Boolean Values

A yes-or-no field may contain:

• Yes
• Y
• TRUE
• 1
• Complete
• Done

Choose one approved system.

For example, convert everything to Yes or No.

A formula might be:

=IF(OR(A2="Y",A2="Yes",A2=TRUE,A2=1),"Yes","No")

A mapping table is often cleaner when many variations exist.

Handle Missing Values Deliberately

Blank cells can mean different things:

• Information is unknown
• Information is not applicable
• Data was not collected
• A process is incomplete
• The value is genuinely zero
• The field was forgotten

Do not automatically replace every blank with zero.

Possible approved values include:

• Unknown
• Not Provided
• Not Applicable
• Pending
• Blank, where appropriate

Use a consistent policy for each column.

Find Blank Required Fields

Use conditional formatting or a helper formula.

For example:

=OR(B2="",C2="",D2="")

This can flag rows missing essential information.

For counting blanks:

=COUNTBLANK(B2:D2)

A result greater than zero indicates at least one missing value.

Fill Down Repeated Categories Carefully

Imported reports often show a category only once, with blank cells underneath.

Example:

The blank cells mean “same as above”.

In Excel:

1. Select the region column.
2. Open Find & Select > Go To Special.
3. Choose Blanks.
4. Type =.
5. Press the Up Arrow.
6. Press Ctrl + Enter.
7. Copy the results.
8. Paste as values.

This fills each blank with the value above.

Use this only when the report’s structure clearly supports it.

Replace Errors Carefully

Errors may include:

• #N/A
• #VALUE!
• #DIV/0!
• #REF!

Do not hide them without understanding the cause.

Useful functions include:

=IFERROR(original_formula,"")

Or:

=IFNA(original_formula,"Not Found")

IFERROR catches every error, while IFNA catches only #N/A.

For business reporting, a visible message such as Check Record may be better than a blank result.

Find and Replace Inconsistent Values

Find and Replace is useful for controlled corrections.

Examples include:

• Ltd. to Ltd
• United Kingdom to UK
• In progress to In Progress
• Old department name to new department name

Use:

Ctrl + H

Before replacing:

• Check whether entire-cell matching is required.
• Review the number of matches.
• Replace one example first.
• Keep a backup.
• Avoid replacing part of unrelated words.

For example, replacing IT everywhere could alter words containing those letters.

Use Flash Fill

Excel’s Flash Fill identifies patterns from examples.

Suppose column A contains:

Smith, Amy

In the next column, type:

Amy Smith

Then press:

Ctrl + E

Flash Fill may transform the remaining rows.

It is useful for:

• Reordering names
• Extracting codes
• Reformatting telephone numbers
• Creating email addresses
• Splitting text patterns

Flash Fill creates static values rather than formulas. Check the complete result before relying on it.

Use Remove Duplicates and Unique Lists Differently

Remove Duplicates permanently removes repeated rows from the selected data.

The UNIQUE function creates a separate list of distinct values.

Example:

=UNIQUE(A2:A1000)

Use UNIQUE when you want to:

• Review category variations
• Build dropdown lists
• Count distinct items
• Create mapping tables
• Audit customer names

This is safer during investigation because the original data remains unchanged.

Sort Data to Reveal Problems

Sorting can expose:

• Blank values
• Extreme numbers
• Incorrect dates
• Misspelled categories
• Duplicate identifiers
• Negative values
• Old records

Useful sorts include:

• Oldest to newest
• Smallest to largest
• A to Z
• By cell colour
• By icon
• By status

Always select the complete table before sorting so rows remain intact.

Using an Excel Table reduces the risk of sorting only one column.

Use Filters to Investigate Exceptions

Filters help isolate:

• Blank cells
• Errors
• One particular category
• Values above or below a threshold
• Records within a date range
• Highlighted cells
• Duplicate flags

Combine filters with helper columns such as:

• Duplicate
• Missing Data
• Invalid Date
• Needs Review
• Clean

This creates a manageable cleaning queue.

Use Conditional Formatting to Surface Issues

Useful rules include:

Highlight Blanks

Select required fields and use:

Conditional Formatting > Highlight Cells Rules > Blanks

Highlight Duplicates

Use the built-in Duplicate Values rule.

Highlight Numbers Stored as Text

Use:

=AND(A2<>"",ISTEXT(A2))

Highlight Invalid Dates

Use:

=AND(A2<>"",NOT(ISNUMBER(A2)))

Highlight Unusual Values

Use thresholds or statistical rules to flag outliers.

Conditional formatting should identify records for review, not automatically decide that they are wrong.

Use Pivot Tables to Audit Cleaned Data

Pivot tables can reveal hidden inconsistencies.

For example, a Status field may produce:

• Complete
• completed
• Complete
• COMPLETED

A pivot table lists each variation separately, making the problem obvious.

Useful audits include:

• Count by category
• Count by region
• Count by status
• Minimum and maximum dates
• Total revenue by month
• Number of blank records
• Unexpected product names

Pivot tables are excellent for checking whether standardisation worked.

Check Unique Values in Each Category Column

Before cleaning a category, generate a unique list:

=SORT(UNIQUE(A2:A1000))

This quickly reveals:

• Misspellings
• Extra spaces
• Incorrect capitalisation
• Old category names
• Unexpected blank values

For older Excel versions, copy the column and use Remove Duplicates on the copy.

Detect Near-Duplicates

Exact duplicate tools will not recognise:

• Northside Ltd
• Northside Limited
• North Side Ltd
• Northside Ltd.

These may represent the same company.

You can identify near-duplicates by:

• Sorting names alphabetically
• Using a standardised helper column
• Comparing postcodes or account numbers
• Using fuzzy matching in Power Query
• Reviewing unique values manually

A cleaned helper formula might be:

=UPPER(SUBSTITUTE(SUBSTITUTE(TRIM(A2),".","")," ",""))

This removes spaces and full stops for comparison.

Do not merge records based only on similar names. Confirm using stronger identifiers such as customer ID, address or email.

Clean Data With Power Query

Power Query is one of the best no-code tools for repeatable data cleaning in Excel.

It allows you to import, clean and reshape data through menus.

You can:

• Remove columns
• Filter rows
• Remove duplicates
• Replace values
• Split columns
• Merge tables
• Change data types
• Fill down
• Trim and clean text
• Unpivot columns
• Append files
• Group records
• Refresh the process later

Although Power Query records transformation steps internally, you do not need to write programming code for normal use.

How to Open Data in Power Query

In Excel:

1. Click inside the table.
2. Open Data.
3. Select From Table/Range.
4. Confirm the range.
5. The Power Query Editor opens.

You can then apply cleaning steps from the ribbon.

When finished:

1. Select Close & Load.
2. Choose where the cleaned data should appear.

The original source remains separate.

Trim and Clean Text in Power Query

In Power Query:

1. Select the text columns.
2. Open Transform > Format.
3. Choose Trim.
4. Choose Clean.

You can also apply:

• lowercase
• UPPERCASE
• Capitalize Each Word

These steps can be refreshed automatically when new data arrives.

Replace Values in Power Query

Select a column and choose:

Transform > Replace Values

This is useful for standardising:

• Region names
• Status values
• Product categories
• Department names
• Missing markers

Every replacement is recorded as a repeatable step.

Remove Duplicates in Power Query

Select the columns that define uniqueness.

Then choose:

Home > Remove Rows > Remove Duplicates

Power Query keeps the first matching record unless additional sorting or grouping logic is applied.

Review which record should be retained before removing duplicates.

Change Data Types in Power Query

Every column should have an appropriate data type, such as:

• Text
• Whole Number
• Decimal Number
• Currency
• Date
• Date/Time
• True/False

Incorrect types can cause errors during loading.

Set the type by selecting the icon beside the column heading.

Power Query can also use regional settings when interpreting dates and decimal separators.

Split Columns in Power Query

Select a column, then choose:

Transform > Split Column

Options include splitting by:

• Delimiter
• Number of characters
• Positions
• Lowercase to uppercase
• Digit to non-digit
• Non-digit to digit

This is useful for product codes, full names and combined addresses.

Fill Down in Power Query

To fill blank cells with the previous value:

1. Select the column.
2. Open Transform > Fill > Down.

This is safer and more repeatable than manual copying when processing recurring reports.

Unpivot Wide Data

Some reports store each month in a separate column:

For analysis, a better structure is:

In Power Query:

1. Select the identifying columns.
2. Choose Transform > Unpivot Other Columns.
3. Rename the new Attribute and Value columns.

Unpivoting is extremely useful for preparing data for pivot tables and dashboards.

Combine Several Files Without Programming

Power Query can combine recurring files from a folder.

For example:

• Monthly sales exports
• Weekly ticket reports
• Branch spreadsheets
• Supplier price lists
• Time-sheet files

In Excel:

1. Open Data > Get Data > From File > From Folder.
2. Select the folder.
3. Choose Combine and Transform Data.
4. Apply cleaning steps once.
5. Refresh when new files are added.

This can replace hours of copying and pasting.

Fuzzy Matching in Power Query

Power Query can perform fuzzy matching when merging tables.

This can help match:

• Northside Ltd
• Northside Limited
• North Side Ltd

Use it cautiously.

Fuzzy matching may create incorrect matches when names are similar.

Always review:

• Match scores
• Customer identifiers
• Addresses
• Postcodes
• Email domains

It should support human review rather than replace it.

Create an Issues Log

Record significant cleaning decisions.

An issues log might contain:

This is especially important for:

• Financial data
• Customer records
• Regulated information
• Shared reporting
• Audit-sensitive work

Do Not Invent Missing Information

Cleaning does not mean guessing.

When a value cannot be confirmed:

• Leave it blank where appropriate.
• Mark it Unknown.
• Flag it for review.
• Return it to the data owner.
• Record the uncertainty.

Invented values can be more damaging than missing ones because they appear trustworthy.

Validate the Cleaned Data

After cleaning, perform checks.

Compare Record Counts

How many rows were present before and after?

A lower count may be expected after duplicate removal, but the difference should be understood.

Compare Totals

Check totals such as:

• Revenue
• Quantity
• Cost
• Number of customers
• Number of invoices

Unexpected differences may indicate accidental deletion or conversion problems.

Check Minimum and Maximum Values

Look for:

• Impossible dates
• Negative quantities
• Unrealistically high values
• Zero prices
• Future birth dates

Review Unique Categories

Confirm that old variations have disappeared.

Test Filters and Pivot Tables

Make sure the cleaned data behaves correctly.

Create Control Totals

A control total is a known figure used to confirm that the data remained complete.

Examples include:

• Total revenue before cleaning
• Total transaction count
• Number of unique invoice IDs
• Sum of quantities
• Earliest and latest dates

Record these before cleaning.

After cleaning, explain every intentional difference.

Use Formulas to Compare Original and Cleaned Values

For example:

=A2=B2

This returns TRUE when the values match.

For case-sensitive comparison:

=EXACT(A2,B2)

To identify changed values:

=IF(A2=B2,"Unchanged","Changed")

This is useful when reviewing automated standardisation.

Use Check Columns

Add temporary helper columns such as:

• Valid Date?
• Duplicate?
• Missing Required Data?
• Numeric?
• Approved Category?
• Needs Review?
• Cleaned?

Examples:

=ISNUMBER(C2)

=COUNTIF($A:$A,A2)>1

=COUNTIF(ApprovedStatuses,D2)>0

These columns make the cleaning process transparent and filterable.

Common Data-Cleaning Mistakes

Cleaning the Only Copy

A mistake becomes difficult to reverse.

Deleting Duplicates Without Reviewing Them

Similar records may be genuine.

Replacing Blanks With Zero

Unknown and zero are not the same thing.

Using Find and Replace Too Broadly

Unrelated values may be changed.

Trusting Visual Appearance

A date or number can look correct while being stored as text.

Mixing Raw Data With Totals

Pivot tables and formulas may double count.

Removing Errors Without Investigating Them

The underlying problem remains hidden.

Guessing Missing Values

Incorrect information becomes harder to detect later.

Applying Proper Case to Every Name

Some brand and personal names are altered incorrectly.

Cleaning Manually Every Month

Repeatable tasks should be moved into Power Query or a standard process.

Failing to Validate Totals

Records may be lost without anyone noticing.

A Practical Cleaning Workflow

For a customer export, use this order:

1. Save the original file.
2. Convert the range into a table.
3. Remove empty rows and decorative headings.
4. Standardise column names.
5. Set the correct data types.
6. Trim and clean text.
7. Standardise capitalisation.
8. Map categories to approved values.
9. Convert dates and numbers.
10. Separate combined fields.
11. Flag missing required information.
12. Identify duplicate customer IDs.
13. Review near-duplicate customer names.
14. validate emails and postcodes.
15. compare record counts and totals.
16. load the clean result into a new sheet.
17. document unresolved issues.

Example: Cleaning a Messy Customer List

Suppose the source data contains:

Step 1: Clean Customer Names

=TRIM(CLEAN(A2))

Then standardise known company names using a mapping table.

Step 2: Clean Email Addresses

=LOWER(TRIM(B2))

Step 3: Standardise Regions

Map:

• north
• North Region
• N

to:

North

Step 4: Convert Spend to Numbers

=VALUE(SUBSTITUTE(SUBSTITUTE(D2,"£",""),",",""))

Step 5: Identify Duplicates

Compare the cleaned email addresses:

=COUNTIF($F:$F,F2)>1

The first two records can now be reviewed as potential duplicates.

Excel vs. Power Query: Which Should You Use?

Use Spreadsheet Formulas When:

• The dataset is small.
• Cleaning is a one-off task.
• You want visible helper columns.
• Users need to understand each transformation.
• The logic is relatively simple.

Use Power Query When:

• The same cleaning is repeated.
• Several files need combining.
• The dataset is large.
• Columns need restructuring.
• Steps should be refreshed automatically.
• The original source must remain untouched.

A strong workflow may use both:

• Power Query for repeatable structural cleaning
• Excel formulas for validation and business-specific checks

Data Cleaning Checklist

Before cleaning:

• Keep an untouched original.
• Confirm what each column means.
• Record row counts and control totals.
• Identify required fields.
• Define approved categories.
• Decide what counts as a duplicate.

During cleaning:

• Remove structural clutter.
• Trim and clean text.
• Standardise capitalisation.
• Convert dates and numbers.
• Split combined fields.
• Map inconsistent categories.
• Flag missing values.
• Review duplicates.
• Keep uncertainty visible.
• Document major corrections.

After cleaning:

• Compare record counts.
• Recheck totals.
• Review unique values.
• Test sorting and filtering.
• Build a pivot-table summary.
• Check formulas and lookups.
• Save the cleaned output separately.
• Protect the final version.
• Create a repeatable import process.

Final Thoughts

Cleaning messy data without programming is entirely possible when the data is stored in spreadsheets or common business exports.

Excel and Google Sheets provide formulas for trimming text, correcting capitalisation, splitting values, converting dates and identifying duplicates. Power Query adds a repeatable, menu-driven workflow for larger or regularly updated datasets.

The most important principle is to clean carefully rather than aggressively.

Keep the original, define what correct data should look like and validate the result after every significant change. Do not delete records, fill blanks or merge duplicates unless the decision can be explained.

A clean dataset should be consistent, structured and easy to analyse. It should also preserve enough history that someone else can understand what changed and why.

Need Help Cleaning or Organising Business Data?

Messy spreadsheets can create inaccurate reports, duplicated customer records and hours of unnecessary manual work.

Hamilton Group can help businesses organise data, improve Excel and Microsoft 365 workflows, build repeatable Power Query processes and create clearer reporting systems.

Visit hgmssp.com, call 0330 043 0069, or book a meeting with one of our experts.