How to Find and Count Duplicate Values in Excel
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.

- 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.
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.
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.
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).
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.
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.
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.

Summarize Duplicate Counts Using a PivotTable
A PivotTable is the best choice if you want to generate a clean, consolidated report that shows each unique value alongside its total occurrence count.
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. Open your data file: Launch WPS Office and open your spreadsheet document in the WPS Spreadsheet application.
- 2. Highlight duplicates visually: Select your data, navigate to the Home tab, and click Conditional Formatting > Highlight Cells Rules > Duplicate Values.
- 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. 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.

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.




