Select Use a formula to determine which cells to format. First clear the previous conditional formatting rule. The method to find duplicate rows in your spreadsheet is similar except the range of cells you select to analyze by the formula is different.
Then your spreadsheet should match the one shown directly below.
First enter some dummy data into a blank Excel spreadsheet. With only two steps you can change the color of the cells that have duplicate values. You might want to include or exclude the first instance when counting duplicates. Select the H ighlight Cells Rules option and from the secondary menu that appears select the D uplicate Values.