Webbo3 Data Analysis Bootcamp · Data Cleaning Module · Lesson 11

Data Cleaning in Excel: Dirty Data, Remove Duplicates, Find and Replace, Text to Columns, Flash Fill, and SUBSTITUTE

A practical, hands on lesson on why messy data quietly breaks analysis, and the exact tools that fix it before a single chart or formula is built on top of it.

Every lesson so far in this bootcamp assumed your data was already clean: properly typed, properly spelled, properly structured. Real data almost never arrives that way. This lesson is about the work that happens before any of the analysis you have learned so far can be trusted, and it is genuinely some of the most important work in the entire field of data analysis, because a brilliant formula built on dirty data still produces a wrong answer.

1. What Dirty Data Is, and Why It Causes Wrong Analysis

Dirty data is any data that is technically present in your sheet but does not actually mean what it looks like it means to Excel, or to a formula reading it. It rarely announces itself. A cell that displays "Lagos" can secretly hold "Lagos " with a trailing space. A cell that displays a phone number can secretly be storing it as a real number, silently dropping the leading zero. A column that looks identical to the eye can contain three different spellings of the same customer's name. None of this looks wrong on the surface, which is exactly what makes it dangerous.

The specific ways dirty data breaks real analysis. Duplicate rows quietly inflate totals and counts, making a business look like it has more customers or more sales than it actually does. Inconsistent spelling or capitalisation splits what should be one category into several, so a SUMIF or a pivot table treats "Lagos," "LAGOS," and "lagos " as three completely separate things rather than one. Numbers stored as text get silently ignored by SUM, AVERAGE, and most other numeric functions, producing totals that are quietly too small with no error message at all. Combined fields, like a full address crammed into one cell, cannot be filtered or sorted meaningfully until they are split apart. Every single one of these problems can sit undetected in a sheet for a long time, because the data still looks fine at a glance, which is exactly why data cleaning has to happen deliberately, as its own dedicated step, rather than being assumed away.

The professional standard worth adopting from day one. Treat cleaning as a distinct phase that happens before analysis, not something you fix reactively when a formula returns a strange result. The five tools in this lesson, used in roughly the order presented, cover the overwhelming majority of real cleaning work you will face in this bootcamp and beyond.

2. Remove Duplicates

Found under Data tab → Data Tools group → Remove Duplicates, this feature scans your selected range and deletes entire rows that are exact duplicates of an earlier row, always keeping the first occurrence and deleting every subsequent identical one. Select your data, click Remove Duplicates, and a dialog box appears listing every column, all checked by default.

Choosing which columns actually define a duplicate. By default, every column must match for a row to count as a duplicate. If you only want rows treated as duplicates based on a single key column, for example a customer ID, uncheck every other column in the dialog and leave only that one ticked. Be aware that even though you are only checking against one column, Excel still removes the entire row, every column included, not just the matching cell.

A genuinely important detail: this compares what you see, not always what is truly stored. Remove Duplicates compares the displayed value in each cell, which means two dates that are stored differently but display identically, or vice versa, can behave unexpectedly. It also only catches exact matches, so "Lagos" and "Lagos " with a trailing space are not recognised as duplicates at all, since they are not exactly identical strings. This is exactly why cleaning steps like TRIM, covered later in this lesson, often need to run before Remove Duplicates, not after, or genuine duplicates slip through untouched.

The single most important habit before using this tool: it permanently deletes data. Unlike most cleaning steps that simply transform a value, Remove Duplicates deletes entire rows outright, and there is no dialog warning you exactly which rows will go before you commit. Always copy your original data to a separate sheet or a separate file before running it, so you have something to compare against, or to recover from, if it removes more or less than you expected. Ctrl + Z immediately after running it will undo the deletion if you catch a mistake right away, but do not rely on that as your only safety net.

3. Find and Replace for Bulk Corrections

You already met the mechanics of Find and Replace, Ctrl + H, Match Case, Match Entire Cell Contents, and wildcards, in an earlier lesson in this bootcamp. In a data cleaning context specifically, this tool earns a second look because it is the fastest way to fix a known, consistent spelling or formatting error across an entire dataset in one pass, without writing a single formula.

The cleaning-specific workflow worth building as a habit. Before clicking Replace All on a real dataset, always click Find All first and review exactly which cells it identifies, since Replace All gives you no second chance to undo a mistake you did not anticipate. A genuinely common real scenario: a region column contains "Lagos State," "Lagos state," and "LAGOS STATE" mixed together, all of which should simply read "Lagos." Turning off Match Case and replacing "lagos state" with "Lagos" in one pass catches every capitalisation variant in a single Replace All, since case-insensitive matching is the default behaviour unless you specifically turn Match Case on.

4. Text to Columns: Splitting Combined Data

Found under Data tab → Data Tools group → Text to Columns, this wizard splits the contents of a single column into several columns, based on either a delimiter or a fixed character width. Select your column, launch the wizard, and choose between two modes in step one.

Delimited mode splits text wherever a specific character appears, for example a comma, a space, or a tab. If column A holds full names like "Chidinma Okafor," choosing Space as the delimiter splits it cleanly into a first name column and a last name column. Step two of the wizard shows a live preview of exactly how your data will split before you commit, which is worth checking carefully, since an unexpected extra space or an inconsistent delimiter can produce a messier split than you intended.

Fixed width mode splits text at specific character positions instead, useful when every entry follows the exact same length with no consistent delimiter character to split on, for example a product code where the first five characters always represent one category regardless of what symbols, if any, separate them.

The warning that catches almost everyone the first time. Text to Columns writes its split results starting from the original column's position by default, which means it will silently overwrite whatever data sits in the columns immediately to the right unless you specifically change the Destination field in step three to point somewhere empty. Before running this wizard on real data, always insert enough blank columns to the right first, or set the destination to a different, empty area of the sheet entirely. There is also a clever, lesser known use for this same wizard: selecting Delimited, then deliberately unchecking every single delimiter option in step two so nothing actually gets split, and choosing General as the column format in step three. Run that way, the wizard does not split anything at all, but it forces Excel to re-evaluate text-formatted numbers as genuine numbers, which is a fast fix for an entire column of numbers that arrived as text and are being silently ignored by SUM and other numeric functions.

5. Flash Fill for Pattern Based Cleaning

Flash Fill, covered in depth in the Text Functions lesson earlier in this bootcamp, deserves a specific mention here as a cleaning tool because it solves exactly the kind of inconsistent, example driven transformation that Text to Columns cannot handle cleanly. Where Text to Columns needs a single, consistent delimiter or a fixed width across every row, Flash Fill learns from one or two examples you type manually and then applies that same learned pattern down the rest of the column, even when the underlying data is not perfectly uniform.

The cleaning specific case where Flash Fill beats Text to Columns. If a column of names mixes single first names with some that include a middle initial, Text to Columns splitting on a space would produce a different number of resulting columns for each row, which breaks the split entirely. Flash Fill instead lets you manually type the correctly extracted first name for two or three of the trickier rows first, so it can infer the actual rule you want, including how to handle the inconsistent cases, before pressing Ctrl + E to apply that learned pattern down the rest of the column. As covered in the earlier lesson, always spot check Flash Fill's output afterward, since it produces plain values with no underlying formula you can audit later if something quietly went wrong on an edge case it misread.

6. TRIM, CLEAN, and SUBSTITUTE in Practice

TRIM and CLEAN were introduced in the Text Functions lesson: TRIM removes ordinary leading, trailing, and excess internal spaces, while CLEAN strips non-printable control characters like line breaks and tabs. In a data cleaning workflow specifically, the standard, reliable combination for genuinely messy imported text is =TRIM(CLEAN(A2)), run once as a helper column, then pasted back over the original data as values to make the cleaning permanent.

SUBSTITUTE syntax: =SUBSTITUTE(text, old_text, new_text, [instance_num])

SUBSTITUTE rounds out this cleaning toolkit by replacing one specific piece of text with another, found by matching content rather than by counting character position. =SUBSTITUTE(A2,"-",".") replaces every hyphen in A2 with a period. By default it replaces every matching occurrence, but supplying the optional fourth argument targets just one specific instance, for example =SUBSTITUTE(A2,"2025","2026",2) changes only the second occurrence of "2025" in that cell, leaving any earlier ones untouched.

The genuine reason SUBSTITUTE belongs in a cleaning toolkit specifically: it can remove the non-breaking space TRIM cannot touch. As covered in the Text Functions lesson, a non-breaking space copied from a web page has character code 160, which sits completely outside the range that both TRIM and CLEAN are built to catch. =SUBSTITUTE(A2,CHAR(160)," ") converts every non-breaking space into a genuine, ordinary space, which TRIM can then clean up normally. This is exactly why the safest, most complete cleaning formula for web-copied text is the full chain =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))), working from the inside out: SUBSTITUTE fixes the invisible non-breaking spaces first, CLEAN strips genuine control characters next, and TRIM tidies up the remaining ordinary spacing last.

A genuine trap worth knowing before you trust SUBSTITUTE's result in a formula. SUBSTITUTE always returns its result as text, even when the result reads like a plain number. =SUBSTITUTE("1,234",",","") returns the text "1234," not the number 1234, which means a SUM formula referencing that cell will silently ignore it, exactly the numbers-stored-as-text problem covered earlier in this lesson. If you need the cleaned result to behave as a real number afterward, wrap the whole formula in VALUE, for example =VALUE(SUBSTITUTE(A2,",","")), or use the double negative trick, =--SUBSTITUTE(A2,",",""), which forces Excel to coerce the text result back into a genuine number.

Using AI to Move Faster in Excel

Data cleaning is repetitive, rule based work, which makes it one of the clearest places where AI inside Excel saves genuine time, provided you keep verifying its output rather than trusting it blindly on data you have not personally inspected.

1. Ask Copilot to scan for dirty data before you start cleaning manually.
Rather than scrolling through hundreds of rows trying to spot inconsistencies by eye, select your range and ask something like "List every unique spelling variation of region names in this column" or "Check this column for hidden non-breaking spaces or extra whitespace." This gives you the full picture of what actually needs fixing before you commit to a Find and Replace, exactly the kind of preview step this lesson recommends doing manually with Find All.

2. Describe the cleaning rule, let Copilot choose between Flash Fill logic and a formula.
If you are unsure whether a transformation is consistent enough for Text to Columns, or needs Flash Fill's example-based pattern matching, or a SUBSTITUTE-based formula, describe the actual task, for example "Standardise these phone numbers to the format 0803-123-4567 regardless of how they're currently formatted." Copilot will typically choose the right tool for the specific inconsistency in your data, rather than you guessing upfront.

3. Use AI to confirm exactly which rows Remove Duplicates would affect, before you run it.
Since Remove Duplicates permanently deletes data with no preview of exactly what will go, ask Copilot first: "How many duplicate rows exist in this range based on the customer ID column, and which rows would be removed?" Reading that answer before opening the Remove Duplicates dialog gives you a second, independent check against an irreversible action.

4. Never trust a cleaned column just because it now looks tidy.
This is the single most important habit in the entire lesson. Dirty data is dangerous specifically because it often still looks fine after a flawed cleaning pass, the same way it looked fine before. Whether you cleaned a column manually or asked Copilot to do it, run a genuine check afterward, comparing row counts before and after Remove Duplicates, spot checking a handful of Flash Fill results, or confirming a LEN comparison before and after a TRIM and CLEAN pass actually shows characters were removed, rather than assuming a tidy-looking result means a correct one.

A habit worth carrying forward from this lesson into every dataset you touch from now on: before building a single formula, chart, or pivot table on top of new data, spend five minutes specifically hunting for the problems covered here, duplicates, inconsistent spelling, numbers stored as text, combined fields that should be split apart. That five minutes spent deliberately looking for dirty data, whether by hand or by asking Copilot to scan for it, is consistently the highest leverage five minutes in any real data analysis task, because every formula built afterward inherits whatever you missed.

Next lesson: sorting, filtering, and conditional formatting for working with larger ranges of data.