Clean up a messy spreadsheet before you analyze it

Set up so you can undo anything

Before you change a single cell, protect the original. Duplicate the sheet and name the copy something like clean. Leave the raw tab exactly as it was exported.

In the clean copy, don't overwrite columns in place at first. Add a new column next to each messy one and put the fixing formula there. You can compare old and new side by side, and when you are happy you can paste the results as values and delete the old column.

A small messy example

Here is a short export from a signup form. It is small, but it has every common problem.

Look at rows 2 and 4. Once you ignore spaces and capitals, it is the same person signing up twice. You will only spot that after cleaning.

Fix spaces and capitals

TRIM removes spaces at the start and end of a cell and turns runs of spaces inside the text into single spaces. Wrap it in a case function to make capitals consistent.

1. 1. Names

   In a new column next to A, enter =PROPER(TRIM(A2)). Row 2 becomes Ana Lopez, and row 5 becomes Chloe Park with one space.

2. 2. Emails

   Next to B, enter =LOWER(TRIM(B2)). Email addresses are compared without regard to case in practice, so lower case gives you one consistent form: ana@example.com.

3. 3. Categories

   Next to D, enter =PROPER(TRIM(D2)). pro, PRO and pro all become Pro, and Basic becomes Basic.

4. 4. Fill down

   Drag each formula to the last row, or double-click the fill handle in the corner of the cell.

PROPER capitalizes the first letter of every word, so check names like McDonald or van Dijk by eye afterward. Fix those few by hand rather than inventing a complicated formula.

Turn text into real numbers and dates

A date stored as text looks fine but sorts like a word and can't be used in date math. A quick test: real numbers and dates usually line up on the right of the cell, text lines up on the left.

• Dates: =DATEVALUE(TRIM(C2)) turns the text into a real date. Format the column as a date to see it as one. For text in the 2026-03-04 form this is reliable. For forms like 03/04/2026, check your spreadsheet's locale first, because one setting reads that as March 4 and another as April 3.
• Numbers: if a column of amounts arrived as text, say in column E, =VALUE(TRIM(E2)) turns text like 1250 into the number 1250 so it can be summed.
• Invisible characters: if TRIM doesn't fix a cell, it may hold a non-printing character from the export. =CLEAN(TRIM(A2)) removes most of them.

Find duplicates before you delete them

Now that emails are consistent, you can count how often each one appears. Say your cleaned emails are in column G, rows 2 to 5. In the next column, enter:

=COUNTIF(G$2:G$5, G2)

The dollar signs keep the range fixed as you fill down. Any row showing 2 or more is a duplicate. In the example, rows 2 and 4 both show 2, because both clean to ana@example.com. Sort or filter by that column to see the pairs together and decide which row to keep, usually the most recent or the most complete.

Only then remove the extra rows. In Excel, use Data > Remove Duplicates and choose the email column. In Google Sheets, use Data > Data cleanup > Remove duplicates. Both keep the first row of each group, so sort first if you want to keep the newest.

Lock in the result

1. 1.Select the new columns, copy, and paste as values only, so they no longer depend on the old columns.
2. 2.Delete the old messy columns and rename the headers.
3. 3.Freeze the header row so it stays visible as you scroll.
4. 4.Write a short note in a cell or a separate tab listing what you changed. The next person, often you, will thank you.

If a formula in your sheet doesn't do what you expect, DuckyHelper can see the sheet on your screen and explain the formula out loud. Having it do the cleanup in the sheet itself, with its own cursor while you keep working, is in early access on Mac.