How to View Duplicates in Google Sheets

To view duplicate values in Google Sheets, select your data, open Format > Conditional formatting, and use a custom formula that highlights values appearing more than once. This makes repeats visible without removing or changing your data. You can also add a helper column and filter it when you want a reviewable list. The steps below are for Google Sheets in a web browser.

What You’ll Need

A Google Sheets spreadsheet with the values you want to check, such as names or product IDs.
Permission to edit the sheet if you want to add formatting, a formula, or a helper column. If you only have view access, you can inspect existing highlights but may not be able to create your own.
A clear idea of what counts as a duplicate. The examples below look for repeated values in one column, not matching entire rows.

Step-by-Step

Choose the column to check. Open the sheet and identify the range containing your values. For example, if names are in column A, with a header in A1 and data starting in A2, you will check A2 downward. Avoid including the header unless you specifically want to compare it with the data. If the sheet has several lists, handle each list separately so a value in an unrelated section is not treated as a repeat.
Select the data range. Click the first cell containing a value, then select the cells you want included. For a list in A2 through A500, select A2:A500. You can also select the column range by clicking its letter, but a defined range is often safer because it avoids headers, notes, and unrelated cells. The range you select is where Google Sheets will apply the highlight.
Open conditional formatting. Choose Format > Conditional formatting from the menu. A conditional formatting panel opens on the right. In the panel, check that Apply to range matches your selected cells. If it does not, enter the intended range, such as A2:A500. This check matters because a correct rule applied to the wrong cells can appear to do nothing or color unrelated content.
Choose a custom formula rule. In Format cells if, open the rule list and select Custom formula is. With values in A2:A500, this formula checks whether each nonblank cell occurs more than once in the checked range: =AND(A2<>"",COUNTIF($A$2:$A$500,A2)>1). Enter it in the custom formula box. The dollar signs keep the list range fixed as the rule evaluates each cell, while A2 adjusts to the current row. The AND condition prevents empty cells from being highlighted as duplicates.
Pick a highlight style and save the rule. Choose a fill color that stands out against the sheet without making text hard to read. You can also change the text color or use another available formatting style. Click Done in the conditional formatting panel. Every nonblank value that appears at least twice in the selected range should now be highlighted, including its first occurrence and later repeats.
Review the highlighted cells. Scan the colored values and compare their spelling, spacing, and surrounding details. A duplicate value does not always mean a duplicate record: two customers may share a name, for example, while having different account numbers. If your list contains a unique identifier, check that field before deciding that a record should be removed. This step is about seeing repeats, not automatically judging whether they are errors.
Optional: use a helper column to label repeats. A helper column lets you filter the list and read a label instead of relying only on color. Suppose the values to check are in A2:A500 and column B is available. Put a heading such as Duplicate status in B1. With the values in A2:A500 and the first data row at row 2, enter this in B2: =IF(A2="","",IF(COUNTIF($A$2:A2,A2)>1,"Duplicate","First occurrence")). This labels only the second and later appearances as Duplicate; the first is labeled First occurrence. Copy or fill the formula down through B500. It leaves blank source cells blank, so they do not clutter the results.
Optional: filter to show only later repeats. Select the header row and the data, then choose Data > Create a filter. Open the filter control in the helper-column header, clear the current selections, and select only Duplicate. The visible rows will be the second and subsequent occurrences. To see the first appearance too, keep First occurrence selected as well, or rely on the conditional formatting rule, which highlights every matching value. When finished, use the filter control to show all values or choose Data > Remove filter.

Common Mistakes

Including the heading in the range: If the header is included, it is counted like any other value. Keep the selected range and the formula range aligned with the first data row. For a list starting in A2, both should start at row 2.

Using a formula that checks only later occurrences: A running COUNTIF helper formula labels later appearances, not the first one. That is useful for finding extra entries, but it differs from conditional formatting that highlights every instance of a repeated value. Decide which view you need before choosing the method.

Expecting a match to mean a bad record: Repeated names, dates, or categories can be legitimate. A match in a single column only establishes that the value repeats in that column. Verify other fields, such as an ID or email address, before treating whole rows as duplicates.

Assuming visually similar text is identical: Extra spaces, different punctuation, or slightly different spelling can make entries distinct. Google Sheets COUNTIF comparisons are not case-sensitive, so entries like Alex and ALEX are treated as matches, but extra spaces can prevent a match.

Applying a rule to the wrong range: If the formula refers to A2 but the applied range starts on row 1 or another column, the relative reference may evaluate against the wrong cell. Match the formula’s starting cell to the top-left cell of the apply range.

Tips

Use a distinct but gentle color. A pale fill is often easier to scan than a saturated one, especially on a long list. If the sheet already uses color to represent status or priority, choose a style that will not be confused with those meanings.

Keep an original copy before cleanup. Viewing duplicates is non-destructive, but deleting rows later is not. Make a copy of the sheet or duplicate the tab before removing records, and verify which entry should be kept. This is particularly important when the list contains customer, inventory, or payment information.

Use a helper column for a sortable review. Conditional formatting is quick for a visual scan. A helper column is more useful when you need to filter, sort, or share a list of repeat entries with someone else. You can keep both methods: the labels aid filtering while the color makes matches easy to spot.

Consider what field should define a duplicate. If two rows count as duplicates only when both a name and date match, checking the name column alone will show too many results. A single-column rule cannot confirm a whole-row match. For a practical review, compare the fields that define a unique record and make the criteria explicit to anyone using the sheet.

Remember that formatting is a view, not a data change. Conditional formatting does not remove values or alter their contents. If you copy the range elsewhere, the rule may not travel in the way you expect, so check the destination sheet’s formatting and rule range.

Troubleshooting

Nothing is highlighted: Reopen Format > Conditional formatting and inspect both Apply to range and the formula. The example formula assumes the first data cell is A2. If your range begins in C5, update the formula’s relative cell reference to C5 and use the correct fixed range for your data. Also check that matching values are genuinely identical apart from capitalization, which COUNTIF ignores.

Blank cells are highlighted: Confirm that you used the AND version of the formula, which excludes empty cells, and that the range does not include unwanted blank rows or header cells. If the cells appear blank but contain spaces, they are not empty to the spreadsheet. Remove or clean those spaces if appropriate, then review the result.

Unexpected values are highlighted: The rule checks the value in one column across the full specified range. If two different records share a name, both names will be highlighted. Check an additional identifier before deciding what to do. Also confirm the range does not extend into another list farther down the same column.

The helper column shows an error or the wrong label: Make sure the formula was entered in the first data row of the helper column, that its references point to the source column, and that you filled it down. If you put the formula in row 3 but its references still start at row 2, the first rows may be classified incorrectly. If your spreadsheet’s locale expects a different formula separator, Google Sheets may ask you to adjust the separators in the formula.

The filter hides rows you need: Open the helper-column filter menu and check which labels are selected. Choose all values to restore the full list, or remove the filter from the Data menu. Filtering hides rows temporarily; it does not delete them. If other people are using the same sheet, coordinate before changing a shared filter view.

Frequently Asked Questions

Does conditional formatting delete duplicate values?
No. It only changes how matching cells look. To remove records, use a separate cleanup process and check the data carefully first.

Does the highlight show every copy or just later copies?
The conditional formatting rule shown here highlights every nonblank occurrence of a repeated value, including the first. The helper-column example labels only later occurrences as Duplicate.

Can I check duplicates in more than one column?
You can apply separate rules to separate columns, but a rule for one column does not establish that entire rows match. If a record is defined by multiple fields, compare those fields together using a method designed for that combination.

Will the rule include new entries I add later?
Only if the new cells fall inside the rule’s apply-to range. Set a range with room for future entries, or revisit the conditional formatting panel and extend the range when your list grows.

Is the process different in Excel?
Excel has its own conditional formatting menus and formula behavior. These instructions use Google Sheets menu paths and are intended for Sheets in a web browser, not 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.