How to use Conditional Formatting to find duplicate Rows in Google Sheets?

Google Sheets is one of Google’s most powerful products and one of the go-to solutions for spreadsheet and data management on the web.

Moreover, Sheets supports custom functions and formulas that help make up for some of these shortcomings. If users are savvy with formulas or can do a little bit of scripting, Sheets can be as powerful as they make it.

How To Use Conditional Formatting to Find Duplicate Rows In Google Sheets

• Once highlighted, right-click and select Conditional Formatting. This will bring up a panel on the right side of the window that shows conditional formatting rules. The column range selected will be prefilled in the Apply to range field, so that won’t need to be touched.
• Next, you want to click on the dropdown menu beneath Format rules to change this condition to Custom formula is. A text field will appear beneath the dropdown menu after this is done, and the following formula should be entered:

=countif(A:A,A1)>1

• However, it will need to be modified to replace all instances of “A” with the column letter that is being searched for duplicates in Google Sheets, which will be shown in the Apply to range field.
• The final step is to set the formatting to apply to all cells in this row that contain data found more than once. If working with a large set of data, the best formatting to set might be a fill color. Using it, a bright and noticeable color such as yellow, hot pink, or lime green will really stand out and allow for noticing duplicates at a glance while scrolling.
• Now, all rows that contain data that is found in at least one other row will be highlighted or formatted in whatever way was chosen. If duplicate rows are deleted, the single row remaining will have its conditional formatting removed automatically.

Join Tip3X on Telegram