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 ?
29 Nov 2020 at 02:43 PM
0pnshow more
Find us on Facebook AnswertabsFind us on Facebook Answertabs

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

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:

13 Dec 2020 at 02:37 PM
0pnshow more
share on facebookshare on twitter
2021 AnswerTabsTermsContact us