How to Clean a Messy Spreadsheet (and Keep It Clean)

Almost every small business has one: the spreadsheet. It might be a customer list, a product inventory, an order log, or a price list that three people have edited over several years. It started tidy. Now it has duplicate customers, five spellings of the same city, dates that sort in the wrong order, and a column called "notes2 (do not delete)."

A messy spreadsheet costs you quietly: duplicate emails to the same customer, reordered stock you already have, an hour fixing numbers before every meeting.

The good news is that cleaning a spreadsheet is mostly a process, not a talent. This guide walks through the steps in the order that works best, using features available in both Excel and Google Sheets.

Step 0: Make a copy before you touch anything

This is the step people skip and regret. Duplicate the file, or at least the sheet, and name it clearly, for example "Customers – original – 13 Oct." Do all your cleaning in a new copy. If something goes wrong, you can always go back.

Step 1: Decide what the spreadsheet is for

Before deleting a single row, write down in one sentence what this file should do. "A list of every active customer with a way to contact them." "Every product we sell, with its current price and stock level."

That sentence becomes your test. Columns that don't serve it can be moved to another sheet or removed. Rows that don't fit it, such as customers who closed their accounts years ago, can be archived rather than mixed in with live data.

Step 2: One row, one thing. One column, one kind of data

Most mess comes from breaking this rule. Look for:

• Merged cells. They look neat and break sorting, filtering, and formulas. Unmerge them and fill in the values.
• Several values in one cell. "Ana Petrović, [email protected], 064..." in one cell should become separate Name, Email, and Phone columns. Excel's Text to Columns and Google Sheets' Split text to columns do this quickly when there's a consistent separator like a comma.
• Totals and subtotals mixed into the data. Move summary rows to a separate sheet or calculate them with formulas below or beside the table.
• Headers in more than one row. Use a single header row with clear, unique names.

Step 3: Trim the invisible mess

Extra spaces are the most common reason two values that look identical don't match. "Belgrade" and "Belgrade " are different to a spreadsheet.

Use the TRIM function in a helper column (=TRIM(A2)), copy the results, and paste them back as values. CLEAN removes some non-printing characters that often come from copying data out of other systems. Google Sheets also has Data > Data cleanup > Trim whitespace.

Step 4: Make text consistent

Now deal with the five spellings of the same thing. Sort or filter each text column and look at the unique values. You'll find things like "Novi Sad," "novi sad," "N. Sad," and "Novi-Sad."

• Use Find and Replace for simple fixes.
• Use UPPER, LOWER, or PROPER to standardise capitalisation where it makes sense.
• For categories (city, product type, status), decide on an official list of values and stick to it. You'll lock this in later with data validation.

Step 5: Fix dates and numbers

Dates are a classic trouble spot, especially when data comes from people or systems using different formats. Is 03/04 the 3rd of April or the 4th of March?

• Check whether your dates are real dates or text that looks like dates. A quick test: real dates usually sort correctly and line up on the right of the cell by default; text dates often sit on the left.
• Pick one format for the whole column and apply it with cell formatting.
• For numbers, remove currency symbols or units typed into the cells ("120 RSD," "5 kg") and put the unit in the column header instead. Then the numbers can actually be added up.
• Watch for numbers that use a comma as the decimal separator mixed with ones that use a dot. Your regional settings decide which one the spreadsheet expects.

Step 6: Find and handle duplicates

Only remove duplicates after the earlier steps, because inconsistent spacing and spelling hide duplicates from the tools.

• In Excel: Data > Remove Duplicates.
• In Google Sheets: Data > Data cleanup > Remove duplicates.

Before removing anything, decide what counts as a duplicate. Same email address? Same name and phone? Two rows with the same customer name might be two different people. For important lists, highlight duplicates first with conditional formatting, review them, and merge information by hand where needed, for example keeping the newer phone number.

Step 7: Deal with blanks

Filter each important column for blank cells. For each one, decide: can you find the missing value, should the row be archived, or is a blank acceptable here? Make it a conscious choice.

Step 8: Keep it clean with a few simple guards

Cleaning is wasted if the file is messy again in a month. A few habits keep it in shape:

• Data validation. Turn category columns into drop-down lists so people choose "Novi Sad" instead of typing their own version. Restrict date columns to valid dates.
• Format as a table. In Excel, Format as Table keeps formatting and formulas consistent as rows are added. In Google Sheets, the table feature and filter views do a similar job.
• Freeze the header row so everyone always sees what each column means.
• A short "Read me" tab. Two or three lines explaining what the file is for, who owns it, and the rules for each column.
• Protect the formula columns so they aren't overwritten by accident.

When to clean it yourself and when to hand it off

You can do all of this yourself. For a few hundred rows and a free afternoon, it's a good investment.

It may be worth getting help when the file has thousands of rows, data merged from several sources, or when mistakes are costly, such as price lists feeding your online shop or customer lists you use for invoicing. Or when the cleanup keeps getting postponed.

Outpost Studio does spreadsheet cleanup for small businesses: we tidy the file, document what we changed, and set up the validation and structure so it stays clean. A basic cleanup (duplicates, formats, merging files, up to 5,000 rows) is a fixed $39, delivered in 24–48 hours; bigger jobs with formulas, pivots or a small dashboard start at $89. Send a short description of your file (no need to share the data itself at first) to [email protected] or via outpoststudio.xyz.

A quick recap

1. Make a copy.
2. Define what the file is for.
3. One row per record, one column per kind of data.
4. Trim spaces and invisible characters.
5. Standardise text.
6. Fix dates and numbers.
7. Remove duplicates carefully.
8. Handle blanks deliberately.
9. Add validation and simple rules so it stays clean.

Work through it in that order and even the most tangled spreadsheet becomes manageable.