Highlight Duplicates in Google Sheets: Both Matches

To highlight both the original and repeated values in Google Sheets, select the cells, open Format > Conditional formatting, choose Custom formula is, and enter a COUNTIF rule. For values in A2:A1000, use =AND(A2<>"",COUNTIF($A$2:$A$1000,A2)>1); choose a formatting style and click Done. Every nonblank value that occurs more than once—including its first occurrence—will be highlighted.

What the duplicate rule does

Conditional formatting changes a cell’s appearance when a condition is true; it does not delete or rearrange data. The COUNTIF rule checks how many times the value in each cell appears in the selected range. If the count is greater than one, the condition is true, so Sheets applies the chosen color or text style.

This is different from a rule that marks only later repetitions. A duplicate is not just the second appearance: if “North” appears in A4 and A9, both cells meet the test because the count for “North” is two. If a value appears five times, all five matching cells are highlighted. The first occurrence is included by design.

The example excludes empty cells. Without the AND check, COUNTIF can treat blank cells as repeated values and color them, which is rarely useful. If blank entries are meaningful in your data and you specifically want to flag them, you can remove the blank check—but consider whether an empty cell should really count as a duplicate.

Set up conditional formatting step by step

Select the data range, such as A2:A1000. Start at the first row containing data, not the header.
Choose Format > Conditional formatting. The Conditional format rules panel opens on the right.
Check that Apply to range shows the intended cells, for example A2:A1000.
Under Format cells if, select Custom formula is.
Enter =AND(A2<>"",COUNTIF($A$2:$A$1000,A2)>1) and choose a fill color or other formatting.
Click Done. Check a known repeated value to confirm that its first and later occurrences are all formatted.

The formula’s first cell reference must correspond to the top-left cell of the apply range. If your range begins at A3, use A3 in the formula. This lets Sheets evaluate each row relative to its own cell. Keep the COUNTIF range fixed with dollar signs so it continues to search the full list while the formula is evaluated down the range.

Understand the formula references

In COUNTIF($A$2:$A$1000,A2), the first argument is the range to search and the second is the value to count. The dollar signs make the search range absolute: it stays A2:A1000 for every formatted cell. The reference A2 is relative, so it becomes A3, A4, and so on as Sheets checks lower rows.

The test >1 is what includes every occurrence. A matching value found once returns a count of 1 and is not highlighted. A value found twice returns 2, so both cells satisfy the condition. The leading = is required because Google Sheets expects a formula in the custom-formula field.

For a short list, you can use an open-ended range such as $A$2:$A so new entries below the current data are included automatically. A bounded range is easier to inspect and may be preferable in a large sheet. Make sure the apply range grows as well; a rule applied only to A2:A1000 will not format new values below row 1000, even if the COUNTIF search range is open-ended.

Highlight repeated values across several columns

If a value should count as a duplicate wherever it appears in a rectangular block, select the block, such as A2:D100, and use =AND(A2<>"",COUNTIF($A$2:$D$100,A2)>1). The search range is locked, but A2 adjusts for each cell in the selected block. This highlights matching values whether they appear in the same column or in different columns.

That approach treats each cell as an individual value. It does not decide whether two records or rows are duplicates. For example, a customer name appearing twice may be expected if the rows represent separate orders. Before applying a cross-column rule, decide whether you mean “this value appears anywhere more than once” or “these records describe the same thing.” Those are different checks.

For a multi-column record, compare the fields that define a duplicate. Suppose columns A and B hold an email address and account type, and the combination must repeat before a row counts as a duplicate. Select the rows to format and use a COUNTIFS rule such as =COUNTIFS($A$2:$A$1000,$A2,$B$2:$B$1000,$B2)>1. This counts rows where both fields match. If column A is a required identifier, add a nonblank check such as =AND($A2<>"",COUNTIFS($A$2:$A$1000,$A2,$B$2:$B$1000,$B2)>1) to avoid flagging empty records. Apply the rule across the desired row cells, for example A2:B1000.

Match whole records, not just individual cells

To highlight rows that repeat across columns A, B, and C, compare all three fields together. If A is a required field, select A2:C1000 and enter =AND($A2<>"",COUNTIFS($A$2:$A$1000,$A2,$B$2:$B$1000,$B2,$C$2:$C$1000,$C2)>1). The dollar signs before the column letters keep each comparison in its intended column, while the row number changes as the rule moves down the sheet.

For example, a table might contain a date, product code, and location. Two rows with the same product code but different dates are not necessarily duplicate records. Comparing all three fields highlights only rows where the complete combination repeats. If your definition of a duplicate uses only some columns, include only those columns in COUNTIFS; the remaining fields can differ without affecting the result.

Be deliberate about blanks in the comparison fields. COUNTIFS can match blank cells, so two incomplete rows may satisfy the comparisons. Requiring a nonblank key, as in the example, prevents rows with no product code or identifier from being treated as duplicate records. If the key itself can legitimately be blank, choose another field or add checks appropriate to your data.

What counts as the same value?

COUNTIF is generally case-insensitive in Google Sheets, so text such as “Maple” and “MAPLE” is treated as a match. That is useful for ordinary lists of names or categories, but it may not fit data where capitalization distinguishes identifiers. If capitalization matters, the basic COUNTIF rule is not a case-sensitive duplicate test.

Extra spaces can also make data appear inconsistent. A value with a trailing space may not behave like the same visibly printed value without that space. If entries were copied from emails, websites, or exported files, inspect suspicious cells and clean the source data before relying on duplicate highlighting. Google Sheets has functions such as TRIM for removing ordinary extra spaces, but applying cleanup to a separate helper column can make the changes easier to review and preserve the original values.

COUNTIF criteria can interpret certain characters, including asterisks and question marks, as wildcards. If your identifiers literally contain those characters, a simple COUNTIF test may not match them in the way you expect. Test the rule on representative values or use a helper column with a carefully chosen comparison method. This is a narrow edge case, but it matters for codes and text that contain wildcard symbols.

Common problems and how to fix them

The first copy is not highlighted: Check that the condition uses a count greater than 1, not a condition designed to isolate later rows. Confirm that the first and later copies are both inside the COUNTIF search range.
The wrong row or cell is checked: The reference in the custom formula should match the top-left cell in Apply to range. For A2:A1000, begin with A2; for B5:B, begin with B5.
Headers are colored: Start the apply range below the header row and make the formula’s first reference match that first data cell.
Blank cells are colored: Include the nonblank test, for example AND(A2<>"",COUNTIF(... )>1), with a complete COUNTIF expression in the actual rule.
Some duplicates are missed: Check for leading or trailing spaces, confirm the search range includes every occurrence, and make sure the compared fields match your definition of a duplicate.
The rule works only for the current rows: Expand Apply to range when new data is added, or choose an open-ended range such as A2:A and use a corresponding formula reference.
A different color appears: Review the rules in the conditional-formatting panel. Multiple rules can apply to the same cells, so another rule may also be changing their appearance.

When troubleshooting, select one highlighted or missed cell and inspect its actual contents in the formula bar. Two values can look identical on screen while differing because of spaces, punctuation, or an unnoticed character. Also verify that the rule’s applied range and its formula reference start on the same row; a one-row offset is a frequent cause of confusing results.

Highlighting duplicates versus removing them

Highlighting is a review step; it leaves the source data intact. That is the safer choice when repeated entries may be legitimate, when you need to inspect each case, or when other columns contain information that should not be discarded. You can change the formatting rule or delete it later without changing the cell values.

If you have confirmed that repeated records should be removed, Google Sheets provides Data > Data cleanup > Remove duplicates. Review the selected range and the columns used to identify duplicates before confirming. Choosing only one column can remove rows that share that field even when their other details differ. Consider making a copy of the sheet or preserving the original data first, especially if the cleanup affects a shared or important table.

Another option is a helper column that labels whether a value has appeared before. That can support sorting, filtering, or reporting, but it answers a different question from the formatting rule: a “seen earlier” test typically marks later occurrences rather than all members of a duplicate group. Choose the method that matches the task—visual review, identification of later repeats, or actual removal.

Frequently Asked Questions

Can Google Sheets highlight both the first and second duplicate?
Yes. Use a conditional-formatting custom formula that checks whether COUNTIF is greater than 1. Every matching cell is evaluated against the full range, so the first occurrence and later occurrences are all highlighted.

Does Google Sheets have a built-in duplicate highlight button?
The practical built-in method is conditional formatting with a custom formula. In the Conditional format rules panel, choose Custom formula is and enter a COUNTIF rule; choose the color yourself. This is separate from the Data cleanup command that removes duplicates.

Can I highlight duplicates in one column but ignore blanks?
Yes. Add a nonblank test, as in =AND(A2<>"",COUNTIF($A$2:$A$1000,A2)>1), and use a range starting at A2. This flags repeated nonblank values without coloring empty cells.

Can I highlight duplicate rows based on two columns?
Yes. Use COUNTIFS to count rows where both selected fields match, then apply the custom formula to the rows you want formatted. Lock the comparison columns and ranges, while leaving the row references relative so the rule checks each record.

Will the rule update when I add more data?
It updates for new cells only if they are included in the rule’s Apply to range. An open-ended range such as A2:A can include later entries; with a bounded range, expand the applied range when the list grows.

Excel comparison

This article describes Google Sheets in a web browser. Excel has its own conditional-formatting interface and rule options, so the menu paths are not interchangeable. The COUNTIF idea is also used in Excel, but for Sheets follow the steps above under Format > Conditional formatting; do not look for Google Sheets’ right-side rules panel in Excel.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.