Function Problems

Show Only the First Occurrence of Each Value in Excel

Steve KSteve K Nov 18, 2026 870 views

Question details

Display the first occurrence of each value in a separate column while leaving duplicates and blank cells empty.

Show Only the First Occurrence of Each Value in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet contains values in one column, and the user wants a formula-based list showing only their first occurrences.
Observed behavior
The result column should show each value the first time it appears and display an empty result for later occurrences and blank source cells.
Before you start

Identify the source column and its first data row, and make sure an adjacent result column is available. The example below assumes the data starts in A1 and the results will appear in column B.

Solution 1Recommended

Use IF and COUNTIF to Show First Occurrences in Excel

Use this formula when you want to show first occurrences without deleting or changing the original data.

COUNTIF uses an expanding range from A1 to the current row. The formula returns a value only when its count is 1, and returns an empty string for duplicates or blank source cells.

1
Select the result cell

Open the worksheet in Excel and click cell B1 next to the first source value in A1.

2
Enter the formula

Enter =IF(A1="","",IF(COUNTIF($A$1:A1,A1)=1,A1,"")) in B1 and press Enter.

3
Fill the formula down

Select B1 and drag the fill handle down alongside the source data. Column B will display the first occurrence of each value and leave later occurrences and blank rows visually empty.

Use IF and COUNTIF to Show First Occurrences in Excel
Adjust the starting row: If your data starts in A2, enter the formula in B2 and change the references to =IF(A2="","",IF(COUNTIF($A$2:A2,A2)=1,A2,"")) before filling down. COUNTIF is not case-sensitive.
Use WPS Spreadsheet

Show First Occurrences with a Formula in WPS Spreadsheet

Use the same IF and COUNTIF formula in WPS Spreadsheet to show first occurrences while keeping the original column unchanged.

  1. 1. Open the workbook: Open WPS Spreadsheet and use Open to select your workbook.
  2. 2. Select the result cell: Click B1, assuming the source values begin in A1.
  3. 3. Enter the formula: Type =IF(A1="","",IF(COUNTIF($A$1:A1,A1)=1,A1,"")) in B1 and press Enter.
  4. 4. Fill down: Drag the fill handle from B1 down to the last row of source data.
Show first occurrences without deleting duplicate source values.Use familiar IF and COUNTIF formulas.Open and save Microsoft Excel XLSX and XLS files.Work with a familiar, easy-to-use spreadsheet interface.
microsoft office alternative - wps office

Frequently Asked Questions

Does this formula remove duplicate values from the original column?

No. It displays results in a separate column and leaves the source values unchanged.

How do I use the formula when my data starts in A2?

Enter =IF(A2="","",IF(COUNTIF($A$2:A2,A2)=1,A2,"")) in B2, then drag the fill handle down.

Are uppercase and lowercase values treated as duplicates?

Yes. COUNTIF is not case-sensitive, so values such as Apple and apple are treated as matches.

Why do blank rows remain empty in the result column?

The first IF test checks whether the source cell is blank and returns an empty string before COUNTIF evaluates the value.