Webbo3 Data Analytics · Cohort 2 · Excel Track · Week 2, Live 1
Removing Duplicates and Handling Blank Cells
Last week you found the problems. This week you fix them, following the Cleaning Process tab, starting with the two that change every total: sales counted twice, and sales with something missing.
By the end of this class you can
- Find and remove duplicate rows safely with Data > Remove Duplicates
- Explain why ticking too few columns in Remove Duplicates deletes real sales
- Filter one column for blanks without disturbing the rest of the data
- Use ISBLANK and ISNUMBER to ask a cell what it holds
- Decide between delete, leave, fill and flag for missing data, and say why
Week 1 was the inspection. From today, you change the data. That makes this the most dangerous week of the track, because a cleaning mistake does not look like a mistake: the sheet still looks tidy, it just holds the wrong rows. So every fix today comes with a way to check it.
Open the Cleaning Process tab of the workbook. Today covers its Steps 2 to 5. Keep it beside you: each section below says which step it is.
Step 2: Keep a Copy
Step 2 of the Cleaning Process
All the cleaning happens on Sales Data. Raw Data is never touched again: if anything goes wrong, the original is one click away. Professional analysts never clean the only copy of the data.
1. Duplicate Rows (Step 3)
Definition
A duplicate is a record that appears in the data more than once. Every duplicate inflates every total it is part of: a sale counted twice is revenue counted twice.
Real-Life Scenario
A class register passed round twice by mistake. Some students sign it again on the second round. If the school counts signatures, attendance looks higher than it was. The fix is not to tear out the second page; it is to cross out each name that appears twice, keeping one.
Types of Duplicates
- Exact duplicates: every column identical. The same record entered or pasted twice. Always safe to remove one.
- Key duplicates: the same ID with different details, such as one Transaction_ID with two different quantities. Something went wrong; investigate before removing either.
- Look-alikes: two records that share some details but are genuinely different, such as two customers buying the same product on the same day. These are not duplicates, and removing them is a serious error.
Type 1: Exact Duplicates, and How to Remove Them
In Week 1 you found 20 Transaction_IDs that appear twice. Here is one of them again, TP-0210: every column the same, so it is an exact duplicate.
Step 3 of the Cleaning Process
This is the exact message the Cleaning Process tab tells you to expect: 20 removed, 1,050 remain. Read the message every time: it is your check. If the numbers had been different, you would press Ctrl + Z immediately and find out why.
Type 2 and 3: Why Every Column Must Stay Ticked
The column checkboxes tell Excel which columns must match for two rows to count as duplicates. Tick fewer columns and Excel calls more rows "the same". Watch what happens if someone reasons "same day, same product, same quantity must be the same sale" and ticks only Date, Product_ID and Quantity:
Excel removed the 20 real duplicates and 27 genuine sales. Two different customers, served by different staff, bought the same product in the same quantity on the same day. With only three columns compared they look identical. They are look-alikes, not duplicates. The sheet still looks neat; it has just lost 27 real sales, and every total in your report is now wrong.
Keep every column ticked unless you have a specific, written reason not to. And always read the message: "20 duplicate values found" is a check that passed. A number you did not expect is a check that failed.
On a copy of Raw Data, run Remove Duplicates with only Product_ID ticked. Read the message, then press Ctrl + Z.
Check your answer
Excel keeps one row per product and deletes everything else: only 20 rows would be left. That is the clearest picture of why the checkboxes matter.
Picture the register passed round twice: you cross out a name only if the whole signature line matches, not just the first name. Plenty of different students are called Chidi.
2. Filtering for Blank Cells (Step 4)
Definition
A blank cell is a cell with nothing in it. A filter hides the rows that do not match what you choose, without deleting or moving anything. Filtering for (Blanks) shows only the rows where one column is empty, so you can see them, count them, and decide what to do.
Real-Life Scenario
A teacher holds up the class list and says "everyone who has not submitted, stand up". Nobody leaves the room and nobody changes seats. For a moment she can see exactly who is missing, count them, and decide what to do. Then everyone sits down again. That is a filter.
Why Blanks Happen
- Not recorded: the till, the form or the person skipped it. (TechPlug's blank Quantity.)
- Not known: nobody knows the value. (TechPlug's blank Customer_Type: the sale went through without anyone recording who the customer was.)
- Not applicable: the question does not apply. (A "Delivery date" for a sale collected in the shop.)
- Lost on the way: an export or a copy and paste dropped it.
Filter One Column Without Disturbing the Rest
Step 4 of the Cleaning Process
25 rows, exactly as the Cleaning Process tab says. Three things to notice. The row numbers skip and turn blue: Excel is telling you rows are hidden. The filter arrow on Quantity changes to a funnel: that column is filtered. And the status bar counts the matching records. Nothing was deleted; clear the filter and all 1,050 sales come back.
The tab says leave them blank for now. A blank Quantity is honest: nobody knows how many were sold, and typing a guess would be inventing sales. In Week 3 you will see how a formula deals with them properly.
Bring every row back
Always clear one filter before reading another column's totals, or you will be totalling a filtered subset without realising. If the row numbers are blue, something is filtered.
Picture the class standing up: everyone is still in the room, in their own seat. The filter only changes who you can see.
3. Filling Blanks With a Label (Step 5)
Customer_Type is blank on 15 sales. Here the tab chooses differently: type Unknown. The customer type is not a number anyone would add up; it is a label for grouping. A sale with an empty label quietly disappears from a "by customer type" summary. A sale labelled Unknown stays counted, and says honestly that nobody recorded it.
Step 5 of the Cleaning Process
Check it: filter Customer_Type for Unknown and the status bar must say 15 of 1,050. While a filter is on, Ctrl + D fills only the rows you can see, so no visible customer type was overwritten.
4. ISBLANK and ISNUMBER
Definition
These are your first two functions. A function is a built-in instruction you type into a cell: an equals sign, the function's name, and in brackets the cell it should look at. Both of these ask a yes-or-no question about a cell and answer TRUE or FALSE:
- ISBLANK(cell) asks: is this cell empty?
- ISNUMBER(cell) asks: does this cell hold a real number (which includes a date)?
Week 3 explains formulas properly. Today you only need the pattern: type it in the first row, press Enter, then copy it down to every row.
Real-Life Scenario
A security guard at a gate with a checklist: "Has this person got a pass? Yes or no." He does not decide what happens next; he just answers the question for every person, and someone else acts on the answers. IS functions are that guard.
Function 1: ISBLANK
We put the check in column M, the first empty column. The first blank Quantity after Step 3 is in row 22, so we show rows 20 to 24.
Type it once, fill it down
Only row 22 says TRUE. Double-clicking the fill handle copied the formula to every row down to 1,051, and each copy looks at its own row: M3 checks F3, M4 checks F4. Filter column M for TRUE and the status bar says 25 of 1,050, the same count as filtering Quantity for blanks.
Function 2: ISNUMBER
The opposite answers: FALSE where Quantity is blank, TRUE everywhere else. On this sheet ISNUMBER and ISBLANK tell the same story, because every quantity that is there is a proper number. Where they disagree is where ISNUMBER earns its place: a number stored as text is not blank, but it is not a number either.
ISBLANK would say FALSE for all five, because none is empty. Only ISNUMBER catches row 4: the 7 that looks like a number but is stored as text. Use ISBLANK to find what is missing; use ISNUMBER to find what is the wrong type.
| Cell holds | ISBLANK | ISNUMBER |
|---|---|---|
| A number, like 380000 | FALSE | TRUE |
| A date | FALSE | TRUE |
| Text, like Kano | FALSE | FALSE |
| A number stored as text | FALSE | FALSE |
| Nothing | TRUE | FALSE |
Delete columns M and N when you have finished: the Sales Data tab should end at column L again.
A cell holding a single space looks empty but is not: ISBLANK says FALSE. If ISBLANK and a filter on (Blanks) disagree about a column, look for cells with a space in them.
Picture the guard with two questions on his clipboard: "Is the pass missing?" and "Is it a real pass?" A blank card and a fake card both fail the second question; only the blank fails the first.
5. Delete, Leave, Fill or Flag?
Definition
When data is missing or wrong, an analyst makes a deliberate choice, writes it down, and can defend it. "Delete everything with a blank" is almost never the right one.
Real-Life Scenario
A shop finds receipts with the total smudged. Throw them away and the day's takings look too low. Guess the totals and you might be wrong. Or put them in a pile marked "check these" and count them separately. Same receipts, different choices, different end-of-day reports.
The Four Choices
| Choice | What you do | Use it when | The risk |
|---|---|---|---|
| Delete | Remove the row | The row is not real data: a duplicate, a test entry, an empty line | Deleting real records makes every total too low |
| Leave | Keep the cell blank | The value is genuinely unknown and nothing should be invented | Someone may misread the blank as zero |
| Fill | Type an honest label in the blank | A text column used for grouping, where a blank would drop the row out of a summary | Filling numbers with guesses is inventing data |
| Flag | Keep the row and add a column saying what is wrong | You want the problem rows easy to find and count later | None, as long as the flag is used |
The Decisions on TechPlug, and Why
| Problem | Decision | Why |
|---|---|---|
| 20 duplicate rows | Delete (Step 3) | They are not real sales; keeping them counts revenue twice |
| Quantity blank in 25 sales | Leave (Step 4) | Nobody knows how many were sold. A guessed number would be an invented sale |
| Customer_Type blank in 15 sales | Fill with Unknown (Step 5) | A label for grouping; Unknown keeps the sale counted and is honest |
The fourth choice is used on Verdant Foods: your pod adds a Quantity_Flag column with =IF(ISBLANK(G2),"Missing","OK"). IF is taught in Week 3; read it as a sentence for now: if the cell is blank, write Missing, otherwise write OK.
Overall Example: The Routine So Far
- Step 2: copy the sheet. Work on the copy.
- Step 3: Data > Remove Duplicates with every column ticked. Expect 20 removed, 1,050 remain.
- Step 4: filter Quantity for (Blanks). Expect 25. Leave them.
- Step 5: filter Customer_Type for (Blanks). Expect 15. Fill them with Unknown.
- Clear every filter. Check the status bar Count on column A: 1,051, the header plus 1,050 sales.
Picture the receipt pile at closing time: photocopies in the bin, smudged totals left as they are with a note, unsigned ones marked "customer unknown". Every receipt is in exactly one place, and the notes say why.
Practical Exercises
Exercise 1: Very Easy
Filter Payment_Method and look for (Blanks) in the list. Is it there? What does that tell you?
Exercise 2: Simple Application
Do Steps 2 to 5 on your working copy and write down the three counts Excel gives you.
Exercise 3: Combination
Before Step 5, use ISBLANK on Customer_Type, filter it for TRUE, and check the count against the filter on (Blanks).
Exercise 4: A Small Real-World Problem
A colleague says: "Just delete every row with a blank, it is cleaner." Write three sentences explaining, with TechPlug's numbers, what that would do to the count of sales.
Answer to Exercise 4
39 sales have a blank Quantity or a blank Customer_Type, so deleting them would leave 1,011 of 1,050 sales. Every one of those 39 was a real sale, so the count and every total would be too low.
Checkpoint
Q1. Remove Duplicates says "47 duplicate values found and removed". You expected 20. What do you do?
Press Ctrl + Z at once, then check the column ticks. Too few columns were ticked, so look-alike sales were treated as duplicates.
Q2. How can you tell that a sheet is filtered?
Row numbers skip and turn blue, a funnel replaces the filter arrow, and the status bar shows "x of y records found".
Q3. What does ISBLANK return for a cell that holds one space?
FALSE. The cell is not empty, it holds a space.
Q4. Why is Quantity left blank but Customer_Type filled with Unknown?
Quantity is a number used in calculations: a guess would invent sales. Customer_Type is a label for grouping: Unknown keeps the sale in every summary and is honest.
Tick Before You Move On
- ☐ I copied Sales Data to a Raw Data tab before changing anything
- ☐ Remove Duplicates said 20 removed and 1,050 remain
- ☐ I know why every column stays ticked
- ☐ I found the 25 blank quantities and left them
- ☐ I filled the 15 blank customer types with Unknown
- ☐ I can use ISBLANK and ISNUMBER and say when each is the right one
- ☐ I can defend a delete, leave, fill or flag decision
Quick Recap
Remove Duplicates with every column ticked, and read the message as your check. Too few ticked columns turn look-alikes into "duplicates" and delete real sales. A filter hides rows; it never deletes them. Blue row numbers mean something is filtered. ISBLANK finds what is missing; ISNUMBER finds what is the wrong type. Delete what is not real, leave what is honestly unknown, fill labels, flag what you want to track.
Final Scenario
A new analyst at TechPlug is asked how many sales were made from January to March. She deletes every row with a blank "to be safe" and reports 1,011.
You run the routine. Remove Duplicates takes out 20 copies. Of what is left, 39 sales have a blank somewhere, but they all happened. The true count is 1,050.
Cleaning means making the data more true, not making it look tidier.
Next, Week 2, Virtual 1: practise blank handling on every column, then learn LEFT, RIGHT, MID and LEN to pull pieces out of text.