Highlight duplicates within rows in Excel

Sometimes you want to check if the same entry appears more than once in the same row. For example in this dataset you may want to know that Jan appears twice in the first row.

image

You can use conditional formatting to spot these duplicates like this.

  1. First select the cells in the first row that you want to look for duplicates in.
  2. Then use Conditional Formatting > New Rule > Use a formula to determine which cells to format.
  3. In the formula section put =COUNTIF($A1:$F1,A1)>1 . In my data the row starts and ends at A1 and F1. Change these values within the formula to match your data.
  4. Set the format you want using the format button and select OK.

image

Now the duplicate Jan will be highlighted in the first row like this

image

Now you just need to copy this formatting to each row of your data. One way to do this is to use the Format Painter.

  1. Select the first row of data again and then click on Home > Format Painter.
  2. The mouse cursor will change to a paintbrush symbol.
  3. Now drag the mouse to select all the rest of the rows of data at once and then release the mouse button.

image

Done!