24.1 Finding Duplicate Values

Before removing duplicates, you first need to find and review them. Excel provides several ways to identify duplicate values.

Method 1 — Conditional Formatting Duplicate Highlight

1. Select the column(s) to check for duplicates (e.g., A2:A200 for an ID column)
2. Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values…
3. The dialog opens:
   • First dropdown: choose Duplicate (to highlight all repeated values) or Unique (to highlight values that appear only once)
   • Second dropdown: choose the formatting (Light Red Fill, Yellow Fill, Green Fill, or Custom Format…)
4. Click OK — all duplicate values are immediately highlighted
5. To review them: Data → Sort the column — duplicates cluster together for easy identification

Method 2 — COUNTIF Formula to Flag Duplicates

Method 3 — COUNTIFS for Multi-Column Duplicates

When a record is a duplicate only if multiple columns match (e.g., same First Name AND Last Name AND Date of Birth):

Method 4 — Sorting to Surface Duplicates Visually

1. Sort the data by the column(s) that should be unique (e.g., Employee ID)
2. Duplicate values cluster together making them immediately visible
3. Add a formula in a helper column: =IF(A2=A3,"DUPLICATE","") to flag when adjacent rows have the same value after sorting

24.2 Removing Duplicates — Excel's Built-in Tool

Excel's Remove Duplicates tool permanently deletes duplicate rows based on matching values in columns you select. It keeps the first occurrence and removes all subsequent duplicates.

Using Remove Duplicates

1. Click any cell inside the data range (or inside an Excel Table)
2. Data tab → Data Tools group → Remove Duplicates
3. — or — Table Design tab → Tools group → Remove Duplicates (when inside a Table)
4. The Remove Duplicates dialog opens showing all columns as checkboxes
5. Select which columns to use to identify duplicates:
   • Tick all columns — a row is only a duplicate if every column matches another row exactly
   • Tick specific columns — rows are duplicates if only those columns match (e.g., Employee ID only)
6. Ensure "My data has headers" is ticked if row 1 contains column headers
7. Click OK
8. A message confirms: "X duplicate values found and removed; Y unique values remain"

Remove Duplicates vs Keep Duplicates for Analysis

24.3 Conditional Formatting — Complete Overview

Conditional Formatting (CF) automatically applies formatting (fill colour, font colour, borders, icons, data bars) to cells based on rules you define. Rules can be based on cell values, formulas, or relationships between cells. When data changes, the formatting updates automatically.

All Conditional Formatting Rule Types

24.4 Highlight Cells Rules — In Depth

Applying Highlight Rules

1. Select the cells to apply the rule to
2. Home → Conditional Formatting → Highlight Cells Rules
3. Choose the rule type
4. Fill in the threshold value(s) and choose the format
5. Click OK

All Highlight Cell Rule Types

Using a Cell Reference as the Threshold

Instead of typing a fixed value, you can reference a cell for the threshold — changing the cell automatically updates the rule:

24.5 Top/Bottom Rules

Top/Bottom Rules automatically identify the highest or lowest values in a range and apply formatting to them. Unlike Greater Than rules with a fixed threshold, these rules adapt dynamically to whatever values are in the range.

Available Top/Bottom Rules

Top/Bottom Rule Examples

24.6 Formula-Based Conditional Formatting — The Most Powerful Option

When the built-in rules are not enough, you can write any Excel formula as a conditional formatting rule. If the formula returns TRUE (or any non-zero value), the formatting is applied. This enables powerful scenarios like highlighting entire rows based on a condition in one column, comparing values across columns, or using complex logical expressions.

Creating a Formula-Based Rule

1. Select the range to apply formatting to (e.g., A2:F51 to highlight entire rows)
2. Home → Conditional Formatting → New Rule…
3. Select "Use a formula to determine which cells to format"
4. In the formula box, type a formula that evaluates to TRUE or FALSE
5. Click Format… to set the fill colour, font, or border
6. Click OK twice

Essential Formula-Based CF Examples

Row Highlighting — The Most Important Pattern

Highlighting an entire row based on a value in one column is the most requested CF technique in SA offices. The key is using a mixed reference that locks the column:

24.7 Managing Multiple Conditional Formatting Rules

A range can have multiple CF rules applied to it. Understanding how they interact is critical to getting the formatting you intend.

Rule Priority (Order)

• Rules are evaluated from top to bottom in the Manage Rules list
• The rule at the top has the highest priority
• When two rules both apply to the same cell, the higher-priority rule's formatting wins (unless "Stop If True" is ticked)
• Use the ▲/▼ arrows in Manage Rules to reorder rules

Stop If True

• When ticked on a rule, Excel stops evaluating lower-priority rules for that cell if the current rule is TRUE
• This prevents a lower rule from overriding or modifying the formatting of a higher rule
• Example: Rule 1 (Overdue → Red). Rule 2 (Value > R10,000 → Green). Without Stop If True on Rule 1, an overdue invoice over R10,000 could get green formatting from Rule 2. With Stop If True on Rule 1, the overdue rule wins and Rule 2 never runs for that cell.

Practical Multi-Rule Setup — Invoice Status Colour Coding

24.8 SA Data Quality Workflow

A systematic approach to identifying and resolving data quality issues in SA business spreadsheets:

Step 1: Check for Duplicates

Step 2: Check for Missing Data

Step 3: Check for Out-of-Range Values

Step 4: Check for Inconsistent Text

24.9 Quick Self-Check

Q1: An employee database has 500 rows. You suspect there are duplicate Employee IDs in column A. Describe two methods to identify them before deciding whether to delete them.

✓ Method 1 (Conditional Formatting): Select A2:A500 → Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values → choose Red fill → OK. All IDs appearing more than once are immediately highlighted red. Sort column A to cluster duplicates together for visual review. Method 2 (COUNTIF formula): in a helper column B, enter =COUNTIF($A$2:$A$500,A2) and copy down. Values of 2 or higher indicate duplicate IDs. Add a further formula =IF(B2>1,"DUPLICATE","") then filter column B to show only "DUPLICATE" rows for review. Using COUNTIF lets you see which IDs appear how many times, giving more information than CF alone before you decide what to delete.

Q2: You have an invoice table with columns: Invoice# (A), Date (B), Customer (C), Amount (D), Status (E). You want to highlight the entire row in orange for all invoices that are both Unpaid AND have a date more than 30 days in the past. Write the formula for the conditional formatting rule and describe the range to apply it to.

✓ Select the entire table range: A2:E500 (or however many rows). New Rule → Use a formula. Formula: =AND($E2="Unpaid",$B2<TODAY()-30). $E2 (column E locked, row shifts) checks if Status is "Unpaid"; $B2 (column B locked, row shifts) checks if the date is more than 30 days ago. AND ensures both conditions must be true. Format: orange fill. Click OK. Every row where Status = "Unpaid" AND Date is older than 30 days turns orange across all 5 columns. The column-locked references ($E2, $B2) ensure that as the rule applies to each row, it always checks the same columns E and B for that row.

Q3: What is the "Stop If True" option in Conditional Formatting Rules Manager, and why would you use it?

✓ "Stop If True" tells Excel to stop evaluating lower-priority rules for a cell if the current rule's condition is TRUE and its formatting has been applied. Without it, multiple rules can fire on the same cell, and lower-priority rules may override or partially overwrite the higher-priority formatting. Example: Rule 1 (Overdue → Red, Stop If True = YES) and Rule 2 (Amount > 50000 → Green). An overdue invoice for R80,000 would normally trigger both rules. Without Stop If True on Rule 1, Rule 2 would also run and might change the fill to green, overriding the red overdue warning. With Stop If True on Rule 1, Excel applies red and immediately stops — Rule 2 never runs for that cell. Use Stop If True when a higher-priority condition should "win" completely and prevent any other formatting from applying.

Q4: You want alternating row shading (grey on even rows, white on odd rows) for a data range A2:G200 using Conditional Formatting. Write the formula and explain how it works.

✓ Select A2:G200 → New Rule → Use a formula → =MOD(ROW(),2)=0 → Format: light grey fill. ROW() returns the current row number for each cell being evaluated. MOD(ROW(),2) calculates the remainder when the row number is divided by 2: even rows return 0, odd rows return 1. So MOD(ROW(),2)=0 is TRUE for even rows (2, 4, 6, 8...) and FALSE for odd rows (1, 3, 5, 7...). Even rows get the grey fill; odd rows get no special formatting (they stay white/unformatted). There is no cell reference in this formula at all — it evaluates independently for each cell based purely on which row it is in.

Q5: A column of product codes (column B) should each be 8 characters long (e.g., "PROD0001"). How do you use Conditional Formatting to highlight any codes that are the wrong length?

✓ Select B2:B200 (the product code column) → Home → Conditional Formatting → New Rule → Use a formula → enter: =LEN(B2)<>8. Format: Red fill → OK. LEN(B2) counts the number of characters in the cell. <>8 means "not equal to 8". The formula returns TRUE for any cell where the product code is not exactly 8 characters — shorter codes, longer codes, or blank cells all get the red highlight. Note that B2 in the formula is a relative reference (no $ signs) because we want it to shift to B3, B4, etc. as the rule applies to each row in the selection.

Q6: You use Remove Duplicates on a 1,000-row dataset and Excel reports "150 duplicate values found and removed; 850 unique values remain." Immediately afterwards, you realise you should have checked the "Customer Name" column in addition to the "Invoice Number" column in the Remove Duplicates dialog. Can you undo this? What should you do next time?

✓ If you pressed Ctrl+Z immediately (before doing anything else) and the undo is still available in the undo history, you can undo. However, if you closed the dialog, clicked elsewhere, or performed any action, the removal may be permanent. Remove Duplicates warns that it cannot be undone once the confirmation dialog is dismissed and subsequent actions are performed. Correct recovery: restore from the backup copy of the file you should have made before running the operation. This is why the critical workflow is: (1) Save a copy of the original file (File → Save As with a new name, or copy the sheet to a new workbook) BEFORE running Remove Duplicates. (2) Use Conditional Formatting to review duplicates first. (3) Only run Remove Duplicates after carefully reviewing which rows are genuine duplicates vs legitimate repeats. Next time: in the Remove Duplicates dialog, tick all columns that together define a unique record — in this case both "Invoice Number" AND "Customer Name" to prevent removing an invoice number that appears for two different customers.

✓ Module 24 Complete — You Have Learned:

• Finding duplicates — 4 methods: CF Highlight → Duplicate Values; COUNTIF helper column (=COUNTIF($A$2:$A$200,A2) > 1 = duplicate); COUNTIFS for multi-column duplicates; sorting to cluster and =IF(A2=A3,"DUPLICATE","") to flag adjacent matches
• Remove Duplicates tool — Data → Remove Duplicates or Table Design → Remove Duplicates; column selection (all = exact row duplicate; specific = key column match); My data has headers; confirmation message; critical warning (permanent, cannot undo — always save a copy first)
• Remove Duplicates vs alternatives — comparison of Remove Duplicates, CF highlight for review, Advanced Filter for unique list extraction, SUMPRODUCT for distinct count
• Conditional Formatting overview — all 6 rule categories (Highlight Cells, Top/Bottom, Data Bars, Colour Scales, Icon Sets, Formula-based)
• Highlight Cells Rules — all 7 types (Greater Than, Less Than, Between, Equal To, Text Contains, Date Occurring, Duplicate/Unique); using a cell reference as threshold (=$F$1 for dynamic threshold)
• Top/Bottom Rules — all 6 options (Top 10 Items, Top 10%, Bottom 10 Items, Bottom 10%, Above Average, Below Average); dynamic — adapts when data changes
• Formula-based CF — New Rule → Use a formula; reference rule (formula for top-left cell, applies relatively to all cells); mixed references for row/column locking ($E2 for column check, A$2 for row check); 10-formula example table including entire row highlight, zebra stripes (MOD(ROW(),2)=0), duplicate detection (COUNTIF), blank detection (ISBLANK), weekend highlight (WEEKDAY), SA ID length check, contract expiry warning, overdue AND unpaid, above average
• Row highlighting pattern — select entire row range (A2:H200), formula with locked column ($E2="Cancelled"), row number shifts per row
• Multiple CF rules — priority order (top = highest); conflict resolution; Stop If True (prevents lower rules from running); 4-level invoice colour-coding example
• SA data quality workflow — 4-step process: duplicate check, missing data (ISBLANK + COUNTBLANK), out-of-range values (OR with min/max), inconsistent text (TRIM/PROPER + valid list check)