When you are managing a massive mailing list, tracking inventory, or compiling survey results, duplicate entries are inevitable. Sorting the list and scanning it with your eyes to find matching names is tedious and prone to human error. Instead, you can use Google Sheets’ Conditional Formatting feature combined with a custom formula to automatically highlight any duplicate data in bright red.
How to Setup the Formatting Rule
We will use the COUNTIF formula to tell Google Sheets to flag any data that appears more than once.
- Open your spreadsheet in Google Sheets.
- Highlight the entire column you want to check for duplicates (for example, click the letter A at the top to select the whole column).
- Click on Format in the top toolbar, then select Conditional formatting from the dropdown menu.
- A sidebar will appear on the right side of your screen. Under the “Format rules” section, click the dropdown menu labelled “Format cells if…”.
- Scroll all the way down to the very bottom of the list and select Custom formula is.
- In the empty text box that appears, type the following formula exactly (assuming you are checking column A):
=COUNTIF(A:A, A1)>1
Choosing Your Highlight Colour
Under the “Formatting style” section, click the paint bucket icon and choose a bright colour (like red or yellow). Click the green Done button. Instantly, any cell in Column A that contains data matching another cell will be highlighted. If you delete one of the duplicates, the highlight will automatically disappear from the remaining unique cell.