How to Clean Data in Excel: A Practical Checklist

How-to · Updated July 2026 · ~8 min read

Every analyst and accountant knows the feeling: you export a report from your ERP or bank portal, paste it into Excel, and half of it is unusable. Trailing spaces break your lookups, dates arrive as text, names are duplicated, and one column holds three fields at once. This guide is a practical, repeatable checklist for how to clean data in Excel — the exact steps, functions, and menu paths that turn a messy dump into a table you can actually trust.

Work through it top to bottom the first time. After that, most of it becomes muscle memory, and the last sections show you how to automate the whole thing so you never do it twice.

Before you clean data in Excel: copy the raw data first

Cleaning is destructive. Before you change a single cell, duplicate the sheet (right-click the tab, Move or Copy, tick Create a copy) or paste the raw export onto a tab named RAW. If a step goes wrong three edits later, you want the original sitting untouched next to your working copy.

While you are there, convert your working range into a proper Excel Table with Ctrl+T. Tables give you structured references, auto-expanding formulas, and a stable name that Power Query can pick up later.

Step 1: Strip invisible junk with TRIM and CLEAN

The most common reason a VLOOKUP or XLOOKUP "should match but doesn't" is invisible whitespace. To trim spaces and remove non-printable characters, use two functions together in a helper column:

1. In a new column next to the messy one, enter =TRIM(CLEAN(A2)).
2. Fill it down the full height of your data.
3. TRIM collapses leading, trailing, and repeated internal spaces to a single space; CLEAN strips non-printable control characters (line breaks, tabs) that sneak in from PDFs and web exports.

One catch worth knowing: TRIM does not remove the non-breaking space (character 160) that ERP and HTML exports love to insert. If cells still look padded, wrap it: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")).

When the helper column looks right, select it, press Ctrl+C, then Paste Special → Values (Ctrl+Alt+V, then V) back over the original, and delete the helper. You now have genuinely clean text instead of a formula that depends on the messy source.

Step 2: Remove duplicate rows

Duplicates inflate totals and double-count transactions. To remove duplicates safely:

1. Click any cell inside your table.
2. Go to Data → Remove Duplicates (in the Data Tools group).
3. In the dialog, tick only the columns that define a true duplicate. For an invoice register that might be Invoice Number alone; for a customer list it might be Name plus Email.
4. Click OK. Excel keeps the first occurrence and reports how many it removed.

Because Remove Duplicates deletes rows permanently, preview first when the stakes are high. Select the column, then Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values to see what will disappear before you commit. If you only need a count of uniques rather than a cleaned list, =COUNTIF or a quick PivotTable leaves your data intact.

Step 3: Split combined fields with Text to Columns

When one column holds "Smith, John" or "2024-03-14 | Wire | USD", split it apart with Text to Columns:

1. Select the single column (make sure there are empty columns to its right, or the split will overwrite neighbours).
2. Go to Data → Text to Columns.
3. Choose Delimited for separators like commas, tabs, or pipes; choose Fixed width when fields line up by position.
4. Pick or type the delimiter, preview the result in the wizard, and click Finish.

Text to Columns has a hidden second job: fixing numbers and dates stored as text. Select the problem column, run the wizard, click straight through to step 3, and set the Column data format to Date (with the correct order, e.g. DMY) or General for numbers. Excel re-parses every value on the way out.

Step 4: Let Flash Fill handle the patterns

Flash Fill is the fastest way to reshape text when a formula feels like overkill. It watches what you type and finishes the pattern.

1. In the column beside your data, type the result you want for the first row — for example, turning john.smith@acme.com into John Smith.
2. Start typing the second row. When Excel shows a grey preview of the rest, press Enter to accept.
3. If it does not offer a preview, press Ctrl+E to trigger Flash Fill manually.

It is brilliant for extracting first names, reformatting phone numbers, building INV-0001 style codes, or reordering "Last, First" into "First Last". Just remember Flash Fill produces static values, not live formulas — if the source changes, run it again.

Step 5: Standardize dates, numbers, and text

Consistency is what makes data sortable, filterable, and safe to sum. To standardize data across a column:

• Dates: Get everything into real date serial numbers, then apply one display format with Ctrl+1 → Number → Date. For stubborn text dates, =DATEVALUE(A2) or the Text to Columns trick above both work.
• Numbers stored as text: Look for the little green triangle and left-aligned figures. Select the range, click the warning icon, and choose Convert to Number — or multiply by 1 with Paste Special, or wrap in =VALUE(A2).
• Text casing: Standardize with =PROPER(A2) for names, =UPPER() for codes, or =LOWER() for emails.
• Categories: Fix "USD", "usd", and "US Dollar" the same way every time. A small lookup table plus XLOOKUP maps every variant to one approved value.

Step 6: Make it repeatable with Power Query

If this is a monthly bank feed or a recurring ERP export, do not clean it by hand twice. Power Query records every step and replays it on next month's file with one click.

1. Select your table and go to Data → Get & Transform Data → From Table/Range.
2. In the Power Query Editor, do your cleaning with the ribbon buttons: Transform → Format → Trim and Clean, Home → Remove Rows → Remove Duplicates, Split Column by delimiter, and Data Type to lock each column to Text, Date, or Decimal.
3. Every action is logged in the Applied Steps panel on the right — you can rename, reorder, or delete any step.
4. Click Close & Load to drop the cleaned table back into Excel.

Next period, replace the source file and hit Data → Refresh All. Every step runs again automatically — no re-doing the checklist.

Clean it in one request with HISAB 360

Once you know the manual steps, you also know how many clicks they cost. HISAB 360 is an AI assistant that lives inside Excel: you describe the outcome in plain English and it writes the Power Query M — or a VBA macro — that does it, then leaves that behind so the sheet cleans itself next month. It is a shortcut to the exact same methods above, not a black box that replaces knowing them.

Select your table, open the HISAB panel, and type the whole clean-up as one instruction:

Trim the spaces from every column, standardise the dates to ISO
(yyyy-mm-dd), remove duplicate rows, and split Name into First and Last.

HISAB turns that sentence into a single Power Query query, with each transform as its own named Applied Step — the same panel you would have filled in by hand:

Source
 → Trimmed Text
 → Changed Type (Date) → Formatted Date (yyyy-mm-dd)
 → Removed Duplicates
 → Split Column by Delimiter → First Name / Last Name

Because it builds a real query rather than a one-off edit, the recipe stays attached to the workbook. Next month you drop in the new export, hit Data → Refresh All, and every step runs again — exactly as if you had clicked through the editor yourself. Prefer a button to a refresh? Add "and give me a macro I can run" and HISAB writes the equivalent VBA instead.

You still own the result. Open the Applied Steps (or the generated code), rename a step, change the delimiter, or delete one you did not want — it is ordinary Power Query and ordinary VBA underneath. HISAB does the typing; the method is the one this checklist teaches, which is why it is worth knowing both.

The quick clean-data checklist

Keep this next to your keyboard:

1. Copy the raw data to a RAW tab; work on a Table (Ctrl+T).
2. =TRIM(CLEAN()) to strip spaces and control characters (add SUBSTITUTE for CHAR(160)).
3. Remove Duplicates on the columns that define a true row.
4. Text to Columns to split combined fields and re-parse text dates/numbers.
5. Flash Fill (Ctrl+E) for pattern-based reshaping.
6. Standardize dates, numbers, casing, and category labels.
7. Rebuild it in Power Query — by hand or by asking HISAB 360 — so next month is one refresh.

Frequently asked questions

Try HISAB 360 on your own workbook

HISAB 360 is an AI assistant inside Excel for accountants and finance teams — it writes macros, Power Query and formulas from plain English, and connects two-way to QuickBooks, Xero, Zoho Books, Odoo, FreshBooks and Sage. The 15-day trial is the full product, no card required.