How to Highlight Duplicates in Google Sheets?

If you work regularly in spreadsheets, transferring various data from one cell to another, you have probably ended up with duplicate data across columns and/or rows. If your cells contain formulae, then it is also likely that this duplicate content has negatively affected your calculations. As a result, your data is no longer accurate, and it can become extremely time-consuming to manually highlight and remove each duplicate value.

This article will explain how to highlight duplicates in Google Sheets using the built-in conditional formatting feature as well as the =UNIQUE formula. You’ll then learn how to safely remove duplicates from any range in your dataset, whether they appear in one or two columns.

How to find duplicates in Google Sheets in one column?

The quickest way to identify duplicates in Google Sheets is to color highlight them. You can perform a search for duplicates in one or two columns by using the Conditional Formatting feature; this will allow you to color format the cell or the text of the duplicate content. Let’s start by finding duplicates in one column.

1. 1. Open your Google Sheets and select the column where you want to perform the search for duplicates. Don’t forget that your columns need to be named in order for this feature to work. Here, I’ll select Column A to find possible duplicate values of names.

1. 2. Go to Format > Conditional Formatting. The “Conditional Formatting” will open to the right-hand side of your screen.

1. 3. Check that the cell range shown corresponds to the column where you want to find duplicates. Then in the “Format cells if” drop-down list, select “Custom formula is”. A new field should now appear below.

1. 4. Enter the following formula in the new field, including the column range you selected previously. Here, I will enter =COUNTIF(A:A,A1)>1

1. 5. In “Formatting style” select a filling color for the duplicate cells. In this example, I will select “Red”.

1. 6. Another way to format the duplicate cell is to change the color of the text color; select the text color icon (A) in the menu bar. In this example, I will select “Red”.

1. 7. To apply the conditional formatting, click “Done”. It should now highlight all duplicates using your formatting.

As you can see above, the Conditional Formatting feature has successfully highlighted duplicate values in one column. Let’s now apply this same highlighting method to duplicate values in two columns.

How to find duplicates in Google Sheets in two columns?

Finding duplicates in Google Sheets in two columns is an almost identical process; the only difference is in the first step, where you need to select a larger range to include a second or third column. This is especially useful when your dataset is too large for you to remember where duplicates may have occurred. We recommend selecting the whole sheet. This is how you can find duplicates by applying conditional formatting to two columns.

1. 1. Select the columns where you want to perform the search for duplicates. Here, I’ll select Column A and B to find possible duplicate values for both first names and last names.

1. 2. Go to Format > Conditional Formatting. The “Conditional Formatting” will open to the right.

1. 3. Check that the cell range shown corresponds to the two columns. Then in the “Format cells if” drop-down list, select “Custom formula is”. A new field should now appear below.

1. 4. Enter the same formula as before, including the range you selected. Here, I will enter =COUNTIF(A:B,A1)>1. As you can see, it’s almost the same formula as the previous example. Simply change the data range of the first parameter to the range of columns you want to check.

1. 5. In “Formatting style” select a fill color for the duplicate cells or font color. In this example, I will select a fill color in “Red”.

To apply the formatting, click “Done”. All duplicates are now highlighted in the color you selected.

Now that you’ve seen how to highlight duplicates in one or various columns using the Conditional Formatting feature in Google Sheets, let’s ‌look at how you can achieve the same using the =UNIQUE formula.

How to highlight duplicates in Google Sheets using a formula?

In the previous section, we showed you how to use the Conditional Formatting built-in feature combined with the =COUNTIF formula. However, you can use the =UNIQUE formula directly in your Google Sheets file to find and highlight duplicates in your worksheet.

1. 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.

1. 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).

1. 3. Press “Enter” to apply the formula. It will fill your column with only the unique values in your data range, skipping any duplicates. As you can see, this column has skipped the duplicate data in rows 12 and 13.

You can also apply this formula to entire duplicate rows. Simply change the formula to include row numbers, rather than column letters. For example, to find duplicate rows in my data, I simply type in the following formula: =UNIQUE(2:17). As you can see below, the formula returns only the unique rows of data - it has skipped rows 12 and 13.

As the formula will return entire values, adding them directly next to your dataset may cause an error message to appear. Instead, we recommend adding the formula directly below your dataset (as shown in the example above), as this will allow the UNIQUE formula to provide a full list of all your unique rows.

Now that we know the two main ways to highlight duplicates in Google Sheets, let’s see how we can quickly remove them.

How to remove duplicates in Google Sheets?

Once you have found and highlighted duplicates in one or two columns, a row, or an entire worksheet, you can easily remove them using the “Remove Duplicates” feature.

1. 1. Select the columns you would like to check and remove duplicate content for. Here, we will stick to the same columns we highlighted in red.

1. 2. Go to Data > Data cleanup > Remove duplicates.

1. 3. A dialogue box should appear where you can select the columns to check. “Select all” simply refers to the column manually selected before, not the whole dataset.

1. 4. If your columns have a header name, make sure to tick the box “Data has header row”. Once finished, click “Remove duplicates”.

1. 5. Google Sheets will now show a message informing you about the number of duplicate rows removed. It also lets you know how many unique rows you have. Click “OK”.

This built-in feature is the quickest way to remove duplicates in Google Sheets; however, ‌review the content selected before removing it since you may delete cells associated with others in a row.

Want to Boost Your Team's Productivity and Efficiency?

Transform the way your team collaborates with Confluence, a remote-friendly workspace designed to bring knowledge and collaboration together. Say goodbye to scattered information and disjointed communication, and embrace a platform that empowers your team to accomplish more, together.

Key Features and Benefits:

• Centralized Knowledge: Access your team’s collective wisdom with ease.
• Collaborative Workspace: Foster engagement with flexible project tools.
• Seamless Communication: Connect your entire organization effortlessly.
• Preserve Ideas: Capture insights without losing them in chats or notifications.
• Comprehensive Platform: Manage all content in one organized location.
• Open Teamwork: Empower employees to contribute, share, and grow.
• Superior Integrations: Sync with tools like Slack, Jira, Trello, and more.

Limited-Time Offer: Sign up for Confluence today and claim your forever-free plan, revolutionizing your team’s collaboration experience.

Conclusion

Highlighting duplicates in Google Sheets is an extremely vital process many users need to carry out to make sure that their datasets are as accurate as possible. Google Sheets has a fantastic data cleanup feature that makes this process quick and easy.

By the end of this article, you should now know how to highlight and remove duplicates in Google Sheets using built-in features and the UNIQUE formula. Although these methods are safe and quick, we always recommend reviewing your selection before removing any data in your worksheet.

If you would like to know more about Conditional Formatting features and applications in Google Sheets, read our blog article Conditional Formatting in Google Sheets (Complete Guide). Prefer to work in Excel? Take a look at the following articles you manage and organize your data better:

• How to Find and Remove Duplicates in Excel?
• How to Use Conditional Formatting in Excel?