Find Unique Rows or Values in Two Excel Columns
Question details
Find complete rows or individual values that occur exactly once in a two-column dataset.

- 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.
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.
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.
Click an empty cell outside A2:B13, such as D2, and leave room below and to the right for the results.
Type =UNIQUE(A2:B13,,TRUE) in D2 and press Enter. Excel spills the rows that appear exactly once into two columns.

Label unique two-column rows with COUNTIFS
Use a helper column to identify rows that occur exactly once, including in older Excel versions.
Highlight individual values that appear once across both columns
Use conditional formatting when you want to compare individual cells across A and B rather than compare complete rows.
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. Open the workbook: Open WPS Spreadsheet and use Open to select your Excel workbook.
- 2. Add a helper column: For data in A2:B13, type Unique row in C1.
- 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. Filter unique rows: Select the table, choose Data > Filter, and use the column C filter to show Unique.

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.




