Copilot in Excel Issues

How to Find and Count Duplicate Values in Excel

Khadija KhanKhadija Khan Nov 10, 2026 869 views

Question details

The user needs to find out how many times duplicate values occur in an Excel dataset, as the AI assistant only applies conditional formatting without providing a numerical count.

How to Find and Count Duplicate Values in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
Cleaning and analyzing a dataset where the exact frequency of repeating entries is required for reporting or data validation.
Observed behavior
Copilot in Excel successfully highlights duplicate values using conditional formatting but does not automatically generate a count of how many times each duplicate value appears.
Before you start

Ensure your data is organized in a clear, continuous column without blank rows or merged cells so that formulas and PivotTables calculate the occurrences accurately.

Solution 1Recommended

Use the COUNTIF Formula to Count Occurrences

The COUNTIF function is the most reliable and straightforward method to calculate exactly how many times a specific value appears in a selected range.

While automated tools like Copilot are great for visual formatting, standard Excel functions give you the exact numerical data needed for accurate duplicate tracking. You can use COUNTIF to get the exact count, or modify it to flag duplicates with a True/False statement.

1
Select an adjacent cell

Click on an empty cell in a new column adjacent to the first data point you want to check (for example, cell B2 if your data starts in A2).

2
Enter the COUNTIF formula

Type the formula =COUNTIF($A$2:$A$100, A2), making sure to replace the range ($A$2:$A$100) with your actual absolute data range.

3
Apply formula to the column

Press Enter to see the count for the first cell, then double-click or drag the fill handle in the bottom-right corner of the cell to copy the formula down the entire column.

4
Flag duplicates as True/False (Optional)

If you only want to flag whether a cell is a duplicate rather than knowing the exact count, use the formula =COUNTIF($A$2:$A$100, A2)>1 instead.

Use the COUNTIF Formula to Count Occurrences
Absolute References: Using the dollar signs ($) in your range ensures that the reference block stays locked when you drag the formula down the column.
Powerful Spreadsheet Data Analysis

Easily Find and Count Duplicates with WPS Spreadsheet

WPS Spreadsheet provides a robust and intuitive environment to manage, highlight, and accurately count duplicate values. With familiar formulas and streamlined PivotTables, you can handle complex data analysis effortlessly.

  1. 1. Open your data file: Launch WPS Office and open your spreadsheet document in the WPS Spreadsheet application.
  2. 2. Highlight duplicates visually: Select your data, navigate to the Home tab, and click Conditional Formatting > Highlight Cells Rules > Duplicate Values.
  3. 3. Count occurrences with formulas: In an empty adjacent column, type =COUNTIF($A$2:$A$100, A2) and press Enter, then drag the fill handle down to calculate occurrences for all rows.
  4. 4. Generate a summary report: Go to the Insert tab, select PivotTable, and drag your target column into both the Rows and Values areas to instantly generate a summarized count.
100% compatibility with Microsoft Excel (.xlsx) files and standard COUNTIF formulas.Built-in conditional formatting to quickly identify and highlight duplicate values.Advanced PivotTable tools to seamlessly summarize and count large datasets.Lightweight performance ensures smooth calculation even with massive rows of data.
microsoft office alternative - wps office

Frequently Asked Questions

Why does Copilot in Excel highlight duplicates but not count them?

Copilot in Excel currently handles duplicate-related queries by applying conditional formatting rules. This visual approach highlights the data but does not output a mathematical count. For exact frequency numbers, you must rely on standard Excel tools like the COUNTIF formula or PivotTables.

Can I use the UNIQUE function to help count duplicates?

Yes. If you have Excel 365 or WPS Spreadsheet, you can extract a list of unique values by typing =UNIQUE(A2:A100) in an empty column. Once your unique list is generated, you can use the COUNTIF formula next to it to count how many times each unique item appears in your original dataset.

Is it possible to create a custom LAMBDA function for duplicates?

Yes, advanced users can create custom LAMBDA functions to filter and count duplicates dynamically. However, defining LAMBDA functions can be complex. For most everyday tasks, using COUNTIF or generating a PivotTable remains the most efficient and accessible method.

How do I remove the duplicate values after I have counted them?

Once you are done analyzing your duplicate counts, select your data range, go to the Data tab on the ribbon, and click 'Remove Duplicates'. Choose the specific columns you want to evaluate, and Excel will delete the repeating rows, leaving only unique records.