Excel List of Unique Values

The are many methods you can use to remove duplicates and keep only unique values. In this lesson I’ll show you 4 ways, so you can choose the one which is the best for your needs.

Remove duplicates button

Look at the following example. I have a list of random numbers, which I generated using the RANDBETWEEN function.

Select ranges and go to Data >> Data Tools >> Remove Duplicates.

When a new window appears, click OK.

You will get information about the number of cells removed, and the number of unique values.

This is the result.

Filter out duplicates

Let’s say that you don’t want to remove values, just hide those values that are duplicates. To do this, we will have to filter them out. In order to do so, first select all the values in the example. Now, when you have the cells selected, go to Data >> Sort & Filter >> Advanced.

Here, you have a few options. Let’s select the first radio button to filter the list, and check Unique records only. Click OK.

Now, you have the same unique values, but this time they are filtered out, instead of removed. If you select these cells and press Ctrl + C, only unique values will be copied.

In order to show all values, even those that are hidden, you can’t right-click and choose Unhide from the contextual menu because it won’t work here, instead, use the cleat button located at Data >> Sort & Filter >> Clear.

Remove values with an Excel formula

In this example, we will use a COUNTIF function. It counts the number of cells inside a range that meet the given criteria.

We will use here an expanding reference. When the formula is copied down, the reference will expand to include more rows in the unique list. So the first occurrence in the list will return – 1, second – 2, third – 3, and so on. This means, that values that returned 1 are unique values.

Remove values with VBA

If you want to know how the VBA code looks for removing duplicates, you can record a macro. But if you don’t want to do this, here is the function.

Header:=xlNo means that we don’t have a header in our example.