• Step 1: Formatting data.
• Step 2: Finding duplicate elements in the column. First, highlight the data in which we want to see whether there are any duplicate elements or not.
• Step 3: Then, click on Home on the Ribbon. In the Styles group, click Conditional Formatting.
• Step 4: In the resulting menu, click Highlight Cells Rules.
• Step 5: Then in the resulting menu, click Duplicate Values
• Step 6: In the Duplicate Values box, we have to select the formatting which we want to apply to the duplicate values, and then click OK.

Duplicate Rows in Excel

Use COUNTIFS (with a S at the end) instead of COUNTIF to locate and highlight duplicate rows in Excel.

• Step 1: Select the range A1:C16
• Step 2: Click Conditional Formatting under the Styles group on the Home menu
• Step 3: Click on New Rule
• Step 4: Select ‘Use a formula to determine which cells to format’
• Step 5: Enter formula =COUNTIFS(Business,$A1,Gender,$B1,Ethnicity,$C1)>1
• Step 6: Select a formatting style and click OK.