Function Problems

Find Unique Rows or Values in Two Excel Columns

Phi Hung VoPhi Hung Vo Nov 18, 2026 871 views

Question details

Find complete rows or individual values that occur exactly once in a two-column dataset.

Find Unique Rows or Values in Two Excel Columns
Product
Microsoft Excel
Device & OS
not provided
Scenario
The user wants to identify unique two-column combinations or values in Microsoft 365, Excel 2021, or an older Excel version.
Observed behavior
Rows or values that occur exactly once should be returned, labeled, or highlighted.
Before you start

Identify the data range and whether you want to compare complete rows or individual cells across both columns. The examples use A2:B13, with headers in row 1; adjust the references to match your data.

Solution 1Recommended

Return rows that appear exactly once with UNIQUE

Use this method in Microsoft 365 or Excel 2021 to generate a separate list of unique two-column combinations.

1
Select an empty cell

Click an empty cell outside A2:B13, such as D2, and leave room below and to the right for the results.

2
Enter the formula

Type =UNIQUE(A2:B13,,TRUE) in D2 and press Enter. Excel spills the rows that appear exactly once into two columns.

Return rows that appear exactly once with UNIQUE
Exactly once versus distinct: The TRUE argument returns only rows that occur once. Omitting it returns one copy of every distinct row, including rows that occur multiple times.
Find unique rows in WPS Spreadsheet

Identify unique two-column rows in WPS Spreadsheet

Use a COUNTIFS helper column in WPS Spreadsheet to label rows that appear exactly once, then filter the results.

  1. 1. Open the workbook: Open WPS Spreadsheet and use Open to select your Excel workbook.
  2. 2. Add a helper column: For data in A2:B13, type Unique row in C1.
  3. 3. Test each row: In C2, enter =IF(COUNTIFS($A$2:$A$13,A2,$B$2:$B$13,B2)=1,"Unique","") and press Enter, then drag the fill handle through C13.
  4. 4. Filter unique rows: Select the table, choose Data > Filter, and use the column C filter to show Unique.
Find unique combinations across two columnsOpen and edit Microsoft Excel XLSX and XLS filesFilter labeled rows in a familiar spreadsheet interfaceUse WPS Office for free
microsoft office alternative - wps office

Frequently Asked Questions

How do I find rows that occur exactly once in Excel?

In Microsoft 365 or Excel 2021, enter =UNIQUE(A2:B13,,TRUE) in an empty cell. The formula returns two-column rows that appear exactly once.

Can I find unique rows without the UNIQUE function?

Yes. In C2, enter =COUNTIFS($A$2:$A$13,A2,$B$2:$B$13,B2)=1 and fill down. TRUE marks a combination that appears once.

How do I compare individual values across both columns?

Select A2:B13 and create a conditional formatting formula rule using =AND(A2<>"",COUNTIF($A$2:$B$13,A2)=1). It highlights nonblank values that occur once anywhere in the range.

Why does UNIQUE return a row that appears more than once?

Without the third argument, UNIQUE returns one copy of each distinct row. Use =UNIQUE(A2:B13,,TRUE) to return only rows that occur exactly once.