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 ?

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:



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



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:



Drag the input data field into Rows section:



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:



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)