How to get unique values / remove duplicates from column in Excel

How can I remove the duplicates in excel to get the separate column with unique values ?

posted on 29.11.2020 at 14:43

Hi,

You can use several methods to get unique values in Excel. Because of differences between them, you have to decide which one to use.

The first one is quite quick and simple. Go to Data and in the Sort & Filter section click on Advanced:

excel get distinct values

Then select Copy to another location, check Unique records only and fill the ranges:

excel get unique values remove duplicates

When you make a change in input data, to refresh the unique values you have to use Advanced filter again.

The second method is based on pivot table and is useful when you regularly need to refresh the values. Go to Insert and create a Pivot table from input data:

excel get distinct values remove duplicates pivot table

excel get distinct values remove duplicates pivot table

Drag the input data field into Rows section:

excel unique values remove duplicates pivot table

You should get a simple pivot table with unique values. If there is a Grand Total row at the bottom, you can remove it by clicking on it with right mouse button and choosing Remove Grand Total. When you make a change in input data, to refresh the pivot table, you have to go to Data and click on Refresh All:

excel refresh pivot table data

The third method is probably the most comfortable. You can get unique values also by using UNIQUE function, which is available only in the recent Office 365. The formula can look like:
=UNIQUE(A2:A15)

posted on 13.12.2020 at 14:37

Tim