There are many ways to format your data in Excel such as data that is greater than or less than a number, data that falls between specific dates, cells that contain certain words or numbers, the list is endless.

Many of the above are built in to Excel and can be achieved with just a few clicks but conditional formatting can also be applied as a result of a formula entered. In this example, we are going to highlight cells where any duplicate values appear in our column.

In the data below I would like to highlight any names in the Sales Person column that appear more than once.

First, select our data range, in this case B2:B11. Then, on the Excel ribbon we go to the Conditional Formatting icon and select ‘New Rule’. The below will appear. Be sure to select the rule type ‘Use a formula to determine which cells to format’.

In the box titled ‘Edit the Rule Description:’ type the following: =COUNTIF($B$2:$B$11,B2)>1


Then, click on the Format option which will be applied if the condition is true.

I have chosen to highlight cells in red if the condition is true. Click ok when you have chosen your format and ok again to apply the rule to the selected cells. The result should look like this.


Leave a Reply

Your email address will not be published. Required fields are marked *