Function Problems

Show Only First Occurrences with COUNTIF in Excel

Olivia MillerOlivia Miller Nov 18, 2026 869 views

Question details

The user wants a formula that displays a result for the first occurrence of each value and leaves later occurrences blank.

Show Only First Occurrences with COUNTIF in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the formula

Select B2 and enter =IF(A2="","",IF(COUNTIF($A$2:A2,A2)=1,1,"")), then press Enter.

2
Fill the formula down

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.

3
Adjust the starting row if needed

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.

Mark First Occurrences with an Expanding COUNTIF Range
Display the value instead of 1: To show the first value itself, replace the result 1 after the COUNTIF comparison with A2: =IF(A2="","",IF(COUNTIF($A$2:A2,A2)=1,A2,"")). The formula does not remove duplicates from column A.
WPS Spreadsheets

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. 1. Open the spreadsheet: Open WPS Spreadsheets and use Open to select your workbook.
  2. 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. 3. Fill down: Select B2 and drag its fill handle down to the last row of data.
  4. 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.
Mark first occurrences without changing the source valuesOpen and save Microsoft Excel XLSX filesFill formulas down a column using the fill handleWork in a familiar spreadsheet interface
microsoft office alternative - wps office

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.