ThisCalculator

Everyday

Multiple Cells Same Value? Find Duplicates in Excel

Updated · Standard formula, shown below

The calculator for this page is switched off while it is checked. The formula is below, and the worked example in the article shows how to use it by hand.

This guide explains how to find, highlight, and handle multiple cells that contain the same value in Excel and Google Sheets. You’ll learn the quickest built-in methods, the formulas behind them, and how to avoid common mistakes like false positives or missed duplicates.

Formula

The calculator above simply returns the number you entered as the count. It is a placeholder to illustrate that “how many cells have the same value” depends entirely on your data—there is no universal formula. In Excel, the actual counting is done with COUNTIF:

=COUNTIF(range, criteria)

  • range – the group of cells you want to check (e.g., A2:A100).
  • criteria – the value you’re looking for (e.g., 5, “apple”, or a cell reference like B1).

For example, =COUNTIF(A2:A100, 5) returns the number of cells in A2:A100 that contain the number 5. If you want to count cells that are identical to another cell, use =COUNTIF(A2:A100, B1).

Worked Example

Using the default values from the calculator above: you have 10 cells, and you want to know how many contain the value 5. In a real spreadsheet, suppose your data is in cells A1:A10:

  1. Click an empty cell, say C1.
  2. Type =COUNTIF(A1:A10, 5).
  3. Press Enter. Excel counts every cell in A1:A10 that equals 5.

If three cells contain 5, the result is 3. The calculator above simply shows the number of cells you entered (10) because it has no actual data to inspect—it’s a placeholder to demonstrate the concept. In practice, you replace the placeholder with your real range and criteria.

Why You Might Need to Find Duplicate Values

Duplicate values are common in real-world data. You might have a list of customer emails, product IDs, or survey responses where the same entry appears more than once. Knowing which values repeat helps you:

  • Clean up mailing lists before sending a campaign.
  • Identify repeated transactions or entries in financial logs.
  • Check for data entry errors where someone typed the same thing twice.
  • Merge duplicate records in a database.

Excel and Google Sheets both offer built-in tools for this, so you don’t need to write complex formulas unless you want more control.

How to Find Duplicates with Conditional Formatting

The fastest way to visually spot duplicates is with conditional formatting. This works in both Excel and Google Sheets.

  1. Select the range of cells you want to check (e.g., A2:A100).
  2. In Excel, go to Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values. In Google Sheets, go to Format → Conditional formatting, then under “Format rules” choose Custom formula is and enter =COUNTIF(A$2:A$100, A2)>1.
  3. Choose a formatting style (like a red fill) and click OK.

Every cell whose value appears more than once in the selected range gets highlighted. This is a visual check—it doesn’t remove the duplicates, just marks them.

Using COUNTIF to Count or Flag Duplicates

If you need a number or a yes/no flag rather than just colors, use COUNTIF in a helper column.

  • To count how many times each value appears, put this in B2 and copy down: =COUNTIF(A$2:A$100, A2). The result is the total count for that value across the whole range.
  • To flag duplicates (i.e., any occurrence after the first), use: =IF(COUNTIF(A$2:A2, A2)>1, "Duplicate", "Unique"). This marks the second and later occurrences as “Duplicate”.

This approach is useful when you want to filter or sort by the flag later.

Removing Duplicates: Built-in Tool vs. Manual

Once you’ve found duplicates, you may want to delete them. Excel has a dedicated feature: Data → Remove Duplicates. Google Sheets has a similar option under Data → Data cleanup → Remove duplicates.

Important: these tools remove entire rows that contain duplicate values in the selected columns. They keep the first occurrence and delete the rest. If you only want to remove duplicate values from one column while keeping the row intact, you need to use a different method—like copying unique values to a new location.

To extract a list of unique values manually, you can use the Advanced Filter in Excel (Data → Advanced) or the UNIQUE function in Excel 365 and Google Sheets. For example, =UNIQUE(A2:A100) returns a list of all distinct values in that range.

Common Mistakes and Misconceptions

Many people make these errors when working with duplicate values:

  • Counting blanks as duplicates – COUNTIF will count empty cells if your criteria is an empty string or if you use a range that includes blanks. Use =COUNTIF(range, "") only if you actually want blanks.
  • Case sensitivity – By default, COUNTIF is not case-sensitive. “Apple” and “apple” are treated as the same. If you need case-sensitive matching, use SUMPRODUCT(--EXACT(range, "Apple")).
  • Leading/trailing spaces – “Apple ” and “Apple” look the same but are different to Excel. Use the TRIM function to clean data first.
  • Numbers stored as text – If some cells contain numbers and others contain text versions of the same number, COUNTIF may not match them. Format all cells consistently.

Frequently Asked Questions

Q: How do I find duplicates in two different columns?
A: Use COUNTIFS with multiple ranges. For example, to count rows where column A and column B both have the same value as in row 2, use =COUNTIFS(A$2:A$100, A2, B$2:B$100, B2). This counts rows that match on both columns.

Q: Does conditional formatting highlight the first occurrence too?
A: Yes, the default “Duplicate Values” rule highlights every cell that has a duplicate, including the first occurrence. If you want to highlight only the second and later ones, use a custom formula like =COUNTIF(A$2:A2, A2)>1 in Excel’s conditional formatting.

Q: Why does COUNTIF return 0 when I know there are duplicates?
A: The most common reasons are leading/trailing spaces, numbers stored as text, or the criteria not matching exactly. Use =TRIM(A2) to clean cells, and check that your criteria has no extra spaces. Also, ensure the range is correct and doesn’t start at the wrong row.

Q: Can I find duplicates without a formula?
A: Yes. Use the built-in “Remove Duplicates” tool (Excel) or “Data cleanup” → “Remove duplicates” (Google Sheets). This will delete duplicate rows, but it doesn’t show you which ones were removed before deleting. For a visual check without deleting, use conditional formatting.

Q: How do I count how many unique values are in a list?
A: Use =SUMPRODUCT(1/COUNTIF(range, range)) for older Excel, or the simpler =COUNTA(UNIQUE(range)) in Excel 365 and Google Sheets. The first formula can have issues with blank cells, so exclude them or use the UNIQUE function.

Related

Formula used
in n | Number of cells | - | 10 | 1
in v | Value to search | - | 5 | 0
out count | Cells with that value | - | n | 0

Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.