When you are managing a massive spreadsheet—such as a list of 5,000 customer email addresses or an inventory database—it is inevitable that duplicate entries will slip into the data. Manually scrolling through thousands of rows looking for repeating names or matching part numbers is impossible. Instead of hunting for errors with your eyes, you can force Google Sheets to instantly scan the entire document and highlight every single duplicate cell in bright red.
Using Conditional Formatting for Duplicates
Google Sheets does not have a simple “Highlight Duplicates” button like Microsoft Excel does. Instead, we must use a simple custom formula within the Conditional Formatting menu.
- Open your spreadsheet in Google Sheets.
- Select the entire column that you want to check for duplicates. To do this, click the grey letter at the very top of the column (for example, click the letter A). The entire column will turn blue.
- Click Format in the top menu bar.
- From the drop-down menu, select Conditional formatting. A sidebar will open on the right side of your screen.
Entering the Custom Formula
We will now instruct Google Sheets to count how many times a value appears. If the count is greater than one, it will trigger the highlight.
- In the sidebar, look under the Format rules section.
- Click the drop-down menu labeled Format cells if….
- Scroll all the way to the very bottom of the list and select Custom formula is.
- A new text box will appear. If you are highlighting Column A, type exactly this formula:
=COUNTIF(A:A, A1)>1- Under the Formatting style section, click the paint bucket icon and choose a bright color (like Red or Yellow).
- Click the green Done button.
The spreadsheet will instantly transform. Any email address or inventory number that appears more than once in that column will be bathed in bright red, allowing you to easily locate and delete the redundant data.