Work With Distinct / Unique Values in Excel & Google Sheets

Written by

Mel Jenkins

Reviewed by

Laura Tsitlidze

Last updated on September 10, 2023

This tutorial demonstrates how to work with distinct values in Excel and Google Sheets.

unique values intro

When you have a sheet of data in Excel that contains repetitive data, there are several methods you can use to isolate distinct values (or distinct rows) from your data. This tutorial covers five methods:

  1. Formulas
  2. Pivot Table
  3. Conditional Formatting
  4. Advanced Filter
  5. VBA

It also shows how to count and select distinct values. Click here to jump to the Google Sheets section.

Formulas to Find Distinct Values

The UNIQUE Function

To extract unique values out of a list of values in Excel that contains duplicates, you can use the new UNIQUE Function. This function is only available in Excel 365. Consider the following list of values:

unique values list

The list in Column B contains repetitive names of cities.

  1. To extract the unique values from this list, click in D3 and type in the formula:

unique values list values

  1. When you press ENTER, the formula populates the rows below with the unique values from Column B. (Any repeated cities are only shown once.)

unique values grayed formula

Notice that, if you move your pointer to cell D4, the formula in D4 is grayed out – you are not able to change it. This is because the list is automatically filled in by the formula contained in cell D3 – you can only edit or delete the formula if you have D3 selected. It’s an array formula.

Formulas Prior to Excel 365

If you’re not using Excel 365, there is no UNIQUE Function available. Showing unique values from a list with duplicates requires more work and a formula that uses four Excel functions: IFERROR, INDEX, MATCH, and COUNTIF.

Use the formula below, where the original list in in Column B and the unique list is in Column D.


unique values index match formula

  • The COUNTIF Function counts values that fit specified criteria. This section of the formula – COUNTIF(D$2:D2,B$3:B$10) –returns a zero for values that aren’t found earlier in the list. In other words, a zero result identifies each new item. Any other result means that the item is a duplicate and should be ignored.
  • The MATCH Function . This section – MATCH(0,COUNTIF(D$2:D2,B$3:B$10),0) – returns the row number for each value with zero returned by COUNTIF. For example, the output for the MATCH portion of the formula in D6 is 7, because Row 6 is a duplicate.
  • The INDEX Function . This section – INDEX(B$3:B$10,MATCH(0,COUNTIF(D$2:D2,B$3:B$10),0)) – returns the value in the row number returned by MATCH. So, D6 shows the value in Row 7 of the original list (Austin).
  • The IFERROR Function returns a blank if the formula extends to a cell outside the list range. This ensures you don’t get the #N/A error.

Pivot Table to Find Distinct Values

  1. As with other methods in working with unique records, select the range of cells you want to obtain unique records from.
  2. Then, in the Ribbon, go to Insert > Tables > Pivot Table.

unique values pivot ribbon

  1. Select a location in the Existing Worksheet to place your pivot table.

unique values pivot 2

  1. In the PivotTable Fields list, drag the field down to the Rows area of the pivot table.

unique values pivot cities

Pivot tables show only unique values.

Tip: Try using some shortcuts when you’re working with pivot tables.

Conditional Formatting to Find Distinct Values

You can use conditional formatting to find unique values in the data, and then you can filter by color to show just these values.

  1. As with other methods in working with unique records, select the range of cells you want to obtain unique records from.
  2. Then in the Ribbon, go to Home > Styles > Conditional Formatting > New Rule.

unique values new rule

  1. Choose Format only unique or duplicate values, and then in the Format all drop down, choose unique. Finally, set the format to be applied to unique cells.

unique values create rule

  1. Click OK to apply the rule.

unique values applied rule

  1. Now, in the Ribbon, go to Home > Editing > Sort & Filter > Filter and choose Filter by Color from the filter drop down.

unique values filter by color

This filters out duplicate values, showing only unique ones in your worksheet.

unique values result

Advanced Filter to Find Distinct Values

You can also use an advanced filter to find unique values.

  1. Select any cell in the list you wish to filter, and then in the Ribbon, go to Data > Sort & Filter > Advanced.

unique values sort advanced

  1. In the Advanced Filter dialog box, choose Copy to another location under Action. Then, type in or select the location you want to Copy to (i.e., where to place the filtered data). Finally, tick Unique records only.

unique values advanced filter

  1. Click OK to filter for distinct records only.

unique values advanced filter

VBA to Find Distinct Values

Finally, you can also find unique values using the RemoveDuplicates function in VBA.

Sub RemoveDups()
   Range("B2:B16").RemoveDuplicates Columns:=1, Header:=xlYes
End Sub

Distinct Values in Column vs Table

As shown at the beginning of this tutorial, the UNIQUE Function finds distinct values in a list (single column). It also works to find distinct rows in a table (multiple columns). Look at the table below. It’s a list of flight numbers and the corresponding origin and destination for each. Some rows are repeated.

unique values multiple columns

To extract the unique rows, use the whole dataset range (B3:D9) as the array argument.


unique values multiple columns result

The UNIQUE Function populates a new set of unique data; there are just three columns instead of one.

Count Distinct Values

Once you have extracted your distinct values, you can add a count to tell you how many distinct values or rows there are.

  1. First, in a blank cell, type in the beginning of the ROWS Function:
  2. Then, highlight the extracted unique rows (here, D3:D12).
  3. When you press ENTER, the formula changes, showing a # sign at the end of the cell address.

unique values rows count

The formula returns a count of the unique values (rows) in the array.

Select Distinct Values

If you have used conditional formatting to filter by color to show distinct values, you can then select just these values using the Go To feature in Excel.

  1. Highlight the list of data you filtered by color.
  2. In the Ribbon, go to Home > Editing > Find & Replace > Go To Special…

unique values go to special

  1. Choose Visible Cells only in the Go To Special dialog box.

unique values visible cells-only

There are six distinct cities, and now the first instance of each is selected.

Distinct Values in Google Sheets

Find Unique Values

Google Sheets does not have VBA, but some of the methods used above (UNIQUE Function, alternate formulas, and pivot tables) can also be used in Google Sheets to obtain unique values.

  • You can use the UNIQUE Function to extract values from a single column.

unique values gs

  • You can also use the UNIQUE Function to extract unique rows.

unique values gs rows

  • Unique values can also be extracted using a formula containing the INDEX, MATCH, and COUNTIF Functions.

unique values gs formulas

  • If you create a pivot table in Google Sheets, it automatically shows only unique values.

unique values gs pivot table

Count Distinct Values

Use the ROWS Function to count distinct values in Google Sheets.

unique values gs count unique

Select Distinct Values

Use conditional formatting in Google Sheets to highlight – and then select – distinct values.

  1. Highlight the list of data in which you wish to select the distinct values, and then in the Menu, go to Format > Conditional formatting.

unique values gs menu conditional formatting

  1. Under Format rules, choose Custom formula is, and then type in the formula:
  2. Adjust the Formatting style, and click Done.

unique values gs conditional formatting

  1. This applies formatting to distinct values in the list according to the custom format set in Step 3.

unique values gs select unique

  1. You can then easily select the highlighted values by clicking on the first highlighted value and then, holding down the CTRL key, click the rest of the highlighted values, one by one.

unique values gs select

AI Formula Generator

Try for Free

See all How-To Articles