Google Sheets COUNTIF Conditional Formatting Guide

To use COUNTIF in Google Sheets conditional formatting, select the cells to format, open Format > Conditional formatting, choose Custom formula is, and enter a formula such as =COUNTIF($A$2:$A$100,A2)>1. Applied to A2:A100, it highlights each value that appears more than once; the dollar signs keep the list range fixed while the row reference adjusts for each cell.

What COUNTIF conditional formatting does

COUNTIF counts cells in a range that meet a criterion. Conditional formatting uses the result of that count to decide whether to apply a visual style, such as a fill color or bold text. The formula does not change the cell contents, remove repeated entries, or create a separate count column. It simply evaluates the rule as the sheet’s values change.

The basic function has two arguments: a range to examine and a criterion to match. In a conditional-formatting rule, a comparison such as greater than one turns the count into a true-or-false test. A true result applies the selected formatting to the cell. A false result leaves it unchanged.

This is useful for finding duplicate names, invoice numbers, email addresses, product codes, or other values. It can also flag unique values, repeated categories, or rows that meet a condition. The important detail is that the formula must be written relative to the top-left cell in the rule’s apply range.

Set up a duplicate-highlighting rule

Select the cells you want to check, such as A2:A100. If row 1 contains a heading, leave it out of the selected range.
Choose Format > Conditional formatting. The conditional-formatting panel opens on the right.
Check the Apply to range field and set it to A2:A100 if needed.
Under Format rules, open the condition menu and select Custom formula is.
Enter =COUNTIF($A$2:$A$100,A2)>1, choose a formatting style, and select Done.

Suppose A2:A7 contains Elm, Fir, Elm, Oak, Fir, and Ash. The count for Elm is two, as is the count for Fir, so both occurrences of each are formatted. Oak and Ash each occur once and are not formatted. The comparison is against the entire checked range, which is why this version highlights the first occurrence as well as later ones.

Understand the references in the formula

In =COUNTIF($A$2:$A$100,A2)>1, $A$2:$A$100 is an absolute range: the dollar signs lock both its column and row. The criterion A2 is relative. When Sheets evaluates the rule for A3, it checks the value in A3 against the same fixed list; for A4, it checks A4, and so on.

That combination is essential. If the criterion were locked as $A$2, every row could be tested against the value in A2 rather than its own value. If the list range were left fully relative, it could shift as the rule is evaluated down the column. A common way to remember the pattern is: lock the range being counted, and leave the cell being tested relative.

For a different starting row, use the corresponding first cell. For example, if the apply range begins at B5, write a rule that refers to B5, not B2. The formula’s cell reference should match the top-left cell of the apply range even when the counted list includes a larger area.

Highlight only later duplicate occurrences

Sometimes the first copy is the original record and only subsequent copies should be flagged. Use a growing range that starts at the first data row and ends at the current row. With values in A2:A100 and the rule applied to that same range, enter =COUNTIF($A$2:A2,A2)>1.

For the first data cell, the formula counts from A2 through A2, so its count is one and it stays unformatted. In the next row, the count extends through A3. If A3 repeats a value already seen in A2, its count is two and it is formatted. As the rule continues downward, the ending row advances while the starting cell remains fixed. This approach is useful for reviewing a list in order, but it answers a different question from the full-range rule: “Has this appeared earlier?” rather than “Does this appear more than once anywhere?”

Adapt the rule for blanks, unique values, and rows

COUNTIF may count blank cells when the criterion cell is blank. If empty cells are included in the apply range, that can make blanks look like duplicates. To ignore empty cells while highlighting repeated entries, use =AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1). Apply it to A2:A100. The first test excludes blanks; the COUNTIF test checks whether the nonblank value occurs more than once.

To highlight values that appear exactly once, change the comparison. With the same data layout, use =COUNTIF($A$2:$A$100,A2)=1. Add the blank check as well if blank cells should not be treated as unique entries: =AND(A2<>"",COUNTIF($A$2:$A$100,A2)=1).

You can also format an entire row according to a repeated ID in one column. If IDs are in A2:A100 and the table spans A2:D100, set the apply range to A2:D100 and use =COUNTIF($A$2:$A$100,$A2)>1. The dollar sign before A fixes the ID column as the rule moves across the row, while the row number remains relative as it moves down. Each row is colored when its ID appears more than once in column A.

Choose the right range and formatting scope

The apply range determines which cells can receive formatting; the COUNTIF range determines which cells are searched. They can be the same, but they do not have to be. For example, a rule can check the ID in column A against all IDs in A2:A100 while applying a color to the related information across A2:D100.

For clarity and predictable performance, use the actual data area when you know its size. A whole-column range can be convenient when new rows are regularly added, but it makes the rule consider many empty cells as well. If you use an open-ended range, make sure the formula’s reference and the apply range have the same intended starting row. For a growing table, you can extend both ranges when adding records, or choose a range large enough for expected additions and exclude blanks with an AND test.

When a sheet has multiple conditional-formatting rules, inspect their apply ranges and conditions together. Two rules may both match the same cell and produce unexpected-looking results, especially if they set different colors or styles. Keep rules focused, remove obsolete ones, and use a helper column if the logic becomes difficult to read or maintain.

What COUNTIF matches and what it does not

COUNTIF is generally case-insensitive in Google Sheets. For example, a repeated name in uppercase and lowercase is not a reliable way to distinguish two different people with this rule. If capitalization carries meaning in your data, verify the result with a method designed for case-sensitive comparison instead of assuming COUNTIF treats letter case as unique.

COUNTIF criteria can also use wildcard characters: an asterisk represents any sequence of characters, and a question mark represents one character. That matters if the criterion is entered as a text pattern. When the criterion is a cell reference containing an asterisk or question mark, those characters may be interpreted as wildcards rather than ordinary punctuation. For product codes or text with wildcard characters, check the matching behavior with a small sample before applying the rule to a large range.

Also distinguish an exact duplicate check from a partial-text check. The basic formula compares the cell value as a criterion; it does not automatically trim spaces or normalize inconsistent punctuation. “North Shop” and “North Shop ” may look alike but contain different text because one has an extra trailing space. Cleaning or standardizing the source data can be more reliable than trying to use formatting to conceal inconsistencies.

Fix common conditional-formatting problems

Nothing is highlighted: confirm that the apply range includes the data and that the formula refers to its top-left cell. Check that the comparison is greater than one, not greater than zero.
The wrong rows are highlighted: review the dollar signs. The counted list should usually be locked, while the tested cell’s row should usually move.
Blank cells are colored: add a nonblank test such as A2<>"" inside AND, using the correct first cell for the apply range.
Headings are included: start the apply and count ranges at the first data row rather than the heading row.
Similar-looking entries do not match: check for leading or trailing spaces, inconsistent punctuation, and text-versus-number differences.
A rule seems to disappear: reopen Format > Conditional formatting and inspect the rules listed for the selected cells. Confirm the rule’s range and custom formula, and check whether another rule overlaps it.

If your Google Sheets locale uses semicolons as function-argument separators, the formula editor may expect semicolons instead of commas. The examples here use commas, as in the standard English-language formula syntax. If a formula is rejected, check the spreadsheet’s locale and the exact error message rather than changing the reference logic at random.

Conditional formatting versus other ways to find duplicates

Conditional formatting is a good choice when you want duplicates to remain visible in the working sheet and update automatically as values change. It is nondestructive: it does not delete records or rewrite values. A filter can help you inspect or temporarily narrow a list, while a helper column can display a count or a written label that can be sorted and reviewed. Those options may be easier when the result needs to be exported or audited.

Do not confuse highlighting with removing duplicates. Formatting only marks cells; it does not decide which record should be kept. Before deleting records, compare the other fields and determine whether repeated IDs are genuine errors or valid repeated transactions. For a one-time cleanup, Google Sheets also provides Data > Data cleanup > Remove duplicates, but that operation changes the data rather than just its appearance. Make a copy or confirm the selected columns before using a destructive cleanup tool.

Frequently Asked Questions

Can Google Sheets highlight both copies of a duplicate?
Yes. Apply =COUNTIF($A$2:$A$100,A2)>1 to A2:A100. Because each value is counted across the complete list, every occurrence of a repeated value meets the condition.

How do I highlight only the second and later copies?
Use =COUNTIF($A$2:A2,A2)>1 with the rule applied from A2 downward. The counted range grows one row at a time, so the first occurrence is not flagged and later repeats are.

Can I highlight an entire row when one column contains a duplicate?
Yes. Apply the rule to the full table, such as A2:D100, and use =COUNTIF($A$2:$A$100,$A2)>1 when the duplicate key is in column A. The reference to column A is locked so the test stays on the ID as formatting moves across each row.

Does COUNTIF distinguish uppercase from lowercase?
No, COUNTIF is generally case-insensitive in Google Sheets. If uppercase and lowercase values must count as different, use a case-sensitive approach rather than relying on this standard duplicate rule.

Will the rule update when I edit the list?
Yes. Conditional formatting recalculates as values in the relevant ranges change. If you add rows beyond the apply range or counted range, extend those ranges so the new entries are included.

Does conditional formatting remove duplicate values?
No. It only changes how matching cells look. To delete repeated records, use an appropriate cleanup workflow and check which data should be retained first; highlighting alone is safer for reviewing possible duplicates.

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.