Find Duplicates in Excel and Google Sheets by Formula

In Excel, select the cells and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values; in Google Sheets, use Format > Conditional formatting and a COUNTIF rule. To check repeated records or remove duplicates, use a formula or the built-in duplicate-removal command in the app you are using.

What Counts as a Duplicate?

A duplicate can mean a repeated value in one column or a repeated record made up of several columns. The distinction matters. If a customer list contains the same email address twice, the email may be the key you want to check. If an order table has the same product in many rows, those repeated product names are normal; you may instead want to find rows where order number, product, and date all match.

Before marking or deleting anything, decide which columns define a duplicate and whether you want to flag every matching entry or only later occurrences. Conditional formatting is useful for a visual review. A formula can make the rule explicit, while Remove Duplicates deletes entries from the selected data. These methods do different jobs, so a highlight is not the same as cleanup.

Highlight Repeated Values in Excel

In Microsoft Excel for Microsoft 365 on Windows, select the cells to check, then go to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Choose a formatting style and select OK. Excel highlights values that occur more than once in the selected range. This is a convenient way to scan a column without changing its contents.

For example, if email addresses are in A2:A100, select A2:A100 before applying the rule. Selecting only the relevant data cells helps avoid highlighting a header or unrelated values elsewhere on the sheet. The rule is conditional formatting: it changes how matching cells look, not the stored values. If the data changes, the formatting can update to reflect the new results.

On Excel for Mac, the same Duplicate Values rule is available through Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values, though dialog appearance can vary by release. Excel for the web also provides conditional formatting, but its menus and available options may differ slightly from the Windows desktop app.

Highlight Repeated Values in Google Sheets

In Google Sheets in a web browser, select the range, then choose Format > Conditional formatting. In the Conditional format rules panel, set Format cells if to Custom formula is, enter a COUNTIF formula, and choose a formatting style. If the values are in A2:A100, apply the rule to A2:A100 and use this formula:

With values in A2:A100 and the conditional-formatting range set to A2:A100, use =COUNTIF($A$2:$A$100,A2)>1. It highlights every non-unique value, including the first instance and later repeats. The dollar signs keep the checked range fixed as the rule evaluates each row, while A2 changes to A3, A4, and so on.

To avoid marking empty cells as duplicates, use a condition that first checks whether the current cell is blank. With values in A2:A100 and the same applied range, use =AND(A2<>"",COUNTIF($A$2:$A$100,A2)>1). Google Sheets uses the same basic COUNTIF approach as Excel, but its conditional-formatting controls are under Format rather than Excel’s Home ribbon.

Use a Formula to Label Duplicates

A helper column can label duplicates, which is handy when you need to filter, sort, count, or report on them rather than simply color them. In Excel or Google Sheets, put the formula beside the first data row and fill it down. With the values to check in A2:A100 and the formula entered in B2, use =IF(COUNTIF($A$2:$A$100,A2)>1,"Duplicate","Unique"). For a value appearing twice, both matching rows show Duplicate; a value appearing once shows Unique.

Sometimes you only want to mark the second and subsequent occurrences, leaving the first as the original. With values in A2:A100 and the formula in B2 copied downward, use =IF(COUNTIF($A$2:A2,A2)>1,"Repeat","First"). The range expands one row at a time, so the first appearance is counted once and each later appearance is identified as a repeat. This is useful when reviewing which entries could be removed while retaining the earliest one.

For an example, if A2:A5 contains north, south, north, east, the second formula labels the rows First, First, Repeat, First. It does not delete anything. You can filter the helper column for Repeat, inspect those records, and then decide what to do.

Check Duplicate Records Across Multiple Columns

To identify repeated rows, define the combination of fields that makes a record unique. Suppose names are in column A and dates of birth are in column B. If both values must match for a row to count as a duplicate, a COUNTIF on column A alone is insufficient; two people may share a name but have different dates.

With names in A2:A100, dates in B2:B100, and the formula in C2 copied down, use =IF(COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)>1,"Duplicate row","Unique row"). COUNTIFS tests both conditions together, so the pair must recur. Add more range-and-criterion pairs if additional columns, such as city or account number, are part of the record’s identity. Keep each paired range the same size.

In Google Sheets, the same COUNTIFS formula can be used in a cell or as the basis for conditional formatting. For full-row highlighting, apply a custom formula to the rows you want to format and anchor the tested columns appropriately. A helper column is often easier to inspect first, especially when the data includes headers, blank rows, or many criteria.

Remove Duplicates Without Losing the Wrong Data

In Excel, make a copy of the data or workbook first. Select the data range, then choose Data > Remove Duplicates. Confirm whether the range has headers, select the columns that define a duplicate, and choose OK. If you select only one column, Excel compares that column; selecting several columns means it looks for rows with matching values across the selected columns. Excel retains one occurrence and removes the other matching rows from the selected range.

In Google Sheets, select the data, then choose Data > Data cleanup > Remove duplicates. Confirm whether the data has a header row and which columns to compare, then run the cleanup. Review the results message and inspect the remaining rows. As with Excel, the selected columns determine what counts as a duplicate; do not select just a name column if records with the same name but different details must remain separate.

Removal changes the data, unlike highlighting or a helper formula. Keep an original copy when the rows matter, and check that the selection includes the complete table so that deleting a duplicate does not leave related columns misaligned. If you have already run a removal and immediately notice a mistake, undo may restore the prior state; it is safer not to rely on that as your only backup. Google Sheets also has version history, and Excel files may have recovery options depending on where they are stored.

Common Matching Problems and How to Avoid Them

A formula may miss an apparent duplicate because the underlying values are not actually the same. Extra spaces, inconsistent punctuation, different date representations, or a number stored as text can complicate a comparison. For instance, an email address with a trailing space may look identical on screen but behave differently in some checks. Inspect suspicious cells and standardize the data before removing rows.

For a simple text cleanup, create a helper column. With the text to clean in A2, use =TRIM(A2) in B2 and fill down. TRIM removes leading and trailing spaces and reduces repeated internal spaces to single spaces. Compare or clean the resulting values, but preserve the source column until you have confirmed the output. TRIM does not resolve every issue, such as inconsistent punctuation or different spellings.

COUNTIF and COUNTIFS comparisons are generally not case-sensitive, so entries such as ABC and abc may be treated as a match. If capitalization carries meaning in your data, do not assume the standard duplicate rule will distinguish it. Also decide how blanks should be treated: multiple empty cells are not necessarily meaningful duplicate records, which is why a not-blank condition can be important for a single-column check.

Another frequent mistake is sorting or deleting based on only one field when a record spans several columns. A matching company name does not prove two invoice rows are identical. Confirm the intended key—perhaps invoice ID alone, or invoice ID plus line number—before using Remove Duplicates. When uncertain, highlight first and review examples before deleting.

Choose the Right Method for Your Task

Use conditional formatting when you want to spot matches visually while preserving all data.
Use COUNTIF or COUNTIFS in a helper column when you need labels, a filterable result, or checks across multiple fields.
Use Remove Duplicates when you have verified the matching columns and actually want repeated records removed.
Use a unique-value list when you want a separate list of distinct entries, not a diagnosis of which original rows repeat.

In Excel for Microsoft 365, UNIQUE can return distinct values as a separate spilling list. With source values in A2:A100, enter =UNIQUE(A2:A100) in an empty cell. The result contains one copy of each distinct value, but it does not label the original duplicates or delete them. UNIQUE is available in Excel 2021 and Microsoft 365. Google Sheets also has a UNIQUE function; its output is similarly a separate list, and it leaves the source range unchanged. For either app, check that the destination cells are empty so the returned list has room to expand.

Frequently Asked Questions

How do I find duplicates in Excel without deleting them?
Use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values to highlight matches, or use COUNTIF in a helper column. Both approaches leave the original data in place.

How do I find duplicates in Google Sheets?
Select the range and choose Format > Conditional formatting. Set the rule to Custom formula is, enter a COUNTIF rule, and choose a format. A helper column with COUNTIF is another option when you want to filter or label results.

Can I find duplicate rows instead of duplicate cells?
Yes. Compare the columns that together identify a record, using COUNTIFS in a helper column or selecting those columns in the duplicate-removal dialog. A match in only one field does not necessarily mean the entire row is duplicated.

Does Remove Duplicates keep the first entry?
Excel’s Remove Duplicates keeps one occurrence and removes matching rows from the selected range. Google Sheets also removes repeated rows based on the selected comparison columns. Make a backup and verify the selected fields before running either command.

Why are duplicates not being highlighted?
Check that the rule covers the full data range and that the formula’s row reference starts on the first row of that range. Look for extra spaces, inconsistent values, and blanks, and remember that COUNTIF comparisons do not distinguish capitalization.

Can I list unique values without changing my data?
Yes. Excel 2021 and Microsoft 365 support UNIQUE, and Google Sheets also provides UNIQUE. It creates a separate distinct-value list rather than marking or removing repeated entries in the source data.

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.