# Work With Distinct / Unique Values in Excel & Google Sheets

Written by

Reviewed by

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

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:

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

In this Article

## 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:

The list in Column B contains repetitive names of cities.

- To extract the unique values from this list, click in D3 and type in the formula:
=UNIQUE(B3:B16)

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

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.

=IFERROR(INDEX(B$3:B$10,MATCH(0,COUNTIF(D$2:D2,B$3:B$10),0)),"")

- 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

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

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

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

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.

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

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

- Click
**OK**to apply the rule.

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

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

## Advanced Filter to Find Distinct Values

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

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

- 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**.

- Click
**OK**to filter for distinct records only.

## 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.

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

=UNIQUE(B3:D9)

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.

- First, in a blank cell, type in the beginning of the ROWS Function:
=ROWS(

- Then, highlight the extracted unique rows (here, D3:D12).
- When you press
**ENTER**, the formula changes, showing a**#**sign at the end of the cell address.=ROWS(D3#)

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.

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

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

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**.

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

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

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

### Count Distinct Values

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

### Select Distinct Values

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

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

- Under
**Format rules**, choose**Custom formula is**, and then type in the formula:=COUNTIF(B3:B18,B3)<2

- Adjust the
**Formatting style**, and click**Done**.

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

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