---
title: "How to Clean Data in Excel Step by Step | Ant Assist"
description: "How to clean data in Excel step by step: TRIM, CLEAN, PROPER, Find and Replace, Text to Columns, Flash Fill, Remove Duplicates and a repeatable checklist."
url: "https://ant-assist.com/resources/how-to-clean-data-in-excel/"
---

RESOURCES · ADMIN · CLEAN DATA IN EXCEL

# How to clean data in Excel, step by step

Clean Excel data on a copy and in order: spaces and hidden characters, capitals, columns, dates, blanks, then duplicates last. Check totals before and after.

Tools our VAs use for this

- Updated 23 October 2026
- 10 min read
- By Ant Assist

[Calculate your savings](https://ant-assist.com/calculator/) · [Book a free 15-min call](https://ant-assist.com/book/)

In short

To clean data in Excel, copy the sheet first, then work in this order: remove extra spaces and hidden characters with TRIM and CLEAN, fix capitals with PROPER, split or join columns with Text to Columns or Flash Fill, convert text dates into real dates, deal with blanks, and remove duplicates last with Data > Remove Duplicates. Check the totals before and after. For a monthly job, use Power Query or hand the clean to a [data entry virtual assistant](https://ant-assist.com/services/admin/data-entry/) who does it routinely.

On this page

Admin Virtual Assistant

[Book a free 15-minute discovery call](https://ant-assist.com/book/)

On this page

[Book a call](https://ant-assist.com/book/) · [Calculate your savings](https://ant-assist.com/calculator/)

Part 2

## What data cleaning means

Data cleaning means fixing the faults that break a spreadsheet, such as stray spaces, duplicate rows, dates stored as text, combined names and blank cells.

### Stray spaces, duplicates, mixed formats and blanks

Most messy spreadsheets have the same handful of faults. **Example: a contact list imported from a web form.** The rows below are invented to show them.

The five faults most messy sheets share, before and after. Invented example rows.

Stray spaces, duplicates, mixed formats and blanks

| Problem in the cell | What it should be |
|---|---|
| **" sarah JONES "** | Sarah Jones |
| **"Sarah Jones" on two rows** | One row |
| **"01/02/2025" stored as text, left-aligned** | A real date |
| **"Smith, John" in one column** | First name and last name in two columns |
| **A blank email cell** | Filled from a reliable source, or flagged |

Each one breaks something: a lookup fails on a trailing space, a mail merge sends two letters, a date column will not sort. For the wider picture, our guide to [what data cleansing is](https://ant-assist.com/resources/what-is-data-cleansing/) explains the process and why it matters. If the sheets keep coming back messy, [admin support from a dedicated VA](https://ant-assist.com/services/admin/) can take the routine clean.

Part 3

## Protecting the original data

### Working on a copy

Right-click the sheet tab, choose Move or Copy, tick Create a copy and click OK. Work on the copy. Put cleaning formulas in a helper column beside the original, then use Copy and Paste Special > Values to keep the results.

### Permanent deletion with Remove Duplicates

Microsoft's guidance on [finding and removing duplicates](https://support.microsoft.com/en-us/excel/find-and-remove-duplicates) warns that the duplicate data is permanently deleted, and advises copying the original data to another worksheet first.

Skills and tools

## Built-in Excel cleaning tools

Excel's built-in cleaning tools include Find and Replace for repeated variations, Text to Columns for splitting columns, Flash Fill for patterns and Remove Duplicates.

### Find and Replace

Press Ctrl+H (or Home > Find & Select > Replace), type the text to find and its replacement, then click Replace All. Use it to fix repeated variations in one go, such as "Ltd." to "Ltd".

### Text to Columns

Use it to split one column into several. Insert empty columns to the right first, so nothing is overwritten. Select the column, go to Data > Text to Columns, choose Delimited and select Next, tick the delimiter (such as Comma or Space), select Next, then select Finish.

### Flash Fill

According to Microsoft's guide to [using Flash Fill](https://support.microsoft.com/en-us/excel/using-flash-fill-in-excel), Flash Fill automatically fills your data when it senses a pattern, for example to split first and last names out of one column. Type the result you want for the first row in the next column, then select the cell below and choose Data > Flash Fill (Ctrl+E on Windows). Check the results, as unusual rows can come out wrong.

### Remove Duplicates

Click inside your data, go to Data > Remove Duplicates, and under Columns choose which columns define a duplicate. Tick only Email, for example, to treat two rows with the same email address as the same person. To review duplicates before deleting anything, select the column and use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values instead.

Part 5

## TRIM, CLEAN, PROPER and SUBSTITUTE functions

Each function fixes one fault; chain them in a helper column.

In Excel, TRIM removes extra spaces, CLEAN removes nonprinting characters, PROPER capitalises each word and SUBSTITUTE replaces characters the others miss, such as the non-breaking space.

### Removing extra spaces with TRIM

In a helper column, type =TRIM(A2) and fill it down. Microsoft's page on the [TRIM function](https://support.microsoft.com/en-us/excel/functions/trim-function) says it removes all spaces from text except single spaces between words, so leading, trailing and doubled spaces disappear.

### Removing nonprinting characters with CLEAN

Data copied from other systems often carries hidden characters, such as line breaks. Type =CLEAN(A2) to remove them. Microsoft's page on the [CLEAN function](https://support.microsoft.com/en-us/excel/functions/clean-function) says it removes the first 32 nonprinting characters in the 7-bit ASCII code (values 0 to 31). Combine the two as =TRIM(CLEAN(A2)) to fix both problems in one column.

### What TRIM and CLEAN do not remove

TRIM is designed for the standard space character (ASCII value 32), so it does not remove the non-breaking space that often arrives with text copied from web pages. Replace it first, then trim: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")). CLEAN, by Microsoft's own description, does not remove some additional nonprinting characters (values 127, 129, 141, 143, 144 and 157) on its own; use SUBSTITUTE with the relevant CHAR value for those.

### Fixing capitals with PROPER, UPPER and LOWER

=PROPER(A2) capitalises the first letter of each word, =UPPER(A2) suits postcodes and =LOWER(A2) suits email addresses. PROPER turns "McDonald" into "Mcdonald", so check such names by hand.

Same clean every month?

Take the monthly spreadsheet clean off your list

A dedicated data entry VA follows your cleaning checklist, works on a copy and checks the totals before and after.

[Explore data entry VAs](https://ant-assist.com/services/admin/data-entry/) · [See your savings](https://ant-assist.com/calculator/)

Part 6

## Fixing dates, blanks and mixed formats

### Dates stored as text

A date stored as text usually sits on the left of the cell by default, and =ISTEXT(A2) returns TRUE. To convert a column, select it, go to Data > Text to Columns, click Next to the last screen, under Column data format choose Date and the date order your data uses (day, month, year for UK dates), then Finish. For single cells, =DATEVALUE(A2) returns a date serial number to format as a date. Watch for dates from US-style systems, where day and month are reversed.

### Missing values

To find every blank, select the range and use Home > Find & Select > Go To Special > Blanks. =COUNTBLANK(B2:B500) counts them. Fill gaps only from a reliable source and mark the rest "To check". Never guess a value.

### Addresses and names

Split full names with Flash Fill or Text to Columns, then trim and fix capitals. Put postcodes into capitals with UPPER. To flag likely duplicates that differ only slightly, add a helper column with =COUNTIF($C$2:$C$500,C2)>1 against a cleaned email or postcode column, filter for TRUE and review those rows.

Part 7

## Automating data cleaning with Power Query

Power Query automates data cleaning by recording each step you apply, such as trimming or splitting a column, and replaying the steps when you refresh with a new file.

### Excel versions with Power Query

Microsoft's [Power Query in Excel](https://support.microsoft.com/en-us/excel/about-power-query-in-excel) page says Power Query is available in Excel for Windows, Excel for Mac and Excel for the web, and that on Windows it needs .NET Framework 4.7.2 or later. Availability can vary by version and licence, so check your own copy by looking for Get Data on the Data tab.

### Repeating a monthly clean

Power Query records each cleaning step you apply, such as trimming or splitting a column, and replays them when you refresh. If you get the same export every month, build the steps once, then refresh with the new file.

Part 8

## Data cleaning checklist

[Word checklist of the ten data cleaning steps, with the Excel formula or menu path for each](https://ant-assist.com/assets/downloads/data-cleaning-checklist.docx) (.docx, Word, 38 KB)

### Order of steps

Copy this into the first tab of your workbook:

1. Copy the sheet and work on the copy.
2. Count the rows and note any totals.
3. Remove spaces and hidden characters: =TRIM(CLEAN(A2)), plus SUBSTITUTE for non-breaking spaces.
4. Fix capitals with PROPER, UPPER or LOWER.
5. Split or join columns with Text to Columns or Flash Fill.
6. Convert text dates into real dates.
7. Find blanks with Go To Special, fill from a reliable source or flag them.
8. Highlight duplicates, review them, then use Remove Duplicates.
9. Paste helper columns as values and delete the old columns.
10. Check the result.

### Checking the result

Recount the rows: the difference should equal the duplicates removed. Sort each column both ways to bring odd values to the top and bottom, filter for blanks once more, and test the sheet where it will be used. The same checklist habit works for email, as [inbox zero](https://ant-assist.com/resources/inbox-zero/) shows.

Part 9

## Limits of Excel

### Large data sets, Python and R

Very large sheets slow down and errors get harder to spot. Load them through Power Query, or clean the data in a database or your CRM. Python and R suit people who already code; for most small businesses, Power Query or a person with a checklist is the better route.

Part 10

## Outsourcing data cleaning to a virtual assistant

### Staffing and location

If the clean comes round every week, it may be better handed over. Ant Assist provides a dedicated virtual assistant, never shared, working full time from our own managed office in Cairo, recruited, managed and supervised there and UK-managed. Your VA works through your checklist during UK business hours, 9am to 5pm Monday to Friday, from the £5 per hour introductory rate; the [calculator](https://ant-assist.com/calculator/) shows the cost for your hours. See [whether a VA can help with research and data entry](https://ant-assist.com/faqs/can-a-va-help-with-research-and-data-entry/) for the short answer. If you are costing a hire, [executive assistant salary UK](https://ant-assist.com/resources/executive-assistant-salary-uk/) sets out typical pay. If you decide to hand the clean over, [how to hire a VA for admin](https://ant-assist.com/resources/how-to-hire-a-va-for-admin/) sets out the steps. Regular cleans like this are typical work for a [virtual admin assistant](https://ant-assist.com/services/admin/).

### Security and data access

We work under NDAs and data-processing agreements, share access through password managers and keep every login revocable by you.

Rated 5.0 on Google. · Trusted by our clients

FAQ

## Questions about cleaning spreadsheets

### How do you clear data in Excel?

Select the cells and press Delete to remove the contents but keep the formatting. For more choice, use Home > Clear, which offers Clear All, Clear Formats and Clear Contents.

### How do I remove unwanted data from an Excel cell?

Use Find and Replace (Ctrl+H) to remove a repeated word or symbol by replacing it with nothing. Use SUBSTITUTE in a helper column for more control, or Text to Columns and Flash Fill to keep only the part of the cell you need.

### Is there a shortcut to clean data in Excel?

There is no single clean-up button. The shortcuts that help most on Windows are Ctrl+H for Find and Replace, Ctrl+E for Flash Fill and Ctrl+T to turn your data into a table, which makes filtering and sorting easier.

### Should I clean data in Excel or in my CRM?

Clean it before it goes into your CRM, so the import is right first time. Once it is in, use the CRM's own merge tools, because edits to an export do not flow back. Our guide to lead list building and CRM data covers CRM upkeep.

Didn't find your answer? Browse [more common questions](https://ant-assist.com/help/) in the Help Center.

Get Started

Clean spreadsheets, every week.

A dedicated data entry VA, from £5 per hour. Book a call.

[Book a free 15-min call](https://ant-assist.com/book/) · [Calculate your savings](https://ant-assist.com/calculator/)

Discovery call · Role brief · VA matched · Onboarded into your tools