How to Highlight Duplicates in Google Sheets with Fuzzy Conditional Formatting
What It Does
Fuzzy Conditional Formatting helps you highlight duplicates in Google Sheets, including near-duplicate values that standard exact matching usually misses. Instead of returning formulas in cells, this tool applies colour directly to matching records.
It is useful when you want to review quality issues visually before you delete rows or run wider cleanup logic. After review, you can use DEDUPE for non-destructive duplicate removal or Schedule Functions for recurring cleanup.
How to Open It
In Google Sheets, go to Extensions > Flookup Data Wrangler > Fuzzy Conditional Formatting.
Sidebar Workflow
- Select Range
Highlight the target column and click Select Range. - Choose Match Type
Pick Percentage Similarity or Sound Similarity. - Set Sensitivity
For Percentage Similarity, adjust threshold from0to1. For Sound Similarity, threshold is disabled because it is not used. - Choose Keep Mode
Select First or Last based on which row representation you prefer in each duplicate chain. - Choose Colour Scheme
Pick a palette and click a swatch to set the active starting colour. - Format
Click Format to apply highlighting.
Matching Modes and Threshold Rules
| Mode | How matching works | Threshold usage |
|---|---|---|
| Percentage Similarity | Uses fuzzy character/token similarity scoring. | Used directly. Higher values are stricter. |
| Sound Similarity | Uses phonetic encoding to group words that sound alike. | Not used. Slider is disabled automatically. |
Credits and Access
- Free users can run Percentage Similarity with a threshold of
0.9and above. - Sound Similarity requires a paid plan.
- Thresholds below
0.9require a paid plan. - Paid users also require available credits. If credits are exhausted, the sidebar returns a clear top-up message.
Undo and Clear
- Undo reverses one formatting step at a time and reports progress, for example
Formatting 2 of 4 undone. - Clear removes formatting from the currently selected range.
Troubleshooting
| Issue | What to do |
|---|---|
| "Please select a single column" | Select one column only. Multi-column selections are intentionally blocked. |
| "Thresholds below 0.9 require a paid subscription" | Raise threshold to 0.9 or upgrade your plan. |
| "Credits exhausted" | Top up credits from your plan page and run again. |
| No duplicates highlighted | Lower threshold (paid plans), switch to Sound Similarity, or review text normalisation first. |
Frequently Asked Questions
How do I highlight duplicates in Google Sheets with fuzzy matching?
Open Extensions > Flookup Data Wrangler > Fuzzy Conditional Formatting, select one column, choose Percentage Similarity or Sound Similarity and click Format to apply colour-coded duplicate groups.
Can I use Sound Similarity on the free plan?
No. Sound Similarity requires a paid plan. Free users can run Percentage Similarity at thresholds of 0.9 and above.
Can I undo fuzzy conditional formatting changes?
Yes. Use Undo to reverse one formatting step at a time, or use Clear to remove formatting from the selected range.