We can often find ourselves in a situation where we have a lot of data in Excel, but we want to make sure to extract only the unique values.
One of these examples is when we want to show only unique values in our columns.
We will show how to do this in the text below.
Extract Unique Values with Pivot Table
For our example, we will use a table of banking loans, with names of the clients, loan amount, and duration.
We will look for distinct values in column C (duration in months). The first way in which we can do this is to create a Pivot Table from our data and put our desired column C in the Rows field.
Our Pivot Table will now show only the distinct values for loan duration:
Extract Unique Values with Remove Duplicates
By using Pivot Table, we are not removing any of the data. By using Remove Duplicates, we sadly do.
We will go to our original Loans table, select range C2:C9, and go to Data >> Data Tools >> Remove Duplicates:
When we click on it, a pop-up appears with the warning message:
We can either Expand the selection or Continue with the current selection. We will select the second option and will be prompted to another window:
Since this is the column for which we want to remove the duplicates, we will click OK. Our table now looks like this:
As seen, we did get what we wanted- we only have unique values in column C. However, we can notice that the other related columns were not deleted and that our data has basically changed. For example, Jack had a loan that had a 24-month duration in the first table. Now he has a loan with 60 months duration.
If we wanted to delete all the related columns, we would have to choose to Expand the selection in the first warning message that we got, and Excel would automatically suggest we include columns A and B.
Extract Unique Values with Pivot Table
Now, there is one more, perhaps the easiest and safest way to extract unique values from a single column.
We will go to Data >> Sort & Filter >> Advanced:
After we click on it, a pop-up appears:
In this filter, we will select column C and we will select Unique records only.
When we click OK, we will have only the unique values filtered in our table.
All the other rows with duplicated values in column C will be hidden.