Fuzzy Conditional Formatting in Google Sheets
What It Does
Fuzzy Conditional Formatting in Google Sheets 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 Deduplicate for non-destructive duplicate removal or Schedule Functions for recurring cleanup.
How to Open It
In Google Sheets, go to Extensions > Flookup Data Wrangler > Data Processing Functions and click the Fuzzy Conditional Formatting card.
Sidebar Workflow
-
Select Range
Highlight the target columns and click Select Range. You can select one or multiple columns - the entire row will be highlighted for each duplicate found. -
Set Key Columns
Enter the column numbers to compare for duplicates as a comma-separated list, for example1or1,2.The first column is used by default. Values from the selected columns are combined and compared, so a row is a duplicate only when its combined key matches another row. -
Set Threshold
Adjust the threshold from0to1.The default is0.75.Higher values are stricter and only match very similar values. -
Choose a Colour Scheme
Select a palette: Playful, Professional, Accessible or Pastels. A colour preview renders in the sidebar and you can pick the starting colour for the first group. -
Format
Click Format to apply highlighting. Rows with no similar match stay white.
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. |
Credits and Access
All plans include Percentage Similarity matching. You can set any threshold value.
Undoing or Removing the Highlighting
Fuzzy Conditional Formatting applies colours as static background colours directly to the cells. It does not create conditional format rules. Because of this, you can remove the highlighting in two ways:
-
Undo immediately after applying:
Use Google Sheets' native Undo (
Ctrl+ZorCmd+Z) to step back through the formatting changes. - Reset the background later: Select the formatted range and reset the background colour using Google Sheets' native Reset background colour option.
Troubleshooting
| Issue | What to do |
|---|---|
| No duplicates highlighted | Lower the threshold or review text normalisation first. High thresholds only match very similar values. |
| "Empty range" error | Select a range that contains data before running. |
| Free plan limits | The Free Forever plan limits each run to 50 rows and a set number of runs per day. Data Nova removes these limits. |
Frequently Asked Questions
How do I highlight duplicates in Google Sheets with fuzzy matching?
Open Extensions > Flookup Data Wrangler > Data Processing Functions, click the Fuzzy Conditional Formatting card, select a range, enter the key columns to compare, then click Format to apply colour-coded duplicate groups. The entire row is highlighted for each match.
Can I compare multiple columns at once?
Yes. Enter multiple comma-separated key columns (for example
1,2
) so the combined values from those columns are compared. A row is highlighted only when its combined key matches another row.
Can I undo fuzzy conditional formatting changes?
Yes. The formatting is applied as static background colours, not conditional format rules. Use Google Sheets' native Undo (
Ctrl+Z
or
Cmd+Z
) immediately after applying or select the range and reset its background colour later.