Cleaning Up a Messy Sales Sheet

A sales sheet that has been edited by several people for a few months almost always ends up with the same three problems: duplicate rows from re-pasted data, extra spaces that make matching formulas fail, and values that look the same but are not. None of these are hard to fix once you know which built-in tool handles each one.

Problem 1: Duplicate rows

Someone re-pastes last week's export into this week's sheet, or two people add the same order from two different tabs. Now you have the same sale counted twice.

Microsoft's own documentation on finding and removing duplicates describes a two-step process. First, highlight potential duplicates so you can review them before anything is deleted:

1. Select the cells you want to check.
2. Go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
3. Choose a formatting style and select OK.

This step does not delete anything. It just colors the rows so you can see what the sheet considers a duplicate. Review these highlighted rows before moving to step two, since not every repeated value is actually an error (two different customers can order the exact same item and quantity on the exact same day in a large sheet).

Once you've confirmed which rows are true duplicates:

1. Copy the original data to another sheet first. Microsoft's documentation is explicit that Remove Duplicates permanently deletes data, so keep a backup copy before running it.
2. Select your range, then go to Data > Remove Duplicates.
3. In the dialog, check or uncheck which columns must match for a row to count as a duplicate. If your "January" column has price data you want to keep separate from the duplicate check, uncheck it so Remove Duplicates ignores that column when deciding what counts as a repeat.
4. Select OK.

A small worked example

Order ID	Customer	Amount	Month
1001	Alpha Co	500	Jan
1002	Beta Co	300	Jan
1001	Alpha Co	500	Jan
1003	Gamma Co	450	Feb

Row 3 is an exact repeat of row 1 because the Order ID, Customer, Amount, and Month all match. Running Remove Duplicates on the full row leaves three unique rows: 1001, 1002, and 1003. If you had only checked the Order ID column (unchecking the others), the result would be the same here, but in a sheet where Order ID gets reused by mistake, checking only one column could delete a row that is actually a different sale. This is why reviewing the highlighted duplicates first, before deleting, matters more than skipping straight to Remove Duplicates.

Problem 2: Extra spaces breaking your formulas

A VLOOKUP that should find "Apple" but returns an error often has a hidden space: " Apple" or "Apple " instead of "Apple". Microsoft's VLOOKUP documentation lists this as a common source of #N/A errors and specifically recommends using the CLEAN or TRIM functions to remove trailing spaces before matching.

In practice this means:

1. Add a helper column next to the messy one: =TRIM(A2).
2. Copy that column, then paste it back over the original as values only (not formulas), so you're not left with a permanent helper column.
3. Re-run your lookup formulas.

Google Sheets documents the same type of problem for its own VLOOKUP function and recommends the built-in Data > Data cleanup > Trim whitespace menu option as a one-click alternative to writing a TRIM formula for every cell.

Problem 3: Numbers stored as text

A column of order amounts that look like numbers but are actually left-aligned (a sign they're stored as text) will not total correctly with SUM, and lookup formulas that expect a number may return the wrong row. Google's documentation on VLOOKUP specifically warns against storing numbers or dates as text in the column you're searching, because it can cause unpredictable lookup results. The fix in Google Sheets is to select the column, then use Format > Number and choose Number or your intended type, which converts text-formatted digits back into real numbers.

Putting it together: a cleanup order that works

When a sheet has all three problems at once, fix them in this order, because each step makes the next one more reliable:

1. Trim whitespace first. Duplicate detection and lookups both rely on exact text matches, so trailing spaces can cause both to behave incorrectly before you've even started.
2. Fix number-as-text columns next. This affects totals and any formula that filters or sorts by value.
3. Highlight and review duplicates, then remove the confirmed ones, keeping a backup copy of the original range.

Doing it in this order avoids a common trap: removing duplicates before trimming whitespace can miss true duplicates that only differ by a stray space, leaving both copies in the sheet.

Key takeaways

• Always highlight duplicates with conditional formatting and review them before using Remove Duplicates, since it permanently deletes data.
• Keep a backup copy of the original range before removing anything.
• Trailing or leading spaces are a frequent, invisible cause of broken lookups; use TRIM (Excel) or Data cleanup > Trim whitespace (Google Sheets).
• Numbers stored as text break sums and lookups; reformat the column as Number before running formulas on it.
• Clean whitespace and number formatting before running duplicate checks, since both affect what counts as "the same" value.