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
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)