How to Color Duplicates in Google Sheets

Ah, the age-old dilemma of identifying and managing duplicates in Google Sheets! It's a common struggle, but fear not, we have the perfect solution: color-coding. By learning how to color duplicates in Google Sheets, you'll not only save time but also enhance your data's readability. So, let's dive into this time-saving trick shall we?

Before we commence, ensure that your Google Sheets is up-to-date. The color-coding feature is available from version 8.0.5 onwards. Now, let's get to the nitty-gritty.

Applying Conditional Formatting to Color Duplicates

The first step involves using Google Sheets' built-in Conditional Formatting tool. This feature allows you to automatically apply colors based on specific conditions. In our case, we want to color cells containing duplicate values.

Let's start by selecting the column/range where you suspect duplicates exist. Here's how to do it:

Identifying Duplicates

Using the COUNTIF function, we'll create a formula to identify duplicates. This function counts the number of times a specific value (in our case, the cell's content) appears within a range.

Assuming you wish to check column A for duplicates, your formula should look something like this: ="COUNTIF($A:$A, A1)>1". This means, "count the number of times the value in A1 appears in column A".

Filtering Duplicates

After entering this formula, press enter. Any cells containing duplicates will display the number of times they appear, while unique cells will show 0. Now, let's use conditional formatting to color these duplicated values.

Select the entire range of cells (including your newly created formula). Click on 'Format', hover over 'Conditional Formatting', then select 'Use a formula' from the dropdown. In the 'Value or formula' field, enter: $"A$1="". Here, "$A$1=""" means "when the condition in A1 is}".

Applying Colors to Duplicates

Now, let's specify the color for your duplicates. In the 'Formatting style' field, select your preferred color. Click 'Done'.

Presto! Your duplicate-values are now highlighted with the color of your choice. This not only improves data readability but also makes spotting duplicates a breeze.

Changing Highlighted Cells' Background Color

But what if you want to change the background color of the cell instead? No worries, just follow these steps.

Instead of choosing the specific color you want, select 'Custom Formula' under 'Formatting style'. Enter the following formula: ="$A$1=""Yes""". Then, choose your preferred highlight color. Click 'Done'.

Remember, this custom formula will apply to the entire range. So, ensure the range is as wide as possible to cover all potential duplicates.

And there you have it! You've successfully learned how to color duplicates in Google Sheets. So why wait? Go ahead and make your data more readable. Happy coloring!