How To Remove Duplicates in Google Sheets

Duplicates, or exact copies of one bit of information, can make for a messy document. If you are saddled with a long list in Google Sheets full of redundancies or tasked with the job of entering or importing information to generate a new sheet, making use of Google Sheets’ Remove Duplicates function will make your job a whole lot easier.

Even if you’re unsure whether or not duplicates exist within your sheet, if you’re wanting to remove them, it’s best to go through this process.

Tip: Use the Sort Function First

If you are dealing with a list of information that can be sorted alphanumerically, I would definitely suggest first sorting the sheet this way. Though this is not a necessary step, it will allow you to see what information you may want to avoid using the function on.

For instance, if you have two different people on your list that go by the same name and happen to have similar data, like on a wedding RSVP list, you could end up erasing unique data to you with the Remove Duplicates function.

It’s also a good idea to specify headers on your Google Sheet before continuing.

  1. To sort alphabetically, highlight the column by which you would like to sort your sheet.
  2. If you want to sort a range of data, highlight the range.
  3. Go to Data > Sort by Sheet/Range and choose whether you’d like to sort in ascending or descending order.

Removing Duplicates

There is a way to remove duplicates using an add-on. However, there are two more simple ways to achieve your desired results.

Use Remove Duplicates

This will search an entire specified range and quickly remove repeats of data.

  1. First, select the range you wish to adjust.
  2. Go to Data > Remove duplicate.
  3. A dialogue box will appear with checkboxes.
  4. If you have headers on your list, make sure “Data has header row” is checked off.
  5. Select “Select All” under Columns to Analyze.

By choosing “Select All” you are telling Google Sheets to go through the first column for repeats, and then the second column for repeats. This way, if you have two similar names in the first column but differing data in the second it will not remove it.

  1. Click “Remove Duplicates.”

You will then be provided with results to the duplicate removal, including the number of duplicates removed. Only unique data will remain.

Use the UNIQUE Formula

This method may take a little getting used to, but it’s good to know. This is also a good way to see side-by-side lists without altering the original list.

You will be entering in the following formula into your cell:

=UNIQUE(Starting Cell Reference 1:Ending Cell Reference 2).

  1. First, select an active cell where you will be displaying results.
  2. Next, enter in “=UNIQUE(“.
  3. With the formula still active, highlight the range in which you’d like to remove duplicates.
  4. Closeout the formula with “)”.
  5. Press Enter.

Example Formula: UNIQUE=(A2:B7)

Only unique data will be shown, starting from the cell in which you input the formula.

Conclusion

Wait, what about the add on? The add on is available to you in the GSuite Marketplace. It is called “Remove Duplicates.” However, the steps you must go through with the add on to actually remove duplicates are convoluted and tedious. Yet, there are some features that you may find helpful, including colored duplicate rows.

You could technically do the same thing with conditional formatting. In my personal opinion, it’s better to stick with what Google Sheets provides.

Get more Google Sheets stuff

Subscribe to our mailing list and get interesting Google Sheets stuff and updates to your email inbox.

Thank you for subscribing.

Something went wrong.