Google Sheets conditional formatting offers the ultimate spreadsheet solution when you want to stop manually searching for duplicate text, product names, or data points in large datasets.
Google Sheets conditional formatting crafted with custom formulas—like COUNTIF and COUNTIFS—transforms how you identify repeated records, manage inventory, and clean up spreadsheets instantly.
Key Takeaways:
- To highlight duplicates in Google Sheets, select the range > click Format > Conditional Formating > Format Rules > Custom Formula Is. Enter the formula =COUNTIF(A:A, A1) > 1 and click Done.
- =COUNTIF(A:A, A1)>1 is the formula to highlight duplicates in Google Sheets. Replace the letter A with the corresponding column.
- =COUNTIFS($B$1:$B,$B1,$C$1:$C,$C1) is the formula for highlighting duplicates in two columns. You can edit it to affect more columns.
How to Highlight Duplicates in Google Sheets
To highlight duplicates in Google Sheets, you need to set “Conditional Formatting” and use the formula “=COUNTIF(A:A, A1) > 1”.
Imagine you’re reviewing your inventory to identify the most popular products.
Your inventory list contains multiple entries for the same product, but they might be scattered throughout the sheet. Manually scanning the “Product Name” column would be inefficient and prone to errors, especially if your inventory is large.
Let’s see how to highlight duplicates using the formula mentioned above.
1. Select the entire “Product Name” column (Column B in our example).
2. Go to the “Format” menu and select “Conditional formatting.”
A sidebar will appear on the right.
3. In the sidebar, under “Format rules,” choose “Custom formula is.”
4. Enter the following formula in the text box: “=COUNTIF(B:B, B1) > 1” and click “Done.” To use a different color, change the fill or text color under Formatting style before you click Done.
=COUNTIF(B:B, B1) looks at each cell in column B (Product Name). It then counts the number of times a product name appears in the whole column. > 1 means that if a product name appears more than once, it should be highlighted.
Our inventory example shows that “Laptop Pro” and “Wireless Headphones” appear more than once.
If you want to check column A, change the B in the formula to A. The new formula will be =COUNTIF(A:A, A1) > 1, and column C will be =COUNTIF(C:C, C1) > 1. Remember to select the matching range or column: A column for the A formula and C column for the C formula.
If you only want to highlight duplicates that appear more than ten times, change the number 1 to 10 at the end of the formula. The new formula will be: =COUNTIF(B:B, B1) > 10.
How to Highlight Duplicates in Multiple Columns
Assume you want to know if you’ve bought a specific product from the same supplier more than once. This will help you identify your most frequent partners and negotiate better deals. In this case, you need to count the events where BOTH the “Product Name” AND the “Supplier” columns match.
So, let’s see how to highlight duplicates in multiple columns using “conditional formatting” and “absolute values.”
1. Select the entire table or the needed columns. In our case, we will select the “Product Name” and “Supplier” columns (columns B and C).
2. Go to the “Format” menu and select “Conditional formatting.”
3. Under “Format rules,” choose “Custom formula is.”
4. Enter the following formula: =COUNTIFS($B$1:$B,$B1,$C$1:$C,$C1) > 1
=COUNTIFS($B$1:$B,$B1,$C$1:$C,$C1) looks at each cell in column B (Product Name) and column C (Supplier). It then counts the number of times columns B and C match. > 1 means B and C match more than once, it should be highlighted. The dollar signs ($) in the formula create absolute references. It ensures the formula works even when copied to other cells.
To highlight a value that appears more than once anywhere in columns B and C, rather than a repeated product and supplier pair, select both columns and use =COUNTIF($B:$C, B1) > 1 instead.
How to Remove Duplicates in Google Sheets?
Follow the steps below to remove duplicates in Google Sheets:
1. Select the range or entire sheet containing the data.
2. Go to Data > Data cleanup > Remove duplicates.
3. Choose whether your data has headers. In our case, we will check “Data has header now” since there is a header called “Product Name.”
4. Select the columns you want to check for duplicates.
5. Click “Remove duplicates.”
A popup will appear and indicate the number of rows removed.
The downside is this method removes entire rows, not individual cells.
How to filter out duplicates with the UNIQUE formula
Remove duplicates deletes rows from your data. To keep the original data and get a separate list without duplicates, use the UNIQUE function instead. It returns each value once.
- 1. Open your Google Sheets and type in “=UNIQUE” into an empty cell next to your data. You will see how Google Sheets prompts the full formula.
- 2. You can complete the formula by clicking the letter at the top of the column you want to find duplicates in. Google Sheets will also include the column range for you. Here, I only want to find duplicates in Column A, so my completed formula looks like this: =UNIQUE(A:A).
- 3. Press “Enter” to apply the formula. It will fill your column with only the unique values in your data range, skipping any duplicates.
You can also apply this formula to entire duplicate rows. To do that, give it a range that covers every column of your data, for example =UNIQUE(A2:F17). The formula then returns each row once and skips any row that matches an earlier row in every column.
When you check whole rows, the results are as wide as your data, so place the formula where there is room, such as a few rows below your data or on a new sheet. If any cell the results need already holds data, the formula returns a #REF! error.
Edit, Add, or Delete a Conditional Formatting Rule
Follow the steps below to edit, add, or delete a conditional formatting rule.
1. Select the range with the conditional formatting you want to edit.
2. Go to the “Format” menu and select “Conditional formatting.”
3. The sidebar will open and show the existing formatting rules for the selected range. Click on the rule you want to edit.
4. Update the format rules, formula, formatting style, or range as needed. Click “Done” to save the changes.
To add a new conditional formatting rule, follow steps 1-2 and click “Add another rule.”
To delete a conditional formatting rule, follow steps 1-2, hover your mouse over the rule, and click on the trash can icon next to it.
Rule Order Pro Tip:
If you have multiple rules for the same range, their order matters. Rules are evaluated from top to bottom, and the first rule that applies to a cell determines its formatting. You can reorder rules by clicking and dragging them in the sidebar.
If the formatting applied by a higher rule covers the conditions of a lower rule, the lower rule might be overridden or might not appear at all.