Function Problems

Show Unique Values with Blank Rows in Excel

Rana GarciaRana Garcia Nov 18, 2026 869 views

Question details

Create a list that displays each value at its first occurrence and leaves subsequent occurrences blank while retaining the original row positions.

Show Unique Values with Blank Rows in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A column contains repeated values, and the user wants a matching list with one visible value per group and blank cells for repeats.
Observed behavior
For a source list such as 1, 1, 2, 2, the desired output is 1, blank, 2, blank.
Before you start

Identify the source range or the top-left cell of the spilled source list, such as A2. Choose an empty output column with enough room for the results; the dynamic formula requires an Excel version that supports LET, UNIQUE, XMATCH, MAP, and LAMBDA.

Solution 1Recommended

Use a dynamic formula with a spilled source range

Use this formula when the source list spills from A2 and the output should resize with it.

The formula keeps the source row positions and displays each distinct value only at its first occurrence. If a value appears again in a later group, that later occurrence is also blank.

1
Check the source spill

Select A2 and confirm that it contains the formula producing the spilled list. The reference A2# represents that entire spill.

2
Enter the output formula

In an empty cell outside the source spill, such as B2, enter =LET(data,A2#,posUnique,XMATCH(UNIQUE(data),data),MAP(SEQUENCE(ROWS(data)),LAMBDA(n,IF(ISNUMBER(XMATCH(n,posUnique)),INDEX(data,n),"")))).

3
Check the result

Press Enter and check the cells below B2. The formula spills one result per source row and updates as the A2 spill changes.

Use a dynamic formula with a spilled source range
Using a fixed range: Replace A2# with $A$2:$A$15 to use a fixed source range. Keep the output cells empty so Excel can spill the results.
Work with repeated values in WPS Spreadsheets

Show first occurrences in WPS Spreadsheets

Use IF and COUNTIF in WPS Spreadsheets to display each value once and leave its repeated rows blank. Fill the formula down alongside your source list.

  1. 1. Open your workbook: Open WPS Office, select Spreadsheets, and open the workbook containing your list.
  2. 2. Enter the formula: If the source starts in A2, select B2 and enter =IF(A2="","",IF(COUNTIF($A$2:A2,A2)=1,A2,""))).
  3. 3. Fill the output column: Drag the fill handle from B2 down to the last source row. Fill it farther down if you add more source rows.
Keep repeated-value rows in place while displaying only first occurrences.Open and edit Microsoft Excel XLSX and XLS files.Use familiar spreadsheet formulas and a fill handle.Work with a free, lightweight Office alternative.
microsoft office alternative - wps office

Frequently Asked Questions

Can I use A2# instead of a fixed range?

Yes. In the dynamic formula, set data to A2# when A2 is the top-left cell of the source spill. The output will resize with that spill.

Why does Excel show a #SPILL! error?

Check the cells below the output formula for existing content or other obstructions and clear them. The dynamic formula needs an empty cell for each result.

What happens if the same value appears in a later group?

The formulas above show only its first occurrence in the entire list. To show a value at the start of every consecutive group instead, enter =IF(A2="","",IF(ROW()=2,A2,IF(A2=A1,"",A2))) in B2 and fill it down.

Do these formulas remove duplicate rows?

No. They retain one output cell for each source row and return an empty string for repeated values; they do not delete or hide the source rows.