An inherited Excel workbook should be cleaned in a copy, not by immediately overwriting the only source. The safest sequence is to normalize messy text in a helper column, inspect duplicate candidates against a deliberate business key, and move recurring or multi-step work into Power Query. That preserves evidence while giving you a process you can validate and refresh.
Start with a safe cleanup plan
Legacy workbooks often mix imported text, manually typed values, obsolete columns and repeated records. Before changing anything, save an untouched copy with a date in its filename and work from the copy. If the file is shared, record who supplied it, when it was received and which worksheet is the source. Microsoft’s data-cleaning guidance recommends backing up source data and shaping cleaned values in a new column before replacing the original.
As an Amazon Associate I earn from qualifying purchases.
- Keep the source intact: hide or protect the original sheet if necessary, but do not treat hiding as a backup.
- Identify the table: click inside the data and use Home > Format as Table, or press
Ctrl+T. Confirm that the first row contains headers and that each column has one type of information. - Note the intended key: decide which fields identify one real record before checking duplicates. A customer email may be a key in one table; a name alone usually is not.
- Validate before replacing: spot-check unusual values, totals and row counts after every destructive step.
The worksheet techniques below are practical for a one-time repair. Power Query is preferable when the same source will arrive again or when you need a visible sequence of transformations.
Quick wins for a faster PC:
Clear out junk files and repair common Windows errorsFree Scan →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Repair Windows errors before they cause bigger problemsFix Now →Technique 1: Normalize imported text in a helper column
Invisible spaces and control characters can make equal-looking values fail a lookup, sort or duplicate check. A helper column lets you see the proposed result beside the original and undo the change without reconstructing the source.
#1 Best Overall
- The Microsoft Office 365 Bible: The Most Updated and Complete Guide to Excel, Word, PowerPoint, Outlook, OneNote, OneDrive, Teams, Access, and Publisher from Beginners to Advanced
- ABIS BOOK
1. Add a neighboring column
Insert a column beside the dirty field and give it a clear name such as Clean customer name. If the source is an Excel Table, a formula entered in the first row normally fills down the table column automatically. Otherwise, enter the formula in the first data row and fill it down by double-clicking the fill handle or copying it to the last used row.
2. Remove ordinary excess spaces
For a value in A2, start with:
=TRIM(A2)TRIM removes leading and trailing spaces and reduces repeated ordinary spaces between words to a single space. It is useful for names, addresses and imported labels, but it does not understand every kind of whitespace. A value can still look correct while containing a nonbreaking space or another Unicode character.
3. Remove supported nonprinting characters
Combine TRIM with CLEAN when imported text contains control characters:
Recommended Free Tools
=TRIM(CLEAN(A2))Microsoft documents that CLEAN removes only the first 32 nonprinting ASCII characters (codes 0 through 31). It does not remove every additional nonprinting Unicode character, so a formula that returns a visually clean value is not proof that all hidden characters are gone.
4. Target a known Unicode character
If inspection shows a particular character, replace that character explicitly before applying the other functions. For example, a nonbreaking space can be targeted with:
=TRIM(CLEAN(SUBSTITUTE(A2,UNICHAR(160)," ")))Use a targeted replacement only when you know what the character represents. Replacing every unusual symbol blindly can alter legitimate punctuation or language-specific text. If you do not know the character code, compare a suspicious value with a value typed manually, use the formula bar to inspect it, or ask the data owner what the source system inserts.
5. Check the result before replacing the source
Sort or filter the helper column and inspect blanks, accented names, apartment numbers, hyphenated codes and values that contain intentional spacing. Compare a sample of cleaned values with the original and check formulas that depend on the field. Once the output is approved, copy the helper column, use Paste Special > Values over a replacement column, and keep the original column or the untouched workbook until validation is complete. Pasting values removes the formulas, which is appropriate for a one-time handoff but means later source changes will not flow through automatically.
Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Fix the driver behind crashes, sound loss and screen glitches3Repair Windows errors before they cause bigger problemsDo not use this method to “fix” numbers that are actually stored as text without first deciding whether leading zeroes matter. A product code such as 00127 may need to remain text, while a transaction amount should usually be converted deliberately and checked against totals.
Technique 2: Review duplicates before deleting
A duplicate is not simply a row that looks similar. It is a record that repeats the fields your business uses to identify one event, person or item. Two people can share a name, and one customer can legitimately have several addresses or orders. Define the comparison key before opening Excel’s deletion command.
Choose the comparison key
Write down the columns that must match for two rows to represent the same record. For a customer list, email address or a system-generated customer ID may be appropriate. For an order table, the key might be order number plus line number. Comparing only a surname can collapse separate people; comparing every descriptive column can fail to catch a duplicate when one row has a typo in the city field.
Standardize the key first when necessary. Trim email addresses, normalize case if your process treats case as irrelevant, and decide how blanks should be handled. A blank email is not a safe key: many unrelated records may share it.
Preview candidates without changing data
For a worksheet table, select the key column or key columns and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. This highlights repeated values but does not decide which row to keep. You can also use the table filter to display unique values, or sort by the key and inspect adjacent rows. Add a temporary count column such as =COUNTIFS($B:$B,B2) when you need to see how many times each key occurs; adapt the ranges to your table and key columns.
Review the full records behind each highlighted key. Determine whether one row is newer, more complete or already posted to another system. Record the reason for retaining or discarding a row if the workbook supports financial, customer or compliance work.
Remove only after confirming the key
Make another copy before using Data > Remove Duplicates. In the dialog, select only the columns that define a duplicate, or select all columns if an exact full-row match is the intended test. Excel keeps the first occurrence it encounters and removes later matching rows, so sort the table first if a particular version should win. The operation permanently deletes duplicate data from that worksheet; it is not a reversible filter. Microsoft Support advises: “Before removing duplicate values, filter for unique values first to confirm that the results are what you want.”
After removal, compare the remaining row count with your preview, check totals, and preserve the pre-removal file. If you need a review trail, mark rows for deletion in a status column and filter them out rather than deleting immediately.
The Tool Desk
Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Use Power Query when the duplicate rule must be repeatable
In Power Query, select the intended comparison columns, then choose Home > Remove Rows > Remove Duplicates. The query keeps one row for each distinct combination of the selected columns. Selecting a single email column produces a different result from selecting email, customer ID and address together. The step is recorded and can be inspected or changed later, but it still changes the query output, so retain the original source and confirm which row survives.
Technique 3: Use Power Query for repeatable cleanup
Power Query is useful when a legacy workbook is rebuilt from the same export every week or when cleanup requires several ordered operations. Instead of editing the source sheet, you create a query with visible steps that can be reviewed and refreshed.
Bring the data into a query
- Click inside the Excel Table and choose Data > From Table/Range. For a file or database, use the appropriate command in Get Data.
- In the Power Query Editor, confirm headers, data types and the rows being imported. Rename the query so its purpose is clear, such as
Orders_Cleaned. - Make transformations in a logical order: remove clearly empty rows, trim or clean text, set types, handle errors, then apply the business-key duplicate rule.
Every action appears under Applied Steps. Select a step to see its result, rename a step for clarity, or delete a step when testing an alternative. Load the finished query to a new worksheet or to the Data Model rather than overwriting the raw table.
Rank #4
Profile the complete dataset
Turn on View > Column quality, Column distribution and Column profile to see valid, empty and error values. By default, Power Query profiles only the top 1,000 rows. Change the profiling option to the entire dataset before trusting the percentages or distinct-value counts, especially when late rows come from a different month, region or source system. A top-of-file sample can look clean while errors occur near the bottom.
Windows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallCrashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteFilter blanks and manage errors deliberately
Use a column filter to exclude blank, null or unwanted values, but decide whether a blank means “not applicable,” “not yet supplied” or “bad import.” Those cases may require different treatment. For errors, select the column and use Remove Errors, Keep Errors or a replacement operation. Keep or copy error rows when they explain a source problem or are needed for an audit. Removing an error in a query changes the query result; it does not repair the external CSV, database or original workbook that produced it.
Remove columns with refresh in mind
When a source has obsolete fields, explicitly select the named columns and choose Remove Columns. That step continues to remove those names when the query refreshes. Be cautious with Remove Other Columns: it keeps only the columns selected at design time, so a newly added source column can be excluded and go unnoticed after refresh. If new columns must remain visible for review, document the intended behavior and test a refresh with a sample file that includes an added field.
Refresh and validate
Save the query, load the result, and use Data > Refresh All with a fresh source copy. Check row counts, key uniqueness, error counts, totals and a few known records. If the export format changes, an applied step can fail or silently produce an unexpected type; inspect the step list rather than assuming refresh means correctness. Keep the raw input and the cleaned output separately so another person can reproduce the decisions.
Which technique should you use?
| Need | Best starting point | Why | Main caution |
|---|---|---|---|
| One small, one-time text repair | Helper-column formulas | Fast, visible comparison beside the source | TRIM and CLEAN do not remove every Unicode character |
| Investigate possible duplicate records | Worksheet highlighting and filters | Lets you review candidates before deletion | The selected key controls what counts as duplicate |
| Recurring imports or many ordered steps | Power Query | Applied steps can be reviewed and refreshed | Profiling samples 1,000 rows unless changed; refresh needs validation |
Worksheet formulas and commands are convenient for a quick repair, but they usually require a person to repeat the work and can overwrite evidence. Power Query takes more setup and is less convenient for an isolated cell, yet it provides a recorded transformation sequence, filtering, profiling, duplicate handling and error handling. In either approach, the source should remain available and the business key should be explicit.
Free tools Windows power users keep installed
One-click scans. No signup required.
A final validation checklist
- Can you open the untouched source and the cleaned output independently?
- Did you test the text formula on blanks, punctuation, accented characters, nonbreaking spaces and intentional leading zeroes?
- Did you preview duplicate candidates and confirm the exact comparison columns?
- Did you compare row counts, totals and key fields before and after each destructive step?
- For Power Query, did you profile the entire dataset rather than only the default first 1,000 rows?
- Did you decide whether errors should be removed, retained for audit, or sent back to the source owner?
- After a refresh, are newly added source columns handled as intended?
Document the date, source filename, key definition and major transformations in a note on the cleaned workbook. That small record makes the next handoff safer than a workbook whose final values have no visible history.
Frequently Asked Questions
Should I clean the original workbook or make a copy?
Make an untouched backup and perform destructive work in a copy. Helper columns and Power Query let you compare cleaned output with the original before replacing or deleting anything.
Why does TRIM not remove some spaces?
TRIM handles ordinary spaces, while CLEAN handles only nonprinting ASCII characters 0 through 31. Nonbreaking spaces and other Unicode characters may require a targeted SUBSTITUTE, such as UNICHAR(160).
How do I know which columns to select for Remove Duplicates?
Select the fields that define one real record in your process. Email or a system ID may identify a customer, while an order number plus line number may identify an order line. A name alone is often unsafe.
Does Excel’s Remove Duplicates keep the newest row?
It keeps the first matching row it encounters and removes later matches. Sort the data first if a particular version should be retained, and preserve a backup because worksheet deletion is permanent.
Why does Power Query’s profile look different from the table?
Column profiling defaults to the top 1,000 rows. Change the profiling setting to the entire dataset before relying on quality percentages, distinct counts or error counts.
If I remove errors in Power Query, is the source fixed?
No. Removing or replacing errors changes the query output only. Keep error rows when they help explain a source problem, and correct the external file or system separately.
What happens when new columns appear after a Power Query refresh?
Explicitly removing named unwanted columns generally leaves later-added columns available. Remove Other Columns keeps only the originally selected fields, so newly added fields can be excluded after refresh.
The Bottom Line
Clean inherited spreadsheets defensively: preserve the source, normalize text beside the original, verify duplicates against a real business key, and use Power Query when the process must repeat. Validate row counts, totals, errors and refresh behavior before treating the cleaned table as authoritative.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
