This tutorial will demonstrate how to highlight duplicate rows using Conditional Formatting in Excel and Google Sheets.
Highlight Duplicate Rows – COUNTIFS Function
When programmed properly, the COUNTIFS Function will count all rows that match the current row.
Note: If you simply want to highlight duplicate values (instead of rows), you can use Excel’s built-in Highlight Cells Rules > Duplicate Cell Values.
- Select the range you want to apply formatting to.
- In the Ribbon, select Home > Conditional Formatting > New Rule.
- Select Use a formula to determine which cells to format, and enter the COUNTIFS formula:
Note: Your formula will differ based on the number of columns to compare.
- Click on the Format button and select your desired formatting.
- Click OK to view the result.
Highlight Duplicate Rows in Google Sheets
The process to highlight duplicate rows in Google Sheets is similar to the process in Excel.
- Highlight the cells you wish to format, and then click on Format > Conditional Formatting.
- The Apply to Range section will already be filled in.
- From the Format Rules section, select Custom Formula from the drop-down list and type in the formula:
- Select the fill style for the cells that meet the criteria.
- Click Done to apply the rule.