If you wish the duplicate values in your Excel spreadsheet to stand out, you can do so, and you have the option to choose between two conditional formatting rules.
This article, How to highlight duplicate values in Excel, will show you two easy ways you can apply conditional formatting to duplicate values or the row that contains a duplicate value. First, I will show you the easy built-in rule that formats duplicate values. Then, we need to apply a conditional format rule that highlights the entire record.
How to highlight duplicate values in Excel?
- Select the values you want to format.
- Click the Home tab, then, click the Conditional Formatting dropdown, this is situated in the Styles group.
- From the dropdown, choose Highlight Cells Rules, then select Duplicate Values from the resulting submenu.
- Now, select a preset format from the dropdown to the right.
- After you click on OK, Excel will highlight the duplicate values.
This built-in rule is easy to implement as well as adequate, however, it sometimes is not; then you might have to turn to a formulaic rule.
How do you highlight rows in Excel?
Sadly, you cannot use a built-in rule to highlight the entire row when the column contains a duplicate value. For that, you will have to have a formula that relies on a COUNTIFS() in the form
COUNTIF(range,criteria)
In the formula, the range identifies the entire data set (a record) and criteria specify the condition. That can be a cell reference, a value, or it could even an expression. To do so follow:
- Click on the data range, if you wish to highlight the entire row. If you use a Table, then Excel will update the range as you add as well as delete records.
- Click on Conditional Formatting situated in the Styles group, then click on New Rule.
- In the top pane of the resulting dialog, you have to select the last option, use a Formula to Determine Which Cells to Format.
- You will have to enter the formula in the bottom pane.
- For example,
- =COUNTIF($D$3:$D$16, $D3)>1
- Click on Format, choose a format, then click on OK.
- Click on OK.
The COUNTIFS() function itself counts the number of times a value occurs in the column you selected. If that value is greater than one, it means that the value occurs more than once, then the function will return True, and the format is applied. If the count is 1 or less, the function will return False, and nothing happens.
Now, you have got to know about two conditional formatting rules which you can use to work with. One is built-in that helps to highlight individual values. The other one is a formula that will help you highlight the entire record.
If you liked this article (or if it helped at all), leave a comment below or share it with friends, so they can also know How to highlight duplicate values in Excel?
