Excel Guides

How to Filter Columns for Unique Values in Excel

There are a few different ways that you can filter columns for unique values in Excel. One way is to use the Data > Filter menu option. This will bring up a dialog box where you can select the column that you want to filter on and then choose to only show unique values. Another way to do this is to use the =UNIQUE() function. This function takes a range as an argument and will return an array of the unique values in that range. You can then use this array in another formula or use it directly in a cell.

If you have a lot of data, then it might be better to use a macro to filter the data. This way you can automate the process and don't have to manually select the column and choose the option to only show unique values. The following macro will do this for you:

Sub FilterUnique()
    Dim rng As Range
    Dim col As Variant
    Dim lastRow As Long

    'Get the range from the user
    Set rng = Application.InputBox("Select a range", "Get Range", Type:=8)

    'Get the column number from the user
    col = Application.InputBox("Enter the column number", "Get Column Number", Type:=1)

    'Find the last row in the range
    lastRow = rng.Rows(rng.Rows.Count).Row

    'Apply the filter
    rng.AutoFilter Field:=col, Criteria1:="<>"

    'Copy the visible cells to a new location
    rng.SpecialCells(xlCellTypeVisible).Copy

    'Remove the filter
    rng.AutoFilter

    'Paste the visible cells back into place
    rng.PasteSpecial xlPasteAll

    'Delete the empty rows that were created by filtering
    Rows(lastRow + 1 & ":" & lastRow + 100).Delete Shift:=xlUp    
End Sub

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.