HCR – Unique Values
Highlight Cell Rules - Duplicate and Unique Values
With Excel’s preset conditional formatting tool, Highlight Cell Rules, you can alter a range of cells’ appearances according to your own criteria.
One of the condition’s alternatives, Duplicate Values.., can search for both unique and duplicate values.
The Highlight Cell Rules section of the conditional formatting menu looks like this:
Highlight Cell Rule - Duplicate Value Example
When a cell’s value is the same as other cells in the range, the “Duplicate Values…” Highlight Cell Rule will highlight the cell with one of the appearance possibilities.
Any range can be selected as the one to which the Highlight Cell Rule should be applied. It could consist of a few cells, one row, one column, or a mix of several rows, columns, and cells.
Now let’s apply the rule to every value in a cell.
“Duplicate Values…” Emphasize Cell Rule, sequentially:
- Select the range A2:H8
2. Click on the Conditional Formatting icon in the ribbon, from Home menu
3. Select Highlight Cell Rules from the drop-down menu
4. Select Duplicate Values… from the menu
This will cause a dialog box to popup, allowing you to choose the appearance option and value.
5. Choose Duplicate
6. From the dropdown menu, choose “Yellow Fill with Dark Yellow Text” as the appearance choice.
Now, all the cells in the range that have the same value as another cell are highlighted in yellow:
Mew has a stat value of 100 and Ditto has a stat value of 48, so they are both highlighted.
Additionally highlighted are all of the Normal Type values and all of the Dittos.
Note: Manage Rules allows you to remove the Highlight Cell Rules.
Highlight Cell Rule - Unique Value Example
Let’s apply this to the same set of data to see if the “Duplicate Values…” Highlight Cell Rule can also locate and highlight Unique Values:
Any range can be selected as the one to which the Highlight Cell Rule should be applied. It could consist of a few cells, one row, one column, or a mix of several rows, columns, and cells.
Now let’s apply the rule to every value in a cell.
“Duplicate Values…” Emphasize Cell Rule, sequentially:
- Select the range A2:H8
2. Click on the Conditional Formatting icon in the ribbon, from Home menu
3. Select Highlight Cell Rules from the drop-down menu
4. Select Duplicate Values… from the menu
This will open a dialog box where you can specify the value and the appearance option.
5. Select Unique from the dropdown menu
6. Select the appearance option “Green Fill with Dark Green Text” from the dropdown menu
Now, the cells with unique values will be highlighted in green:
Mew and Psychic are highlighted because they only occur once in the range.
Note: The Removing Duplicate Function can be used to eliminate unnecessary duplicates from a table.