How to Find Unique Values from Multiple Columns in Excel (5 Easy Ways)

We have a record of student IDs and names. We want to extract the unique values.

Method 1 – Extract Unique Values from Multiple Columns with Array Formula

Case 1 – Using the UNIQUE Function

Precaution: The UNIQUE function is only available in Office 365.

Syntax of UNIQUE Function:


  • Takes three arguments, one range of cells called an array, and two Boolean values called by_col and exactly_once.
  • Returns the unique values from the array.
  • If by_col is set to TRUE, it searches for the unique values by the columns of the This argument is optional. The default is TRUE.
  • If exactly_once is set to TRUE, returns the values which appear only once in the array. This argument is optional. The default is FALSE.

We want to extract the unique values from both the First Names (Column C) and the Last Names(Column D).

  • Select a result cell and insert the following formula there.


Apply UNIQUE function to find unique values in Excel

  • We have got the Unique Names in two different columns.

We have inserted by_col as FALSE, so it did not search along the columns We have also inserted exactly_once as TRUE, so it did return the values that appear only once.

Case 2 – Combining CONCATENATE and UNIQUE Functions

Let’s get a complete list of full names.

First Formula:


Alternative Formula:


Combination of UNIQUE & CONCATENATE functions to find unique values in Excel

Case 3 – Using UNIQUE, CONCATENATE, and FILTER Functions to Extract Unique Values Based on Criteria

Let’s extract the unique names of the students whose IDs are greater than 150.

Precaution: The FILTER function is only available in Office 365.

Syntax of FILTER Function:


  • Takes three arguments. One range of cells called an array, one boolean condition called include, and one value called
  • Returns the values from the array which meet the condition specified by the
  • If any value of the array does not fulfill the condition specified by the include, it returns the value if_empty for it. Setting if_empty is optional. It is “no result” by default.
  • Use the following formula:

=UNIQUE(FILTER(C5:D16,B5:B16>150,"no result"),FALSE,TRUE)

Combination of UNIQUE & FILTER functions to find unique values in Excel

  • If you want to extract the full unique names in one cell, use this formula:

=UNIQUE(FILTER(CONCATENATE(C5:C16," ",D5:D16),B5:B16>150,"no result"),FALSE,TRUE)

Combination of UNIQUE, FILTER & CONCATENATE functions to find unique values in Excel

Method 2 – Highlight Duplicate Values Using Conditional Formatting

We have three columns, but all with the same type of data. We’ll highlight the duplicates.


  • Select the dataset.
  • Go to Home and select Conditional Formatting, then choose Highlight Cells Rules and select Duplicate Values.

Apply Conditional Formatting to find unique values in Excel

  • You will get a small box called Duplicate Values.
  • Select any color in the second box to highlight the duplicate values.
  • Click OK.

Method 3 – Extract Unique Values from an Excel Column Using a Non-Array Formula


  • Select any cell.
  • Insert the following formula-

=IFERROR(IFERROR(LOOKUP(2, 1/(COUNTIF($F$4:F4,$B$5:$B$11)=0), $B$5:$B$11), LOOKUP(2, 1/(COUNTIF($F$4:F4, $C$5:$C$9)=0), $C$5:$C$9)),LOOKUP(2, 1/(COUNTIF($F$4:F4, $D$5:$D$12)=0), $D$5:$D$12))

  • Drag the Fill Handle and you will find the unique names.

Combination of IFERROR, COUNTIF & LOOKUP functions to find unique values in Excel

Method 4 – Extract a Unique Distinct List from Two or More Columns Using a Pivot Table


  • Press Alt + D. then press P. You will get the PivotTable and PivotChart Wizard opened.
  • Select Multiple consolidation ranges and PivotTable.

  • Click Next. You will move to Step 2a of 3.
  • Select Create a single page field for me.

  • Click Next. You will go to Step 2b.
  • In the Range box, select the range of your cells with an empty column on the left. We have selected cells B5 to D12.
  • Click Add. Your selected cells will be added to the All ranges box.

Apply Pivot Table to find unique values in Excel

  • Click Next. You will move to Step 3.
  • In the Existing worksheet box, select the cell where you want the Pivot Table. We put $F$4.

  • Click Finish. You will get a Pivot Table.
  • In the Choose fields to add to report part, unmark Row, Column, Value, Page 1.

  • Select Value. You will get the unique names in the Pivot Table.

Method 5 – Use VBA Code to Find Unique Values


  • Press Alt + F11 on your workbook to open the VBA window.
  • Go to the Insert tab in the VBA toolbar and choose Module.

  • You will a get new Module window.
  • Insert the following code:
Sub Uniquedata()
Dim rng As Range
Dim InputRng As Range, OutRng As Range
Set dt = CreateObject("Scripting.Dictionary")
xTitleId = "Select Range"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Output to (single cell):", xTitleId, Type:=8)
For Each rng In InputRng
    If rng.Value <> "" Then
        dt(rng.Value) = ""
    End If
OutRng.Range("A1").Resize(dt.Count) = Application.WorksheetFunction.Transpose(dt.Keys)
End Sub

Apply VBA Macro to find unique values in Excel

  • Save the file as an Excel Macros Enabled Workbook.
  • Go back to the workbook.
  • Press Alt + F8.
  • You will get the Macro box.
  • Select the name of the Macro and then click on Run. The name of this Macro is Uniquedata.
  • Enter the range of your data in the Range box.

  • Click on OK. You will get another input box.
  • Enter the first cell where you want the unique names. We put cell F5.

  • Cclick OK. You will get unique names from your data set.

Download the Practice Workbook

<< Go Back to Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF