How to highlight cells with same/duplicate values in Excel?

We can apply the conditional Formatting feature to highlight all duplicates values in a certain column easily. You can do as follows:

1. Select the column that you will highlight all duplicate values, and click the Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.

2. In the opening Duplicate Values dialog box, specify the highlight color in the second drop down list, and click the OK button.

Note: You can also customize the fill color with selecting the Custom Format from the second drop down list, and then specify the custom fill color on the Fill tab of Format Cells dialog box. See below screenshot:

So far, all duplicate values in the selected column are highlighted with the specified color as below screenshots shown:

Kutools for Excel also provides a Select Duplicate & Unique Cells utility to highlight duplicate cells in a certain column. What’s more, this utility enable to highlight the whole rows that duplicate values are in.

1. Select the column that you will highlight duplicate values, and click the Kutools > Select > Select Duplicate & Unique Cells. See screenshot:

2. In the opening Select Duplicate & Unique Cells dialog box, please (see screenshot above):
(1) Check the Duplicate (Except 1st one) option or All duplicates (Including 1st one) option;
(2) Check the Fill backcolor option, and click below drop down list and specify a fill color;
(3) It’s optional to check the Select entire rows option.

3. Click the Ok button. Then a dialog box comes out to show how many rows have been selected. Just click the OK button to close it.

Then you will get the similar highlight rows/cells with same values as below screenshots shown:

Sometimes, you may want to compare two columns, and then highlight the same values in one column. For example, you need to compare the Column B and Column G as below screenshot shown, and then highlight the same values in the Column B. You can try Kutools for Excel’s Compare Ranges utility to archive it easily.

1. Click the Kutools > Select > Select Same & Different Cells to open the Compare Ranges dialog box.

2. In the opening Select Same & Different Cells dialog box, please:

(1) Specify the column that you will highlight same values in the Find values in box;
(2) Specify the column that you will highlight same values according to in the According to box;
(3) Check the Each row option in the Based on section;
(4) Check the Same Values option in the Find section.
(5) Check the Fill backcolor option, and click below drop down list to select a fill color;
(6) It’s optional to check the Select entire rows option.

Note: If your selected data has headers, please check the My data has headers option in the Compare Ranges dialog box.

3. Click the Ok button. Then a Compare Ranges dialog box comes out to show how many rows have been selected. Just click the OK button to close it.

As long as values in Column B are same as values in Column G, corresponding cells or rows will be highlighted as below screenshots shown: