Excel Data Cleaning: Duplicates, Flash Fill, Text to Columns
SheetHub11 min
A blank cell in column A. Extra spaces in column B. Numbers stored as text in column C. Duplicates in column D. And that's just the first four columns.
Excel Data Cleaning is what turns a chaotic spreadsheet into something you can actually work with. This is not a disaster — this is a typical Tuesday for anyone who works with data. Excel has six tools that turn messy data into ready-for-analysis information: Remove Duplicates, Flash Fill, Text to Columns, TRIM, Find & Replace, and Data Validation.
Availability: All techniques in this guide work in Excel 2019, Excel 2021, and Excel 365 for Windows and Mac. Flash Fill and dynamic arrays are available in Excel 2016 and later. Power Query (Get & Transform) is available in Excel 365 and Excel 2021. Some features may have reduced functionality in older versions or Excel Online.
The fastest way to clean duplicate rows is Excel's built-in Remove Duplicates tool.
How to use it: Select your data range, go to the Data tab, and click Remove Duplicates in the Data Tools group.
A dialog box appears showing all columns in your selection. Excel checks entire rows — it only removes a row if all selected columns match another row.
Key decisions:
Flash Fill is Excel's pattern-recognition feature. Type one example of what you want, press Ctrl + E, and Excel fills the remaining cells by detecting the pattern. (Check the Excel Keyboard Shortcuts Guide for more shortcuts like this one.)
When to use it:
Type "John" in the first row of the "First Name" column. Press Ctrl + E. Excel recognizes the pattern — extract the word before the space — and fills the remaining cells.
Example — Format phone numbers:
Type
This formula recalculates automatically when source data changes. Use Flash Fill for one-time cleanups and formulas for ongoing data workflows.
Text to Columns splits a single column of data into multiple columns based on a delimiter or fixed width.
How to use it: Select the column, go to Data > Text to Columns (in the Data Tools group).
Delimited — Choose the character that separates your data: comma, tab, semicolon, space, or custom. The preview shows how the data splits. This is the go-to choice for CSV data, tab-separated exports, and log files.
Fixed width — Set breaklines where columns should split. Use this when data aligns in columns without a consistent delimiter, such as legacy system reports.
Pro tip — Fix text dates: Dates imported as text strings do not work in formulas or Pivot Tables. Use Text to Columns to convert them:
Three text functions that handle the most common text quality issues — extra spaces, non-printable characters, and inconsistent capitalization.
TRIM — Removes leading spaces, trailing spaces, and double spaces between words. Imported data almost always has extra spaces:
CLEAN — Removes non-printable characters (like line breaks and tab characters) that often appear in data copied from web pages or PDFs:
PROPER — Capitalizes the first letter of each word. Use after importing data in all-caps or all-lowercase:
Combine for thorough text cleaning:
This removes all non-printable characters and extra spaces in one step. Create a new column with the cleaned data, then copy and paste as values to replace the originals.
Limitation: These functions create new data in adjacent columns — they do not modify the original cells in place. You need to copy and paste as values to replace the original data.
Workaround — Use Power Query for in-place transformation: If running text cleaning on the same dataset repeatedly, load the data into Power Query (Data > From Table/Range) and apply TRIM and CLEAN as transformation steps. Power Query applies the transformations during the load process, and refreshing the query reapplies them without manual formulas.
Everyone knows Ctrl + H for basic find and replace — covered in the Excel Keyboard Shortcuts Guide along with others. But Find & Replace has powerful options that most users miss.
Wildcards: Use
This creates a new column with the replacement applied only where the search term exists, and leaves other values unchanged. Review the results, then paste as values over the originals.
The best cleaning strategy is preventing bad data from entering your sheet in the first place. Data Validation gates what users can enter into cells.
How to use it: Select cells, go to Data > Data Validation (or Data > Data Tools > Data Validation in older versions).
Useful validation rules:
Input messages show users what to enter when they select the cell. Error alerts block invalid entries with a custom message. For shared sheets, this eliminates most data entry errors.
The Data Validation Dropdown Guide covers advanced validation patterns including cascading dropdowns and custom formula validators.
Limitation: Data Validation only works on manually entered data. Paste operations bypass validation rules entirely.
Workaround — Use Sheet Protection: Combine Data Validation with sheet protection. Protect the sheet but allow editing in validated cells. Users can only paste into unprotected areas, forcing data entry through validated cells. For imported data, always run the cleaning steps above after pasting.
Putting it all together — the six-step process from messy import to analysis-ready data:
For step 6, see the Dynamic Array Functions Guide for using UNIQUE to identify unique values after cleaning, and the Pivot Table Beginner Guide for creating Pivot Tables from cleaned data.
Here is the cleaning workflow from section 8. Describe your specific data problem to any AI assistant:
The AI should return a tailored cleaning sequence using the functions above. Test the workflow on a small sample (10 rows) before applying to the full dataset.
Does Remove Duplicates delete data permanently?
It deletes duplicate rows from the current sheet. Once saved, the rows cannot be recovered. Always save a backup copy before running Remove Duplicates.
Why does Flash Fill sometimes not work?
Flash Fill needs a clear pattern. If the first example is ambiguous — or if adjacent rows have inconsistent patterns — Flash Fill may not detect the intended transformation. Type a second or third example to reinforce the pattern, then press Ctrl + E again.
What is the difference between TRIM and CLEAN?
TRIM removes extra space characters (leading, trailing, and double spaces between words). CLEAN removes non-printable characters like line breaks and tab characters that cannot be seen but interfere with formulas.
Can Data Validation prevent all bad data?
No. Data Validation only works on manual entry. Pasting data bypasses validation. Use sheet protection to restrict pasting, or run the cleaning workflow after every data import.
How do I know if my data is clean enough for analysis?
Run a quick quality check: use COUNTIF to check for blanks in required columns, check that all columns have the expected data type, and verify that SUM and AVERAGE return reasonable values. The cleaning workflow in section 8 covers the full verification process.
Remove Duplicates
- Select all columns — removes only full duplicate rows (identical in every field). Use this for exact row deduplication.
- Select specific columns — removes rows where the selected columns match, even if other columns differ. Use this when you only care about uniqueness in key fields (like email addresses or order IDs).
- Select your range.
- Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
- Review which rows are highlighted before running Remove Duplicates.
Flash Fill (Ctrl+E)
- Split a full name column into first and last names
- Extract domain names from email addresses
- Format phone numbers consistently
- Combine first and last names from separate columns
| Full Name | First Name |
|---|---|
| John Smith | John |
| Jane Doe | (type "Jane" → Ctrl+E fills the rest) |
| Bob Johnson | Bob |
(555) 123-4567 next to 5551234567. Flash Fill detects the formatting pattern and applies it to all cells in the column.
Limitation: Flash Fill does not update automatically. If source data changes, Flash Fill results do not recalculate — they need to be reapplied.
Workaround — Use formulas for dynamic updates: Instead of Flash Fill for tasks that need to update regularly, use text functions like LEFT, RIGHT, MID, and FIND. For example, extracting first names:
=LEFT(A2, FIND(" ", A2) - 1)Text to Columns
- Select the date column.
- Data > Text to Columns > Delimited > Next > Next.
- Under Column data format, select Date and choose the format (MDY, DMY, YMD).
- Click Finish.
TRIM, CLEAN, and PROPER
=TRIM(A2)=CLEAN(A2)=PROPER(A2)=TRIM(CLEAN(A2))Find & Replace (Advanced)
? to match any single character and * to match any sequence of characters. Replace *Co.* with Company to standardize inconsistent abbreviations in one pass.
Replace within formulas: Click Options > Look in: Formulas to find and replace inside formula text. Use this to update range references across many formulas at once — for example, changing '2025'!B:B to '2026'!B:B across an entire workbook.
Format-based replace: Click Options > Format to find cells with specific formatting and replace both the format and the content. Replace all red-highlighted error cells with a standardized orange warning format in one operation.
Limitation: Find & Replace changes all matches at once with no per-match review option. It can unintentionally modify data that matches the search pattern but should not be replaced.
Workaround — Use a helper column with SEARCH: For targeted replacement that allows per-row review:
=IF(ISNUMBER(SEARCH("Co.", A2)), SUBSTITUTE(A2, "Co.", "Company"), A2)Data Validation (Prevention)
| Rule Type | Prevents | Example |
|---|---|---|
| Whole number | Text in numeric fields | Between 1 and 100 |
| List | Invalid entries | Dropdown from a named range |
| Date | Wrong date format | Date after January 1, 2026 |
| Custom formula | Complex validation | =ISNUMBER(A2) ensures numeric input |
Cleaning Workflow
1. Import correctly — Use Power Query or Import Wizard to set data types at import
2. Remove duplicates — Run Remove Duplicates on a backup copy of your data
3. Clean text — Apply TRIM, CLEAN, Flash Fill across text columns
4. Fix data types — Text to Columns for dates and number conversion
5. Validate — Set up Data Validation to prevent new errors
6. Analyze — Feed cleaned data into Pivot Tables or dynamic array formulasAI Prompt to Build Your Cleaning Workflow
I have messy data in Excel with the following issues:
- [LIST ISSUES: duplicates / extra spaces / text dates / etc.]
- Here is a sample row: [PASTE A FEW ROWS]
- My data has [NUMBER] rows and [NUMBER] columns
What is the fastest cleaning workflow for this specific data? Give me the exact Excel formulas and step-by-step instructions.Related Functions
- Dynamic Array Functions Guide — Using UNIQUE to identify unique values after cleaning
- COUNTIF and COUNTIFS Guide — Counting cleaned data to verify deduplication
- Pivot Table Beginner Guide — Creating reports from analysis-ready data
- Open UTF-8 CSV in Excel Guide — Fixing encoding issues during CSV import
FAQ
Topics
Topics in this article
Explore related topics and continue reading similar content.
Share this article
Discussion
Preparing the comments area...