If you want to quickly find duplicate values in Google Sheets, conditional formatting is a highly effective tool. Whether you want to highlight duplicates in a single column, multiple columns, or the entire sheet, this tutorial will guide you through the process step by step. Just follow along and click "Next" to continue.
To access Google Sheets, click here.
Step-by-Step Guide to Highlight Duplicates in Google Sheets:
1. Open Conditional Formatting: Click on the Format menu, then choose Conditional formatting from the dropdown.
2. Set the Range: In the Apply to range field, enter the range you want to filter for duplicates. For example, if you only want to filter column A (and A1 is a header), start the range from A2:A999. This ensures that the header row is not included in the filter.
3. Set the Condition for Duplicates:
In the Format rules section, select Custom formula is.
For example, to highlight duplicates starting from row 2 in column A, use the formula:=COUNTIF(A:A, A2)>1
4. Apply the Rule:
Once you've set the formula, click Done to finish the process. Duplicate values will now be highlighted!
Filtering Multiple Columns or the Entire Sheet:
(Similarly, if you don’t want to filter the header row, make sure to exclude it when adding the range.)
1. Multiple Column Filtering: If you want to highlight duplicates across multiple columns, simply add another range in the Apply to range field.
2. Full Sheet Filtering: To apply the rule to the entire sheet, just set the Apply to range to cover the full table (e.g., A1:Z999).
Customizing the Highlighted Duplicates:
If you want to modify the background color or other styles of the highlighted duplicates, go to the Formatting style section. Here, you can adjust the background color, font color, and other formatting options to meet your needs.