Buy Now
Use conditional formatting, the Remove Duplicates Tool and the UNIQUE function to effectively and easily remove duplicates in Google Sheets in this Simple Sheets Blogpost

How to Remove Duplicates in Google Sheets Without Using Apps Script

conditional formatting countif excel functions google sheets gsheets remove duplicates unique May 18, 2022

A Google Sheet vs. An Excel Sheet?

Whether you're a small business owner, or a spreadsheet god that uses Excel for data analysis, there's no beating around the bush that Google Sheets has now been a staple for all sorts of people and organizations when it comes to fulfilling their spreadsheet needs. Who can blame them? Auto-saves, collaboration, and the free price tag really do come in handy.

That being said, there are many differences when it comes to how different functions and tools operate between both applications. If you want to learn how you can remove duplicates in Microsoft Excel, you can check out our other blogpost right on how to remove duplicates in Excel.

With the integration of Google Sheets with another app such as Google Forms, we see more and more raw data being processed with Google Sheets. If you use a Google Sheet to track attendance for example, you will most definitely have duplicate values whether it be text or numbers. In this article, we'll talk about a few neat tricks that we can use in Google Sheets to highlight duplicates to identify duplicates, as well as how to remove duplicates in Google Sheets.

How to Highlight Duplicate Cells in or Within a Single Column Range

Duplicates in Google Sheets: Not as Straightforward as You Think

Suppose we maintain a list with names in Google Sheets and we just want to see which duplicate entries there are so we can take note of returning customers.

Manually checking for duplicate entries in this list is not only time consuming, but it's also very prone to errors.

As we mentioned earlier, there are a ton of things that Google Sheets does differently from Microsoft Excel. One of these things is the ability to readily highlight duplicates using conditional formatting. In Excel, you can highlight duplicates in as easy as three clicks. Highlighting duplicates in Google Sheets requires a couple more steps.

Step-by-Step: How to Highlight Duplicate Entries In a Single Column In Google Sheets

Step 1: Select the data range you want to highlight duplicate entries in or at least take note of its column letter

Note: When creating your selection, be mindful of the header row as its formatting could change depending on if there's also a duplicate somewhere in the range.

Step 2: Open up the conditional formatting module

In the Menu Bar, select Format and in the dropdown, select Conditional Formatting to open up the conditional formatting module.

Step 3: Create a custom formula

Unlike Excel, Google Sheets doesn't have a preset condition to highlight duplicates so we have to create our own. Go ahead and select "Custom formula is" in the Format Rules dropdown.

Enter this formula: =COUNTIF(A:A,A1)>1

This formula allows you to highlight duplicate cells in a single column.

Do take note that any new entries entered at the bottom do would not be included unless you adjust the "Apply to range" field in the Conditional Format Rules Module

How it Works:

This custom formula basically creates a true/false check. We can see this custom formula in action if we place it beside the column with duplicate records that we were working on.

We see that it returns a True or a False value. What happens here is that we used the COUNTIF statement to get the number of instances a name appears in the column. When we add the ">1" we tell Google Sheets to return True if the number of instances are more than one and False if it's not. When we plug this in the conditional formatting module, the custom formula adds color the cells involved if there are more than one instance of it or if there are duplicates.

Step-by-Step: Highlighting Duplicate Data Across Multiple Columns

In case you want to to highlight duplicate data in a data range that spans multiple columns, you can also use the steps above with just some variations to the custom formula.

Suppose we have two columns now that we want to check for duplicate data.

Step 1: Select the columns that we want to apply the conditional formatting to

Step 2: Open the Conditional Format Rule Module

Just like what we did earlier, in the Menu Bar, select Format and in the dropdown, select Conditional Formatting to open up the conditional formatting module.

Step 3: Create your custom formula

In the Format Rules portion, select again "Custom formula is"

Enter this formula =COUNTIF(A:B,A1)>1

Your selected columns now have the duplicate values highlighted!

Without having the preset, it just takes a tad bit longer to highlight duplicates in Google Sheets, but with a little Excel know-how, you too will master Google Sheets like the back of your hand. In the next section, let's talk about how to delete duplicates or remove duplicate rows.

How to Remove Duplicates in Google Sheets

Using the Filter Tool

If you're trying to maintain a database, knowing how to delete duplicates would prove to be one of the more important skills. If you're coming from the previous section and you already know how to highlight duplicates, you can use the the Filter tool in Google Sheets to manually remove duplicates. This method deletes duplicates based on cell formatting, so it's a given that you need to format cells as a prerequisite.

Step 1: Select a cell inside the data range and create a filter

Click on any cell within your data range and in the Menu Bar, open the Data Menu dropdown and select "Create Filter"

You can tell if you've correctly created the filter when you see the filter options icon on the header row.

Step 2: Filter out the unique rows

Open the filter options icon of the column that you want to see the duplicate rows of. Select Filter by color > Fill Color and select the color that you set for the highlighted duplicates.

You should see something like this

Step 3: Manually remove duplicate cells

Start reviewing and manually deleting duplicate rows starting from the bottom. To select an entire column, you can use the keyboard shortcut Shift + Space Bar

You can tell when you have the whole duplicate rows selected if the row number on the left is highlighted. To delete duplicate rows, go to the Menu Bar > Edit > Delete > Delete Selected Rows

As you remove duplicate rows, the original values lose their formatting indicating that only one unique value remains for that cell in the data set

This method is can be used to analyze an entire table, but it works best with fewer rows as this takes a huge amount of time. Although this method gives you the opportunity to individually check before you delete entire rows. It's also important to note that depending on how many duplicates are present, this could take Google Sheet more time to process.

Using the Built-in Remove Duplicates Tool

With the manual method of removing duplicates in Google Sheets out of the way, we can now really get down to business to remove duplicate entries. This is the fastest method of deleting duplicates in Google Sheets.

Step 1: Select your table

Step 2: Open up the Remove Duplicates tool

In the Menu bar, open up the Data Menu and select Data Cleanup then Remove Duplicates

Step 3: Set your parameters and remove duplicates

The Remove Duplicates Dialog Box appears and here we can see we have the options to select the column or columns to seek out the duplicates from.

We can tick the check box on top to tell the tool whether or not our selected data range contains headers. Once we tick that check box, we can see the column names appear in the Columns to analyze portion.

Suppose you only want to remove duplicates based on the names, we can untick "Column B - State" and press Remove Duplicates.

A message shows up showing you how many duplicate rows were found and how many remain.

Only the de duplicated data in the list remain as we can see above. The conditional formatting that we set awhile ago is now gone indicating that all we see are unique rows. Do note that how the tool operates is that it deletes rows from the bottom of the table going up, similar to what we were doing awhile ago during the manual removal.

Removing Duplicates Using the UNIQUE Function

Another, less-invasive way of removing duplicate rows in Google Sheets is by using the UNIQUE function. The UNIQUE function is an array function that returns only one instance of each unique value in a given data range. This array function is dynamic and fully automatic which makes it great for use in dashboards and automated spreadsheets.

Similar to our previous examples, let's try getting the unique names in this list.

In a separate column, let's type in this formula: =UNIQUE(A2:A15)

As we can see, it only returned the unique names in the list, effectively removing the same data. This method allows you to work indirectly off of a data set which allows you to preserve its integrity. If we were to use the Remove Duplicates tool on our main data set, it'd be very hard to assure the integrity of the data when we delete a duplicate row, whereas using this function allows you to remove duplicates while leaving the data alone.

When using dynamic array formulas, it's important to note to make sure that the cells under the cell that has the formula are clear. If there are values or text under the main cell, a #REF! error appears.

This error is the equivalent of the #SPILL! Error in Microsoft Excel. You can't look up a guide on how to deal with that in our other blogpost here.

The Wrap-Up

Whether you're team Microsoft Excel or Team Google Sheets, both applications have their quirks and methods on getting stuff done. Through this article, you can now extract unique records in your existing sheet, compare columns for duplicate entries, and now know how to remove duplicates in Google Sheets.

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Cras sed sapien quam. Sed dapibus est id enim facilisis, at posuere turpis adipiscing. Quisque sit amet dui dui.

Call To Action

Stay connected with news and updates!

Join our mailing list to receive the latest news and updates from our team.
Don't worry, your information will not be shared.

We hate SPAM. We will never sell your information, for any reason.