How do I remove duplicates in Excel?

Select your data, then go to Data, Remove Duplicates, and tick the columns that must match for a row to count as a duplicate. Make a copy of the sheet first, because the deleted rows can't be got back once you save.

How do I remove duplicates in Google Sheets?

Select the range, then Data, Data clean-up, Remove duplicates. You can also use =UNIQUE(A2:D500) in a new sheet to make a de-duplicated copy without touching the original.

How do I get rid of extra spaces in cells?

Use =TRIM(A2) in a helper column, copy it, and paste it back as values. TRIM removes spaces at the start and end and turns double spaces into single ones. Google Sheets also has Data, Data clean-up, Trim whitespace.

Why won't my dates sort properly?

They have probably come in as text, often because they were written day-first and the spreadsheet expected month-first, or the other way round. Check your file's locale is set to United Kingdom, then convert the column with Text to Columns (choose DMY) in Excel or DATEVALUE in Google Sheets.

Should I clean the original file?

No. Always work on a copy and keep the original untouched, so you can check anything that looks wrong later.

Guide · Spreadsheets · About a 7-minute read

How to clean up a messy spreadsheet

Customer lists exported from three different systems, a mailing list typed by five people, a stock sheet nobody has touched since 2019. Messy data is normal. Here are ten fixes that sort most of it out, in Excel and Google Sheets, in the order we'd do them.

Before you start: make a copy

Duplicate the file (or the sheet tab) and work on the copy. Freeze the header row (View, Freeze) so you can see which column is which as you scroll.

1. Remove blank rows and columns

Add a filter to the header row (Data, Filter, or Ctrl+Shift+L in Excel), filter a column that should always be filled for "(Blanks)", check the rows, and delete them.

2. Trim stray spaces

Extra spaces stop matches and sorting working. In a helper column, use =TRIM(A2), fill it down, copy it, and paste back over the original as values only. Google Sheets: Data, Data clean-up, Trim whitespace.

3. Remove duplicates

Excel: Data, Remove Duplicates, and tick the columns that must match. Google Sheets: Data, Data clean-up, Remove duplicates. Decide first what a duplicate means: the same email address is usually safer than the same name.

4. Fix capitals

=PROPER(A2) turns "JOHN SMITH" or "john smith" into "John Smith". Check the results by eye, because it also turns "McDonald" into "Mcdonald" and "TCH Ltd" into "Tch Ltd". =UPPER() is handy for postcodes.

5. Split one column into several

Full names or addresses in one cell? Excel: Data, Text to Columns, choose the separator (comma or space). Google Sheets: Data, Split text to columns. Insert empty columns to the right first so nothing gets overwritten.

6. Join columns back together

=TEXTJOIN(", ",TRUE,B2:E2) joins address lines into one cell and skips the empty ones. It works in both Excel 2019 onwards and Google Sheets.

7. Turn text back into numbers

Numbers that sit on the left of the cell and won't add up are stored as text. Remove £ signs and commas with Find and Replace (Ctrl+H), then use =VALUE(A2), or in Excel select the column and use Data, Text to Columns, Finish.

8. Fix dates

UK dates are day first. If 03/04/2026 is showing as 4 March, the file's region is wrong. In Google Sheets set File, Settings, Locale to United Kingdom. In Excel use Text to Columns on the date column and pick the DMY format in step three. Then set one display format for the whole column.

9. Standardise the values

"Essex", "essex" and "Essex " are three different things to a spreadsheet. Filter each category column, look at the list of values, and use Find and Replace to make them match. For columns that should only have set answers, add a drop-down (Data validation) so it stays tidy.

10. Check your work

Compare the row count before and after, and make sure you can explain the difference.
Sort each column A to Z and Z to A to spot oddities at the top and bottom.
Use =COUNTIF(A:A,A2)>1 to flag any duplicates left.
Spot-check ten random rows against the original.

Frequently asked questions

How do I remove duplicates in Excel?: Select your data, then go to Data, Remove Duplicates, and tick the columns that must match for a row to count as a duplicate. Make a copy of the sheet first, because the deleted rows can't be got back once you save.
How do I remove duplicates in Google Sheets?: Select the range, then Data, Data clean-up, Remove duplicates. You can also use =UNIQUE(A2:D500) in a new sheet to make a de-duplicated copy without touching the original.
How do I get rid of extra spaces in cells?: Use =TRIM(A2) in a helper column, copy it, and paste it back as values. TRIM removes spaces at the start and end and turns double spaces into single ones. Google Sheets also has Data, Data clean-up, Trim whitespace.
Why won't my dates sort properly?: They have probably come in as text, often because they were written day-first and the spreadsheet expected month-first, or the other way round. Check your file's locale is set to United Kingdom, then convert the column with Text to Columns (choose DMY) in Excel or DATEVALUE in Google Sheets.
Should I clean the original file?: No. Always work on a copy and keep the original untouched, so you can check anything that looks wrong later.

No time to do it yourself?

TCH Works cleans up spreadsheets and turns PDFs into usable Excel files: duplicates removed, columns split and standardised, dates and numbers fixed, and anything we can't read flagged rather than guessed. Up to 500 rows is £15, up to 10,000 rows is £60, and bigger jobs get a quote. It comes back by email as Excel or a Google Sheet.

Get your spreadsheet cleaned up · from £15

Written by TCH Works, October 2026. Menu names are for current Microsoft 365 Excel and Google Sheets and may differ slightly in older versions. See also: How to convert a PDF to Excel.