How to Get Unique Values in Excel (5 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

If you are looking for how to get unique values in Excel, then you are in the right place. Duplication of values in Excel can occur in a single column or across multiple columns and rows in Excel. It is often necessary to eliminate duplication, for correct data analysis and data integration purposes. Other times, one may just want to note recurring values, rather than remove them. There are a few key ways to get rid of duplicate values in Excel or to detect unique values. So, we are going to examine a few of these in this article on how to get unique values in Excel.


How to Get Unique Values in Excel (5 Easy Ways)

High-Performance Liquid Chromatography (HPLC) is a well-known and widely utilized analytical chemistry technique. It allows for the separation, identification, and quantification of compounds in a mixture. In this tutorial, we will be working on a spreadsheet in which different research assistants will be conducting different HPLC columns that can detect different compounds. We will be analyzing the duplications and uniqueness of the columns in different processes.
The source data is shown below, there are 200 rows of data with 5 columns. We are showing a few rows due to the shortage of space.

how to get unique values in excel

Now we’ll show you five diverse methods to get unique values with simple steps.


1. Using Advanced Filter

Excel Advanced filter helps you to extract data based on specified criteria. You can use the Excel Advanced filter to get unique values for certain conditions. Please follow the below steps.

Steps:

  • Firstly, open a blank spreadsheet named Using Advanced Filter.
  • Secondly, go to Data > Sort & Filter > Advanced.

Using Advanced Filter to Get Unique Value in Excel

  • Eventually, an Advanced Filter window will appear.
  • Thirdly, select the Copy to another location
    Fourthly, for the Copy to option, select $B$4 on the Advanced Filter sheet, and tick unique records only as shown below.
  • Fifthly, select the List range, and then navigate to the dataset sheet and select column $B$4:$B$204, which is our Name of University or Research Institute column, as shown below.
  • Sixthly, click OK.

Using Advanced Filter to Get Unique Value in Excel

  • The unique university and research institute names are then extracted from the Using Advanced Filter.

Using Advanced Filter to Get Unique Value in Excel

  • Similarly, you then follow the same process for all the other columns in the original dataset.
  • Edit the Advanced Filter window like the picture below to find the unique value of the HPLC Column.

Eventually, the output is like this.


2. Removing Duplicates to Find Unique Values in Excel

You can also remove duplicate values, using the Remove Duplicates feature from the Data tab. Remember, this feature will remove your data for good.

Steps:

  • Firstly, make a copy of the Original Dataset sheet, since it is always best to keep the source data intact as mentioned, and call this copy, the Removing Duplicates to Get Unique Values.
  • Secondly, select the range and go to Data > Data Tools > Remove Duplicates.

Removing Duplicates to Find the Unique Values

  • Eventually, a Remove Duplicates window will appear.
  • Importantly, make sure the My data has headers checkbox is ticked, and then choose the column that you’d like the duplicates to be removed from or leave all the columns checked.
  • Additionally, if you choose to remove duplication for one particular column in the data set, you’ll get the same results as for the Advanced Filter, unique extracts example above.
  • For example, choose the Name of University or Research Institute as shown below.

Removing Duplicates to Find the Unique Values

  • Eventually, results in a dialog box popping up, showing that based on the Name of University or Research Institute column 171 duplicate values were removed, and 6 unique values remain.

  • This confirms the Advanced Filter results and additionally highlights the number of duplicate values, within this specific column.
  • Removing duplicates and ensuring all the columns are checked as shown below.

  • Consequently, this results in Excel returning an answer that says that there were 3 duplicate values and 197 unique values. This means that there were three rows in the dataset where the values for Name of University or Research Institute column, HPLC Column column, Compounds the HPLC Column can Detect (Supplier) column, Compounds the HPLC Column is actually used to detect (specific lab) column and Research Assistant column were all equal.
  • Additionally, we now want to look at the data in context and find out, outside of the Research Assistant column, how many values are duplicated.

  • Eventually, we get 22 duplicate values found and removed, 178 unique values remain in the dataset as shown below.

  • Finally, click OK and get the unique values.

3. Using Excel Formula

You can also use different formulas to find unique values. EXACT and UNIQUE functions are very handy to use to find unique values.


3.1. Using EXACT Function with Conditional Formatting

We can then use the EXACT function, in conjunction with Conditional Formatting in order to show us where exactly these values in the column are occurring.

Steps:

  • Firstly, highlight the Compounds the HPLC Column is actually used to detect (specific lab) columns as shown below.
  • Secondly, we select the following Home > Styles > Conditional Formatting. Select New Rule from the drop-down options given.

Using EXACT Function with Conditional Formatting

  • Thirdly, in the Format values where this formula is true: text box, enter the following formula:
=EXACT("C8 compounds",$E5)

Using EXACT Function with Conditional Formatting

Importantly, you need to format before clicking OK.

  • Choose a dark blue fill, with white bold text from the options given, as shown below.

  • Fifthly, click OK once done, in order to preview what the formatting will look like.

Finally, click OK to get the output like this.


3.2. Utilizing UNIQUE Function

You can use the UNIQUE function to get unique values. The UNIQUE function mainly finds out the unique value that has been given in command to its syntax within a bunch of datasets.

We suppose need to find the unique value of the Research Assistant column header.

  • Firstly, write the following formula in the H5
=UNIQUE(F5:F204)

Here, F5:F204 is the range of cells of the Research Assistant Column.

Utilizing UNIQUE Function

  • Secondly, press ENTER to get the filtered Research Assistants.


4. Utilizing Built-in Feature

You can also use the built-in features of Excel Tables, in order to detect and remove duplication.

Steps:

  • Firstly, make a copy of the sheet containing the original data.
  • Secondly, with a cell in the data set selected, press CTRL + T on your keyboard or go to Insert > Tables > Table.

Utilizing Built-in Feature to Get Unique Values in Excel

  • Thirdly, using the filtering options on the table also allows for the isolation of certain values. Clicking on the drop-down arrow next to the Name of University or Research Institute column, allows one to select certain universities. Ticking just University ABC isolates all the records for University ABC in the data set as shown below.

Utilizing Built-in Feature to Get Unique Values in Excel

  • Eventually, you’ll get an output like this with the unique names of universities.

  • Additionally, one can also filter a table using more than one column, for example, let’s say one wanted to see only the records in the table from University ABC, which research assistant Jennifer Smith compiled. One would first tick University ABC as in the above example and then click the drop-down arrow next to the Research Assistant column and select Jennifer Smith as shown.

  • Consequently, you will find unique Research Assistants.

Utilizing Built-in Feature to Get Unique Values in Excel


5. Using Slicers with Tables

Slicers provide a quick way to filter tables visually. You can follow the steps below to get unique values using the Slicer.

Steps:

  • In order to insert a slicer, firstly, select a cell in the table, and go to the Design tab in the Table Tools context-sensitive menu > Tools > Insert Slicer.

Using Slicers with Tables to Get Unique Values in Excel

  • Secondly, in the Insert Slicers dialog box, tick the Compounds the HPLC Column can Detect (Supplier) column, and the Compounds the HPLC Column is actually used to detect (specific lab) column as shown below.

  • Eventually, you have inserted the slicers below.

  • Thirdly, selecting C8 compounds from the Compounds the HPLC Column can Detect (Supplier) slicer, filters the table instantly to show only the rows where you have listed C8 compounds, in this column of the below picture.
  • Fourthly, choose C8 compounds again in the Compounds the HPLC Column is actually used to detect (specific lab) slicer that the picture shows. We want to check where the value repeats in order to see which labs are actually following the specifications set by the supplier.

Using Slicers with Tables to Get Unique Values in Excel


How to Find Unique Values from Multiple Columns in Excel

You can find unique values in Excel very easily if you use the UNIQUE function. The UNIQUE function can find the unique value of multiple columns.

  • Suppose you need to find the unique values from columns B and C. Firstly, write the following formula in the E5 cell like this.
=UNIQUE(B5:C204,FALSE,TRUE)

Here, B5:C204 refers to the range of cells Name of Universities or Research Institute and HPLC Columns.

Find Unique Values in Excel Multiple Columns

  • Secondly, press ENTER.

Eventually, you’ll find the output like this.


How to Count Unique Values in Excel

You can count the unique values in columns or rows if you use the COUNTIF function. The COUNTIF function doesn’t show unique values rather it counts the number of them and shows the counted number.

  • Suppose you need to know the number of unique values of Column C. Firstly, write the formula in the G5 cell like this.

=COUNTIF(C5:C204)

How to Count Unique Values in Excel

  • Similarly, after pressing ENTER you’ll get the output as 14.


Download Practice Workbook


Conclusion

Excel provides a wide array of tools to detect recurring values and/or remove duplicate values. It is sometimes necessary when cleaning datasets in Excel for future database integration (which requires removal of redundancy and duplication) to use more than one tool. We don’t always want to remove recurring values instead we just want to count them or note them, in scenarios that we create in Excel as well. Hope this article will help you to find unique values in Excel. Please feel free to comment on the attachment and tell us your tips for removing duplicate values/detecting recurring values and if you use HPLC analysis in your lab.


<< Go Back to Unique Values | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Taryn Nefdt
Taryn Nefdt

Taryn is a Microsoft Certified Professional, who has used Office Applications such as Excel and Access extensively, in her interdisciplinary academic career and work experience. She has a background in biochemistry, Geographical Information Systems (GIS), and biofuels. She enjoys showcasing the functionality of Excel in various disciplines. She has over ten years of experience using Excel and Access to create advanced integrated solutions.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo