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 keyways 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.
Download Practice Workbook
5 Easy Ways to Get Unique Values in Excel
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.
Now we’ll show you five diverse methods to get unique values with simple steps.
1. Using Advanced Filter
- Firstly, open a blank spreadsheet named Using Advanced Filter.
- Secondly, go to Data > Sort & Filter > Advanced.
- 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 using 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.
- The unique university and research institute names are then extracted from the Using Advanced Filter
- 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.
- 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.
- 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.
- 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
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.
- 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.
- Thirdly, in the Format values where this formula is true: text box, enter the following formula:
Importantly, you need to format before clicking OK.
- So, fourthly, choose
- 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 which 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
Here, F5:F204 is the range of cells of Research Assistant Column.
- 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.
- 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.
- 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, that 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.
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.
- 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.
- 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.
Find Unique Values with 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.
Here, B5:C204 refers to the range of cells Name of Universities or Research Institute and HPLC 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 from 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.
- Similarly, after pressing ENTER you’ll get the output as 14.
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.