Arrow left
Back to product guides
Google Sheets Tips

How to Remove Duplicates in Google Sheets

There are several ways to get rid of duplicates in Google Sheets: Google Sheets’ “Data cleanup” function, formulas such as the UNIQUE and QUERY, and Pivot Table. In another way, You can use Conditional Formatting to identify duplicates and remove them manually. In this article, you will learn how to eliminate duplicates using the clean-up function in Google Sheets. The function is straightforward and valuable when you process the existing data set and don’t need to keep the filtered output dynamic.

How to delete duplicates by Data cleanup in Google Sheets

  1. Select the range you want to clean up.
  2. Go to the “Data” → “Data cleanup” → “Remove duplicates”, which brings up a pop-up menu.
  3. Check “Data has header now” if the selected range includes table headers.
  4. Choose the columns based on which duplicates will be removed in the “Columns to analyze” section.
  5. Click “Romove duplicates”.

Steps 1 and 2

How to show the “Remove duplicates” window from the menu bar in Google Sheets

Steps 3 to 5

What “Remove duplicates” menu looks like in Google Sheets

See the examples below to understand how the function works depending on the number of columns selected in Step 4 above.

Example #1 shows the case in which you select all columns as standard.

Example #2 presents the outcome when you focus on a single column (Column B - Name)

Example #3 describes the case in which you choose two columns for criteria (Column B - Name and Column C - Country)

The entire array, including table headers, is selected as the array to be streamlined and contains the same items in all examples.

Example #1

How “Data cleanup” works for multiple columns in Google Sheets

The highlighted rows are duplicates in the left table. As you can see, after the function is applied, there is no duplication in the table (on the right). The function judges if there is any redundancy based on values in selected columns in a specific row. For instance, the first row under the headers contains Isabella, North, 20, and Plan A. The function considers the factors as a chunk, and if it finds the same chunk in other rows, it excludes them from the outcome.

Example #2

How “Data cleanup” works for a column in Google Sheets

The function works similarly in this example. However, as we focus on the “Name” column this time, the entire table is rearranged only based on the items in Column B (Name); The rows incorporating identical names are removed.

Example #3

How “Data cleanup” works for two columns in Google Sheets

The function works similarly to the first example. In this example, it regards items in Columns B and C in a row as one group and eliminates other rows containing the same information. Thus, as you can see, even though the sixth row includes the same information (Isabella) for Column B as the first row because their countries are different, the sixth row remains in the streamlined list.

Finally, ensure you don’t have the extra space(s) or other hidden texts in each cell in the range to be filtered.

Learn how to do this step-by-step in the video below 👇

Do you need personal help?

Our team of real people are here to help you any time between 9am and 10pm GMT.
Check Icon
Email us at: founders@liveflow.io
Check Icon
Call us at +1 (415) 650-1711