How to Highlight Duplicates in Google Sheets with Fuzzy Conditional Formatting
The Problem: Exact Matching Is Not Enough
Google Sheets has a built-in conditional formatting rule for duplicates. It works perfectly when your data is clean and consistent. The problem is that real-world data is rarely clean or consistent.
A sales spreadsheet might contain "Acme Corporation" in one row, "Acme Corp" in another and "ACME CORPORATION" in a third. A customer list might have "Jon Smith" alongside "John Smith". An inventory sheet could list "Widget-001", "Widget 001" and "widget001". All three examples are duplicates in a business sense, but Google Sheets will not flag any of them as duplicates because no two cells are exactly identical.
This limitation forces analysts into tedious manual review. They scroll through thousands of rows, visually scanning for entries that look similar. It is slow, error-prone and impossible to scale. When you have 20,000 rows of customer data, scanning for near-duplicates by eye is not a viable strategy.
Even when you do find suspected duplicates, there is no standard way to quantify similarity. One person might flag "ABC Corp" and "ABC Corporation" as duplicates while another passes them as different. Without a consistent threshold, your duplicate detection is at the mercy of individual judgment.
What Is Fuzzy Conditional Formatting?
Fuzzy conditional formatting is a method of highlighting cells that are similar but not identical based on configurable similarity thresholds. Instead of asking "are these two values exactly the same?", it asks "are these two values close enough to be considered the same?".
Flookup Data Wrangler implements fuzzy conditional formatting through two distinct matching modes: percentage similarity and sound similarity. Each mode uses a different algorithm to compute how close two values are. You choose the mode that best fits the type of data you are working with.
When you apply fuzzy conditional formatting, Flookup analyses every cell in your selected range and groups cells into clusters of similar values. Each group receives a distinct highlight colour so you can visually identify duplicate clusters across your sheet at a glance. Groups that fall below your similarity threshold remain unformatted.
The result is a spreadsheet where near-duplicates are immediately visible, colour-coded by group, with no manual scanning required.
How It Works: Percentage Similarity
Percentage similarity uses an edit-distance algorithm to compare strings character by character. The algorithm calculates how many single-character edits (insertions, deletions or substitutions) are needed to turn one string into another. The result is a score from 0 to 100 percent indicating how similar the two strings are.
For example, comparing "John Smith" to "Jon Smith" requires one deletion (the letter "h") and scores approximately 90 percent similarity. Comparing "John Smith" to "Jonathan Smith" requires several insertions and scores approximately 65 percent similarity. Comparing "John Smith" to "Sarah Jones" requires a complete rewrite and scores very low.
You set a threshold percentage and Flookup highlights all pairs of cells whose similarity score meets or exceeds that threshold. A threshold of 85 percent means any two cells that are at least 85 percent similar will be flagged as duplicates. This mode works well for text where spelling matters: product names, addresses, descriptions and codes.
Percentage similarity is also sensitive to word order. "Customer Name" and "Name Customer" require more edits to transform into each other than you might expect, which makes this mode useful when word order is meaningful in your data.
How It Works: Sound Similarity
Sound similarity takes a completely different approach. Instead of comparing characters, it converts each string into a phonetic code based on how the word sounds when spoken aloud. Words that sound the same but are spelled differently will produce the same or similar phonetic codes.
This is powerful for catching name variations. "Smith" and "Smythe" produce the same phonetic code. "Catherine" and "Katherine" sound nearly identical. "Phillips" and "Philips" are phonetically equivalent. Sound similarity catches all these cases.
Sound similarity operates on a binary match basis rather than a sliding scale. Either two strings sound the same or they do not. This makes it a good complement to percentage similarity. Use sound similarity when you are working with names where phonetic drift is common and use percentage similarity when you need fine-grained control over how close a match must be.
In practice, sound similarity works best for people's names, company names and locations. It is less useful for codes, IDs or product numbers where phonetic equivalence is meaningless.
Step-by-Step: Highlight Duplicates in Your Sheet
Step 1: Open Flookup Data Wrangler
Install Flookup from the Google Workspace Marketplace if you have not already. Once installed, open your Google Sheet and launch Flookup from the Extensions menu. The sidebar will appear on the right side of your screen.
Step 2: Choose Conditional Formatting
Inside the sidebar, select the Conditional Formatting option. This opens the fuzzy conditional formatting configuration panel where you define your matching rules.
Step 3: Select Your Range
Click the range selector and highlight the cells you want to analyse. You can select a single column, multiple columns or a specific cell range. Flookup will compare every cell in the range against every other cell.
Step 4: Choose Matching Mode
Select either Percentage Similarity or Sound Similarity depending on the type of data in your selected range. For customer names, sound similarity is often the right choice. For addresses or product descriptions, percentage similarity gives you more control.
Step 5: Set Your Threshold
If you chose percentage similarity, enter your threshold value. Start at 85 percent and adjust up or down based on the results you see. If you chose sound similarity, the threshold is implicitly binary and you can skip this step.
Step 6: Pick Highlight Colours
Choose how you want duplicate groups to be highlighted. Flookup assigns a distinct colour to each group of similar values. You can select a predefined colour palette or customise the colours yourself.
Step 7: Apply and Review
Click Apply. Flookup will process your range and highlight all near-duplicate groups. Review the results. If you see too many false positives, increase the threshold. If you see missed duplicates, lower the threshold. The operation is non-destructive and can be reapplied as many times as needed.
Multi-Column Matching
Sometimes a duplicate is defined by a combination of values across multiple columns. For example, you might have separate columns for First Name and Last Name. Neither column alone is a reliable indicator of duplication, but "John Smith" in one row and "Jon Smythe" in another row is clearly a duplicate when the two columns are combined.
Flookup supports multi-column fuzzy matching directly in the conditional formatting panel. When you select a range that spans multiple columns, Flookup concatenates the values from each row into a single compound value and then applies the fuzzy matching algorithm to that compound value.
This lets you catch cross-column duplicates that would otherwise be invisible. A customer whose first name is spelled differently in one row but whose last name matches exactly is still flagged as a potential duplicate. The same applies to address blocks where street number, street name and city form a compound identifier.
Multi-column matching works with both percentage similarity and sound similarity modes. The compound value is constructed by joining each column's content with a separator, so the matching algorithm sees the full combined text rather than individual fragments.
Choosing the Right Threshold
The threshold value you choose directly determines how aggressive your duplicate detection is. Too high and you miss real duplicates. Too low and you drown in false positives. The table below shows common threshold ranges and what they catch.
| Threshold | What Matches | Use Case |
|---|---|---|
| 95% | Single-character typos, minor spacing differences | Product codes, serial numbers, strict identifiers |
| 85% | Common spelling mistakes, missing letters, extra spaces | Customer names, email addresses, phone numbers |
| 75% | Word-order differences, abbreviations, truncated entries | Company names, job titles, department names |
| 65% | Heavy abbreviations, missing words, partial matches | Address lines, product descriptions, long text fields |
| 55% | Loosely related text, shortened forms, acronyms | Broad exploratory deduplication, data audits |
Start with 85 percent for most use cases. Review the highlighted groups and adjust. Your goal is to minimise both false positives (cells highlighted that are not actually duplicates) and false negatives (duplicates that are not highlighted). A quick visual scan of your results will tell you whether the threshold is set correctly.
For sound similarity mode, the concept of threshold does not apply in the same way because the comparison is binary. However, you can influence sensitivity by adjusting how the phonetic encoding is applied. Flookup uses a standard metaphone algorithm which is optimised for English-language names and words.
Frequently Asked Questions
What is fuzzy conditional formatting in Google Sheets?
Fuzzy conditional formatting is a method of highlighting cells that are similar but not identical based on configurable similarity thresholds. Unlike Google Sheets' built-in exact-match duplicate highlighting, fuzzy conditional formatting can detect near-duplicates that differ by typos, spelling variations, word order or phonetic equivalents.
How do I highlight duplicates in Google Sheets using fuzzy matching?
Install Flookup Data Wrangler from the Google Workspace Marketplace, open the sidebar and choose Conditional Formatting. Select your range, choose between percentage similarity or sound similarity mode, set your threshold value and pick highlight colours. Flookup will apply colour-coded formatting to all groups of near-duplicate values.
What is the difference between percentage similarity and sound similarity?
Percentage similarity compares strings character by character using edit-distance algorithms. It is ideal for text where spelling matters, like product codes or addresses. Sound similarity converts strings to phonetic codes and compares how they sound when spoken. It catches names and words that sound the same but are spelled differently, like Smith versus Smythe.
What similarity threshold should I use for fuzzy duplicate detection?
The optimal threshold depends on your data. For strict matching with minor typos, use 90 to 95 percent. For moderate fuzziness like word-order differences, use 75 to 85 percent. For loose matching that catches heavily abbreviated entries alongside full forms, use 60 to 70 percent. Test with a sample of your data to calibrate the threshold before applying it to your full dataset.
Can I highlight duplicates across multiple columns with fuzzy conditional formatting?
Yes. Flookup Data Wrangler supports multi-column fuzzy matching. You can select a range spanning multiple columns and the system will combine the values when computing similarity scores. This is useful for scenarios like matching first and last name pairs where the individual columns may not be duplicates but the combined value is.