Did you know that around 80% of the time spent on data analysis is actually used for cleaning and preparing the data?[1]

Credits: [1] Stats and Graphics are obtained from a 2016 Forbes Article by Gil Press .

That stat alone tells us one thing: most data isn’t clean.

Whether you’re working with spreadsheets for reporting, dashboards, or using Google Sheets as a simple database or tracker — there’s a good chance your data has issues. From duplicated entries to typos, it happens more often than we think, especially when multiple people are updating the same sheet.

One common mistake? Accidentally duplicated rows or values.

This is where conditional formatting comes in handy. Instead of manually scanning rows and/or columns, you can set up a rule to instantly highlight duplicates.

It’s a great way to:

• Keep your data clean
• Spot issues as soon as they happen
• Help users fix errors in real-time when entering data
• Highlight the duplicated values so the PIC can validate easily.

In this tutorial, you’ll learn how to use conditional formatting in Google Sheets to highlight duplicate values:

Tips: Click to jump to the section below.

• In a single column
• In a single row
• Across multiple columns and rows

Bonus: If you’re new to google spreadsheets, we have prepared a workbook for you. Download it now to learn better, it includes all the examples in this guide. Click here to download.

Highlight Duplicated Cells in a Single Column in Google Sheets – Tutorial

Sometimes you’re working with a vertical list — like names, emails, product IDs — and you need to quickly check if any values appear more than once in a single column. Here’s how to instantly highlight the duplicates.

Let’s say your data is in Column B (B2:B100).

Step 1: Select the Range
Click and drag to select the range you want to check.
👉 Example: B2:B21 (skip the header if you have one)

Step 2: Open Conditional Formatting
Go to the top menu and click:
Format > Conditional formatting.

Step 3: Use a Custom Formula
In the sidebar that appears, under the tab “Single colour”. Select the dropdown “Format cells if” as “Custom Formula is”.

Enter this formula:
= COUNTIF($B$2:$B$21, B2) > 1
This formula checks if the current cell’s value appears more than once in the selected range.

Step 4: Choose a Formatting Style

Pick a style that stands out (I like to go for light yellow fill). Click Done.

Highlight Duplicated Cells in a Single Rows in Google Sheets – Tutorial

Most tutorials focus on vertical lists (columns), but data doesn’t always come that way. Sometimes, the data is arranged horizontally in a row.

Let’s say your data is in row 2 (B2:U2).

Step 1: Select the Range
Click and drag to select the range you want to check.
👉 Example: B2:U2 (skip the header in A2 if you have one)

Step 2: Set Conditional Formatting
Go to the top menu and click:
Format > Conditional formatting.

Step 3: Use a Custom Formula
In the sidebar that appears, under the tab “Single colour”. Select the dropdown “Format cells if” as “Custom Formula is”.


Enter this formula:

= COUNTIF($B$2:$U$2, B2) > 1

This formula checks if the current cell’s value appears more than once in the selected rows.

Step 4: Choose a Formatting Style

Pick a style for the duplicated values. Click Done.

Highlight Duplicated Cells in across Multiple Rows and Columns in Google Sheets – Tutorial

In the previous tutorial, we focused on cleaning up data by identifying duplicates and false entries. Now, we’re shifting to a different use case: highlighting repeating entries across a wide table, where data is spread across multiple rows and columns.

This technique is not limited to finding errors; it’s useful for spotting patterns and identifying recurring data points, i.e. searching for the winner name that are in top 3 places for more than once.

Here’s how:

Let’s say your data is in row 2 (B2:D4). The data is organized in a way where years are listed in the first column, and the columns to the right contain the prize medals (e.g., Gold, Silver, Bronze), with names of winners under each category.

Using conditional formatting, we can instantly highlight names that appear more than once — giving us a visual cue of repeat winners.

Step 1: Select the Range
Click and drag to select the range you want to check.
👉 Example: B2:D4 (note that we skip the year header in A1:A4 and category header in B1:D1.)

Step 2: Set Conditional Formatting
Go to the top menu and click:
Format > Conditional formatting.

Step 3: Use a Custom Formula
In the sidebar that appears, under the tab “Single colour”. Select the dropdown “Format cells if” as “Custom Formula is”.


Enter this formula:

=COUNTIF($B:$D,Indirect(Address(Row(),Column(),)))>1

This formula checks if a name (in any of the Gold, Silver, or Bronze categories) appears more than once in the entire range. If a name appears more than once, it will be highlighted.

Pro tips: Instead of checking for values that appear more than once, modify the formula to =COUNTIF($B$2:$U$2, B2) > 2. This will highlight any winner who has repeated their success in your data more than twice.

Step 4: Choose a Formatting Style

Pick a style for the duplicated values. Click Done.

Conclusion

By using conditional formatting to highlight repeating entries, you can quickly spot patterns, track recurring data points, and gain valuable insights from your dataset.

Now that you’ve mastered conditional formatting to highlight duplicates, it’s time to learn an efficient way to remove duplicates from your data without using formula!

Check out this tutorial next on how to remove duplicates in Google Sheets.