How to Mark Duplicates in Google Sheets with Color

To mark repeated values in Google Sheets, select the cells you want to check, open Format > Conditional formatting, and use a custom formula to apply a color to duplicates. This highlights matching entries without deleting or changing your data, and the highlighting updates when values change.

What You’ll Need

A Google account and a spreadsheet open in Google Sheets in a web browser.
A clear idea of what counts as a duplicate: the same value in one column, or the same combination of values across several columns.
A header row, if your table has one, so you can start the rule on the first row of data.

You do not need an add-on or a script. Conditional formatting is built into Sheets. The instructions below assume row 1 contains column headings and the data starts in row 2; adjust the row numbers if your sheet is laid out differently.

Step-by-Step

Choose the cells to check. Click the first data cell in the column you want to inspect, then drag to select the data range. For example, select A2:A500 to check IDs in column A. You can also use a larger range, such as A2:A, if new rows will be added over time. Avoid including the header unless you want it checked too.
Open the conditional formatting panel. With the range selected, choose Format > Conditional formatting from the Google Sheets menu. A Conditional format rules panel opens on the right. If the range shown under Apply to range is not the one you meant to check, edit it before creating the rule.
Choose a custom formula. In the panel, open the Format cells if menu and select Custom formula is. The formula should compare each cell in the selected range against the rest of the data. It is important that the row number in the formula corresponds to the first row of the selected range.

For a range starting at A2, with values in A2:A500, enter =AND(A2<>"",COUNTIF($A$2:$A$500,A2)>1). If A4 and A9 both contain Maple, the formula is TRUE for both cells, so both can be highlighted. The first part prevents empty cells from being treated as duplicates. The dollar signs keep the comparison range fixed while Sheets evaluates each row.
Pick a visible format. Under Formatting style, select a fill color that stands out from the sheet, or choose a text color or other available style. A pale red or orange fill is often easy to notice without making the table hard to read. The preview shows what the format will look like.
Save the rule. Click Done at the bottom of the panel. Repeated values in the selected range should now show the chosen style. Click a highlighted cell and inspect the formula bar if you want to confirm that the values match exactly.
Decide whether to mark every copy or only later repeats. The formula above marks every occurrence of a duplicated value, including the first one. To mark only the second and later appearances, use a running comparison instead. With values in A2:A500 and the rule applied starting at A2, use =AND(A2<>"",COUNTIF($A$2:A2,A2)>1). For example, if Pear appears in A2 and A4, A2 is not highlighted and A4 is. This can make it easier to identify which row is the extra entry.
Check whether whole rows are duplicates. If a record is only a duplicate when several fields match, select the rows or columns you want the rule to format and compare each field. For data in columns A, B, and C, starting on row 2, use =AND(COUNTA($A2:$C2)=3,COUNTIFS($A$2:$A$500,$A2,$B$2:$B$500,$B2,$C$2:$C$500,$C2)>1). This marks rows where the combination of A, B, and C occurs more than once, while skipping rows with a blank field. For instance, two records with the same name but different IDs will not match unless all three fields match. Set Apply to range to the rows or cells you want colored, such as A2:C500.
Review and manage the rule. Look over a few marked and unmarked examples. To change the range, formula, or color later, select a cell in the affected area and choose Format > Conditional formatting. The panel lists rules for that range; click a rule to edit it or use the trash icon to remove it.

Common Mistakes

Starting the formula on the wrong row: If Apply to range begins at A2, the formula should refer to A2, not A1 or A3. A mismatch can shift the check and highlight the wrong cells. When you change the starting row, update the relative cell reference in the formula too.

Including the heading: If the header says “Email” and the same word appears in the data, a rule that includes row 1 could mark the heading as a duplicate. Start the range below the header unless there is a reason to include it.

Expecting near-matches to count: A value such as “Sam Lee” is not the same text as “Sam Lee ” with a trailing space. Likewise, a full name is different from an email address, even if both refer to the same person. Clean or standardize the data when you need to catch inconsistent entries, rather than expecting a basic duplicate rule to infer that they mean the same thing.

Checking just one column when the record depends on several: Two customers may share a surname, or two orders may share a date. If duplicates mean matching multiple fields, use a combined-fields rule instead of coloring every repeated value in a single column.

Using a color that is already meaningful: If the sheet uses red fill to indicate overdue work, red duplicate formatting can be confusing. Choose a distinct style and make sure the text remains readable.

Tips

Use a bounded range such as A2:A500 when you know the expected data size. It makes the rule easier to understand and avoids checking a much larger area than necessary. If people regularly append rows, an open-ended range such as A2:A can be more convenient; keep the blank-cell check in the formula so unused cells are not colored.

For a quick visual review, sort or filter the relevant column after marking duplicates. Sorting brings matching values together, but it does not replace the rule: conditional formatting is useful when the data changes or new records arrive. If the order of rows matters, avoid sorting the live table without considering how it will affect related data.

You can use more than one rule to distinguish different cases. For example, one rule can color every occurrence, while another can use a different style for later repeats. If rules overlap, review their order and formatting in the Conditional format rules panel, and keep the styles visually distinct.

Conditional formatting only changes how cells look. It does not remove, merge, or permanently label records. If you need a reviewable text label, add a helper column. With values in A2:A500 and the helper formula entered in B2 and filled down, use =IF(A2="","",IF(COUNTIF($A$2:A2,A2)>1,"Duplicate","First or unique")). If Pear appears first in A2 and again in A4, B2 shows First or unique and B4 shows Duplicate. This labels later occurrences, rather than every member of a repeated group.

Troubleshooting

Nothing is highlighted: Check that the Apply to range box covers the data and that its first row matches the row used in the formula. Confirm that Custom formula is is selected and that the rule is saved. Also test a pair of cells by comparing their displayed contents; they may look alike but contain different spaces, punctuation, or characters.

Blank cells are highlighted: Add a blank check such as AND(A2<>"",…) around the duplicate test, or limit the applied range to populated rows. In a custom formula, use the first cell of the selected range in the blank check. If you copy the example, retain its opening equals sign and balanced parentheses.

Too many cells are highlighted: You may be comparing only one column when you intended a full-row match. Use a multi-column comparison, or narrow the selected range to the specific field that defines a duplicate. If all blanks are colored, use the blank guard described above.

Some apparent duplicates are missed: Look for leading or trailing spaces, different punctuation, or inconsistent formatting such as phone numbers entered with and without a country code. Sheets’ COUNTIF matching is not case-sensitive, so capitalization alone generally does not distinguish entries. Clean the source values first if those differences should be ignored; be cautious about changing data in place and make a copy if the original values need to be preserved.

The color does not update as expected: Reopen Format > Conditional formatting and confirm the rule is attached to the correct sheet range. Check for another conditional format that applies a competing style. If rows were inserted or copied, verify that the rule still covers them; expand Apply to range if necessary.

Frequently Asked Questions

Does this delete duplicate rows?
No. Conditional formatting only marks cells visually. To remove copies, review the data first and use the appropriate duplicate-removal option separately; deleting rows can permanently discard information.

Can Google Sheets mark duplicates in more than one column?
Yes. Use a custom formula that checks the relevant columns together, such as the COUNTIFS example above, and apply it to the cells or rows you want highlighted.

Will the rule include new rows?
It will if the Apply to range includes them, for example A2:A. If you chose a fixed range such as A2:A500, records below row 500 will not be covered until you expand the range.

Does it treat uppercase and lowercase as different?
COUNTIF matching is generally case-insensitive, so values that differ only by capitalization are usually counted as duplicates. Differences in spaces or punctuation can still prevent a match.

Can I mark only the extra copies?
Yes. Use the running COUNTIF version shown in the steps. It leaves the first appearance unmarked and formats each later occurrence as a duplicate.

Can I use this in Excel too?
The general idea and many formulas are similar, but the menu and conditional-formatting interface differ. These steps and menu paths are for Google Sheets in a web browser.

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.