How to highlight unique values in Excel

The easiest way to specify a unique value is to format it, and Microsoft Excel offers two ways to do this.

Excel logo

Photo: iStock / muchomor

One of the most common requests I receive from users is how to identify duplicate and unique values ​​in Microsoft Excel. The easiest way I know of is to apply conditional formatting. In short, a Conditional Formatting Changes the appearance of cells or values ​​based on the true/false conditions you specify. For example, a teacher might set a conditional format to display GPAs less than 75 in red. Doing so will visually alert her to students who are not doing well in her class. In this article, I will show you two ways to highlight unique values ​​using conditional formatting. First, we’ll review the easy way: using a built-in rule that highlights the single value. Next, we’ll use the conditional formula rule that highlights the entire record.

We see: 60 Excel Tips Every User Should Master

I’m using Microsoft 365 on Windows 10 64-bit, but you can use an earlier version. You can work with your own data or download the .xlsx demo file. The browser supports conditional formatting; However, you cannot use the browser to apply the formula rule.

How to highlight the values

Excel has a built-in conditional rule that highlights unique values. You don’t have to come up with a special formula – you just have to run with a few clicks. The hardest part is finding it! Use the paper in Figure ALet’s use this rule to select unique values ​​in a Num column:

  1. Select the values ​​you want to format; In this case it is D3: D16.
  2. Click the Home tab. Then click the Conditional Formatting drop-down menu in the Styles group.
  3. From the dropdown menu, choose Cell Highlighting Rules, then choose Duplicate Values ​​from the resulting submenu (Figure A). Earlier, I said that this rule is hard to find – you might not consider searching for a unique rule under a duplicate rule.
  4. The dropdown on the left defaults to Repeat, but you can choose Unique.
  5. Then choose a preset format from the dropdown menu on the right (Figure B).
  6. When you click OK, Excel highlights the unique values ​​in the range you selected in step 1.

Figure A


Access to the built-in unique value base.

Figure B


Choose the unique rule.

It’s very easy to implement, but as you can see, this method only highlights values, which can be visually tricky. Highlighting the entire record helps the user to find the corresponding values ​​faster.

See: How to Easily Embed Dynamic Dates in a Word Document Using Excel (TechRepublic)

How to highlight rows

There is no built-in rule that will highlight the entire history. Therefore, you will need a formula that returns true when the value is unique and false when the value is a duplicate. To achieve this, we will use the COUNTIF() function of the form:

Contiv (elegant – tidyAndStandards)

where elegant – tidy Identifies the entire data set (log) and Standards Defines a condition, which can be a cell reference, a value, or even an expression. The COUNTIF() function counts the number of cells in elegant – tidy that meet the standards. Now, we don’t want a computation, but we know that the unique value will return the value 1. Let’s try that now:

  1. Enter the following function in cell H3 and copy it to the remaining cells:
    = COUNTIF ($D$3: $D$16, $D3)
  2. as you see in Figure C, this function returns 1, when the corresponding value in column D is unique. We can quickly convert this into a true/false result using the function
    = COUNTIF ($D$3: $D$16, $D3) = 1
  3. figure d The results are shown: the function returns true when the corresponding value is 1 and false when any other value. By the way, you can use the same function to find duplicates simply by changing the equality operator from = to .

Figure C


The function returns 1 when the value is unique.

figure d


Now the function returns true or false, not a numeric value.

The next step is to enter the true/false expression as a conditional:

  1. Select the data range, B3: E16 – you want to highlight the entire row. If you are using a table, Excel will update the range as you add and delete records. The demo file contains an example table.
  2. Click Conditional Formatting in the Styles group and choose New Rule.
  3. At the top of the resulting dialog, click the last option, Use a formula to select the cells to format.
  4. At the bottom, enter the formula
    = COUNTIF ($D$3: $D$16, $D3) = 1
  5. Click Format, click the Fill tab, choose the red color from the panel, and then click OK. Figure E Displays the rule and preview formatting.
  6. Click OK.

Figure E


Enter the expression and choose a format.

as you see in shape and, this rule highlights the entire row – the row is the same as before but it’s easy to highlight all the data that corresponds to the unique value. It’s not hard to highlight a unique value (or iteration for that matter), whether you want to see only the value or the entire corresponding record.

shape and


This conditional format highlights the entire row.

see also