Formula Errors

How to Count or Identify First Duplicate Occurrences in Excel

Maira Mehtab Sep 20, 2026 869 views

Question details

The user wants to extract, identify, or count the first occurrence of values that appear multiple times in a specific Excel list.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Analyzing lists of data with duplicated entries to isolate exactly one instance of each repeated value (from 2 up to 100 times).
Observed behavior: Requires a reliable formula to filter these occurrences and a way to troubleshoot the #NAME? error if the function is not recognized by the software.

Before you start

Before proceeding, check if your version of Excel supports dynamic array functions like FILTER. This feature is typically available in Microsoft 365, Excel 2021, and newer versions.

Solution 1Recommended

Use the FILTER and COUNTIF Functions

Combine the FILTER and COUNTIF functions to dynamically extract values that appear more than once in your dataset.

By nesting a COUNTIF condition inside a FILTER function, you can instruct Excel to only return values that have a frequency count greater than one. To isolate just the first instance of each duplicate, you can further wrap this in the UNIQUE function.

Select an empty cell

Click on a blank cell where you want the resulting list of duplicates to spill over.

Enter the core formula

Type the formula =FILTER(A1:A8, COUNTIF(A1:A8, A1:A8)>1) into the formula bar, replacing A1:A8 with your actual data range. This returns all duplicated items.

Isolate the first occurrences

To show only the first instance of each duplicate, wrap the previous formula in the UNIQUE function by typing =UNIQUE(FILTER(A1:A8, COUNTIF(A1:A8, A1:A8)>1)) and press Enter.

Troubleshooting #NAME? Error: If Excel returns a #NAME? error, verify that your version supports the FILTER function. Additionally, ensure that your function separators (commas vs. semicolons) match your operating system's regional language settings.

Efficient Data Analysis

Easily Manage Duplicates with WPS Spreadsheet

WPS Spreadsheet provides powerful functions and built-in tools to handle duplicate data quickly. You can use modern array formulas like FILTER to extract data or use the dedicated 'Highlight Duplicates' tool for instant visual identification.

1. Open your file: Launch WPS Spreadsheet and open the document containing your dataset.
2. Apply the formula: Click on an empty cell and enter the formula =UNIQUE(FILTER(A1:A8, COUNTIF(A1:A8, A1:A8)>1)) to instantly extract the first occurrence of each duplicate.
3. Use visual highlighting (Optional): If you prefer visual cues, highlight your data range, navigate to the Data tab on the top ribbon, and click 'Highlight Duplicates'.

Seamlessly compatible with advanced Microsoft Excel array formulas like UNIQUE and FILTER.Built-in 'Highlight Duplicates' tool under the Data tab for quick visual checks without writing formulas.Lightweight software with a familiar user interface, ensuring a zero-learning-curve migration.Completely free to use for everyday data analysis and spreadsheet tasks.

Frequently Asked Questions

Why does my FILTER formula return a #NAME? error?

The #NAME? error typically occurs if your version of Excel does not support the FILTER function (it requires Microsoft 365 or Excel 2021+), or if there is a typo in the function name itself.

How can I count the total number of duplicate values instead of listing them?

You can count the resulting unique duplicate values by wrapping the formula in the ROWS function. For example: =ROWS(UNIQUE(FILTER(A1:A8, COUNTIF(A1:A8, A1:A8)>1))).

Can I use Conditional Formatting to find duplicates without formulas?

Yes. Select your data range, navigate to Home > Conditional Formatting > Highlight Cells Rules, and select Duplicate Values to visually highlight all repeated entries.

Does this formula work for both text and numbers?

Yes, the COUNTIF and FILTER combination evaluates the contents of the cell regardless of data type, meaning it works identically for text strings, numbers, and dates.

Excel & Data Problems