How to Clean Data in Excel Before You Build a Report

Remove duplicates, extra spaces, text numbers and broken dates in Excel, in the right order, and check the totals so your report starts from clean data.

Founder, Glow Reports. , 3 min read
  • Excel
  • Data Quality
  • Reporting Workflows
A messy spreadsheet passing through a funnel and coming out as a tidy table, with removed duplicate rows set aside
On this page
  1. How do you clean data in Excel?
  2. How do you remove duplicates in Excel?
  3. How do you remove extra spaces and hidden characters?
  4. How do you fix numbers stored as text?
  5. How do you fix dates that won't sort?
  6. How do you check that the data is clean?
  7. Where does Glow Reports fit?
  8. Common questions

A report built on a dirty export still gives a total. It is the wrong one. Duplicate rows count an order twice, a trailing space splits one customer into two, and amounts stored as text drop out of a SUM without a warning. Cleaning data in Excel means fixing those problems in a set order before you build anything: copy the raw file, strip spaces, remove duplicates, convert text to numbers and dates, then check the totals against the source. Each step below uses built-in Excel features, with the Google Sheets equivalent where there is one.

How do you clean data in Excel?

Work on a copy, then fix one kind of problem at a time: extra spaces and hidden characters, duplicates, numbers stored as text, dates, and blanks. Check the row count and one total after each step. The order matters, because a stray space can hide a duplicate and a text number can hide from a total.

Microsoft's own checklist starts the same way. Keep a backup of the original data in a separate workbook, and put the data in a plain table with one kind of value per column, no hidden rows and no blank rows Microsoft Support. Then select the range and press Ctrl+T to make it an Excel table, so formulas fill down on their own.

Before you change anything, write down three numbers from the source tool: the row count, the sum of the main amount column, and the date range. Those are your control totals. Every cleaning step should leave them unchanged or change them for a reason you can name.

How do you remove duplicates in Excel?

Select the range, then DataRemove Duplicates, tick the columns that define a duplicate and select OK. Excel deletes the extra rows for good, so copy the original data to another sheet first Microsoft Support.

The column choice is the decision that matters. Tick every column and Excel only removes rows that are identical everywhere. Tick only the order ID and it keeps the first row for each ID, even if a later row has the refund. For an order export, the ID column is usually right. For a customer list, the email often is.

Look before you delete. Under HomeConditional Formatting, the Duplicate Values rule colors the repeats so you can see what will go. If you want a clean list without touching the source, the UNIQUE function returns the distinct rows of a range in a new place Microsoft Support.

In Google Sheets, the same tool is DataData cleanupRemove duplicates. Google treats values that differ only by letter case or formatting as duplicates Google Docs Editors Help. Power Query does the opposite and compares case-sensitively, so "inv-104" and "INV-104" stay as two rows Microsoft Learn.

How do you remove extra spaces and hidden characters?

Use TRIM for spaces and CLEAN for nonprinting characters, in a helper column. TRIM removes all spaces except single spaces between words, so "Acme Ltd " becomes "Acme Ltd". CLEAN removes the control characters (codes 0 to 31) that some exports leave behind Microsoft Support.

TRIM has one blind spot. It removes only the regular space (character 32), not the nonbreaking space (character 160) that comes with text copied from web pages Microsoft Support. Replace it first:

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

Fill the formula down, copy the helper column, and paste it over the original with Paste SpecialValues. Then delete the helper column. Run this on names, emails and IDs before you remove duplicates, or "[email protected]" and "[email protected] " survive as two customers.

Google Sheets has DataData cleanupTrim whitespace. It also leaves nonbreaking spaces in place, so use the SUBSTITUTE formula there too.

How do you fix numbers stored as text?

Select the cells, open the green error indicator and choose Convert to Number. For a whole column, use in a helper column, or multiply the cells by 1 with Paste SpecialMultiply Microsoft Support.

You can spot text numbers because they sit on the left of the cell by default, and because SUM skips them without an error. That is the dangerous part. A column of 1,000 amounts with 40 stored as text gives a total that looks right and is short.

Three causes cover most cases:

  • A space or hidden character around the number. Run the TRIM formula above first.
  • A minus sign on the right, as some accounting exports write "120-". Microsoft suggests for those cells.
  • A decimal separator from another locale, such as "1.234,50" in a file made in France. NUMBERVALUE lets you name the separators.

After the fix, compare the column's SUM with your control total.

How do you fix dates that won't sort?

They are text, not dates, usually because the export used another day and month order. Select the column, choose DataText to Columns, skip to step 3, pick Date and the order the file uses (DMY or MDY), and select Finish. Excel turns the text into real dates that sort and filter by month.

Microsoft lists dates and times among its ten cleaning steps for the same reason Microsoft Support. A real date is a number underneath, so a quick test is to change the cell format to General. If 3 April 2026 turns into 46115, it is a date. If nothing changes, it is still text.

Watch for the silent case. A UK export read as US dates turns 03/04/2026 into 4 March instead of 3 April, with no error. Check a date where the day is above 12. If it shows as text or an error, the whole column was read in the wrong order.

How do you check that the data is clean?

Compare the cleaned table with the control totals you wrote down at the start: the row count, the main amount and the date range. If a number changed, you should be able to say which step changed it and why. Then count what's left to fix: blanks, errors and outliers.

Quick formulas for the last pass:

  • for empty cells in a required column.
  • for duplicate IDs still there, in an ID column with no blanks.
  • A filter on each column, sorted from smallest to largest, to see negative amounts, zero quantities and dates in the wrong year.

For a fuller column-by-column check, Power Query's profiling tools show the share of valid, empty and error values. That method and the problems behind dirty data are covered in the five biggest analytics problems. If the clean data comes from several monthly files, combine them first with Power Query, then clean once.

Where does Glow Reports fit?

Cleaning the file is still your job, and the steps above work whatever tool comes next. Glow Reports helps with the report you build on top. You upload one or several CSV or Excel files and ask for the report in plain language.

The AI writes the SQL, builds the charts and drafts the text. The SQL sits under each figure, so you can see whether it filtered out test orders, counted each ID once or skipped blank amounts, and edit the query when it didn't. Reports export to PDF and stay in the workspace. The free plan gives 300 credits once, and a report costs 100 credits.

Common questions

What is the fastest way to remove duplicates in Excel?

Select the range, then DataRemove Duplicates and tick the ID column. Copy the data first, because the deleted rows don't come back after you save.

Why doesn't TRIM remove all the spaces?

The leftover is a nonbreaking space, character 160, often from text copied from a web page. Wrap the cell in SUBSTITUTE(A2,CHAR(160)," ") before TRIM.

Why is my SUM lower than expected?

Some cells hold numbers stored as text, which SUM skips. Look for left-aligned numbers or the green triangle, and convert them with Convert to Number or VALUE.

Does Glow Reports clean the data for me?

The AI writes SQL that can filter and count rows the way you ask, and you can read it under each figure. It doesn't change your source file or fix the records in the tool you exported from.

El Mehdi Benane
Founder, Glow Reports

I am a product engineer and the founder of Glow Reports. I spent more than 6 years building products in tech, data and cybersecurity, for large groups, startups and SMEs. In most organizations I saw data treated as a technical topic, when it drives business decisions. I built the reporting tool I was looking for, so the people who run small and mid-sized organizations can read their own numbers without a data team.

Upload your CSV or Excel files to Glow Reports and get a report with the SQL behind each figure.

Keep reading