Show Only First Occurrences with COUNTIF in Excel
Question details
The user wants a formula that displays a result for the first occurrence of each value and leaves later occurrences blank.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A column contains repeated values, and the user wants to identify each value's first appearance in a separate column.
- Observed behavior
- The desired result is a marker for each first occurrence and a blank result for duplicates.
Check which row contains the first data value and choose an empty column for the results. The steps below assume a header in A1 and data starting in A2.
Mark First Occurrences with an Expanding COUNTIF Range
Use this formula to display 1 for the first occurrence of each nonblank value and leave duplicates blank.
The range $A$2:A2 expands as the formula is filled down. COUNTIF checks how many times the current value has appeared from the first data row through the current row.
Select B2 and enter =IF(A2="","",IF(COUNTIF($A$2:A2,A2)=1,1,"")), then press Enter.
Select B2 and drag the fill handle down alongside the data in column A. The first occurrence of each nonblank value displays 1; later occurrences display blank results.
If your data starts in a different row, change both references to A2 and the fixed range start $A$2 in the formula to match that row before filling down.

Identify First Occurrences in WPS Spreadsheets
Use the same IF and COUNTIF formula in WPS Spreadsheets to mark first occurrences while leaving duplicate results blank.
- 1. Open the spreadsheet: Open WPS Spreadsheets and use Open to select your workbook.
- 2. Enter the formula: If your data starts in A2, select B2 and enter =IF(A2="","",IF(COUNTIF($A$2:A2,A2)=1,1,"")), then press Enter.
- 3. Fill down: Select B2 and drag its fill handle down to the last row of data.
- 4. Save the workbook: Choose Menu > Save or Save As and select the Excel XLSX format if you need to share the workbook with Excel users.

Frequently Asked Questions
Can I display the first value instead of the number 1?
Yes. In B2, use =IF(A2="","",IF(COUNTIF($A$2:A2,A2)=1,A2,"")) and fill it down. The first occurrence displays the value from column A.
Does the formula remove or prevent duplicate entries?
No. It only changes what appears in the formula column; the original values remain in column A. To block new duplicate entries, use Excel's Data > Data Validation instead.
Why does the formula use dollar signs in $A$2?
The dollar signs keep the start of the COUNTIF range fixed at A2 as you fill the formula down. The other end of the range moves with each row.
Will the formula work when new rows are added to an Excel table?
You can convert the data to a table using Insert > Table and enter the formula in a table column. Excel can automatically fill a calculated column for new rows; check the formula after adding a row if automatic filling is disabled.




