How to Highlight Unique Values in Excel (Easiest Way in 2024)

In this article, we will show you how to highlight unique values in Excel. Simply follow the steps below.

Highlight Unique Values in Excel

Follow the steps below on how to highlight unique values in Excel.

1. Select the Data Range to Analyze

Click and drag to highlight the range of cells containing the data you want to analyze. This step ensures that Excel knows where to look for unique values. In the example below, we selected the range A3:J7.

2. Navigate to the Home Tab in Excel

Go to the “Home” tab in the Excel ribbon at the top of the screen. Find the “Conditional Formatting” button in the Styles group. Click this button to open a menu that offers several formatting rules for your data.

3. Choose the Highlight Cell Rules Option

Hover your cursor over “Highlight Cell Rules” in the Conditional Formatting menu. This action will display a sub-menu with different rule options for highlighting cells.

4. Select the Duplicate Values Option

Click on “Duplicate Values” in the sub-menu. This option allows you to create rules to highlight cells based on whether they contain duplicate or unique values.

5. Set the Rule to Highlight Unique Values

In the Duplicate Values dialog box, you will see two drop-down menus. Change the first menu from “Duplicate” to “Unique.” In the second menu, choose a highlighting style, such as a specific color, or click “Custom Format” to create your own style.

6. Apply the Unique Values Highlighting Rule

After setting the rule, click “OK” to apply it. Excel will now automatically highlight all unique values within the selected range according to the formatting style you chose.

7. Verify the Highlighted Unique Values

Review your data to make sure the unique values are highlighted correctly. If you need to make any changes, you can revisit the Conditional Formatting menu and adjust the rule settings as necessary.

We hope you now have a better understanding of how to highlight unique values in Excel. If you enjoyed this article, you might also like our article on ways to highlight empty cells in Excel or our article on how to highlight expired dates in Excel.