Excel: Identify Unique Values

To identify unique values in an Excel table, follow the steps below.

This article is the third post in the Excel basic data analytic series, which starts here.

The steps for identifying unique values are similar to identifying duplicates. The first difference shows up in step 3 below.

These procedures are written for Excel 2013, but the same steps should also work in Excel 2010 and 2016 (Office 365), but the screenshots might be a little different.

Identifying Unique Values

To identify unique values (columns) in an Excel table:

1. Highlight all the cells that you want to analyze.
   In Example 1 below, I selected all cells in the table (to enlarge an example, click it). To highlight the table, click in cell A1, and while holding the mouse button, drag down to cell D11. Release the mouse button.
   
2. On the Home tab, click the following: Conditional Formatting, Highlight Cell Rules, Duplicate Values.
   Yes, to identify unique values, you have to select ‘Duplicate Values’ first. Just trust me. See Example 1 below.
   
   

   Example 1

    

3. The Duplicate Values dialog box appears, and the duplicates in each column are highlighted immediately (left side of Example 2).
   Under Format cells that contain, select Unique as shown on the right side of Example 2. The table will change accordingly. Click OK.
   
   

   Example 2

    

4. Notice the following in Example 2 above:
   – The red cells on the left side are duplicate values, and the red cells on the right side are unique values.
   – On the right side, the headings (Name, Position, Hourly Rate, Start Date) are identified as unique values (and they are).
5. To list all unique values, you have to filter the values. This is a 3-step process.
6. First, position the cursor in row 1; when the cursor changes to an arrow, click once to highlight the entire row.
   See Example 3 below.
   
   

   Example 3

    

7. Second, apply the filter arrows to the columns: in the Data tab, click Filter.
   See Example 4 below (the filter arrows don’t appear until after this step–see Example 5  further down).
   
   

   Example 4

8. Third, determine which column to filter. Depending on the column you choose you will get different results (more on that later).
   In this example, let’s filter by column A. So in column A, click the filter arrow, Filter by Color, and then the light red color (which identifies unique values) under Filter by Cell Color.
   See Example 5 below.
   
   

   Example 5

    

9. The result is shown in Example 6 below. Notice the following:
   – In column A and D, all the values are unique (red)
   – In columns B, no unique values are identified (white).
   – In column C, the only unique hourly rate is 48 (red).
   

   Example 6

10. In step 8, I noted that filtering different columns will provide different results. If in step 8, you filtered the data by column C, you would see the results shown in Example 7 below. Because column C has only 1 unique value, only that row is displayed when you filter by column C.
    

    Example 7

    So be aware that you’ll get different results based on which column you filter.
    

Next

The next post in this series is Excel: Text To Columns

Previous posts in this series:

Excel: Remove Duplicates

Excel: Identify Duplicates

Advertisement