Remove Duplicates & Spaces in Sheets

Google Sheets has new features that allow you to very easily remove duplicates and extra spaces from cells without using a formula or script. It’s pretty awesome!

Remove Duplicates

We’ll start by taking a look at the ‘remove duplicates’ feature. Removing duplicates hasn’t been that hard up to this point, but now it’s super simple. In my spreadsheet I have a list of zip codes, cities and states. A few of them are duplicated. I’ve bolded those ones to make it easier to see in the image.

Highlight the column with duplicates. In the menu, click on Data > Remove Duplicates and a box will pop up with some options.

The first option allows you to expand your selection to include all columns with data in them.

Below that is a checkbox that tells Sheets if you have a header row so it won’t remove any titles if they were duplicated below.

Lastly, you can uncheck any columns that you don’t want to include for duplicate removal.

After clicking on the “Remove Duplicates” button, you will be presented with a summary of what was removed. Click on OK to see that your sheet no longer has duplicates. That’s a pretty awesome feature!

Trim Whitespace

Now let’s take a look at the other new feature that allows us to easily remove extra spaces from cells.

Sometimes when we copy data from a website, or some other source, and paste it into a spreadsheet we’ll see it come in with extra spaces. In this example there are extra spaces before some of the zip codes, and after some of the states.

Usually we would have to write a formula to remove those spaces and clean up the spreadsheet. Not anymore!

Start by highlighting the cells with extra spaces. Then go back to the Data menu. Below Remove Duplicates there is an option called Trim Whitespace.

Clicking on that option will automatically remove all of those extra spaces.

That may not be a big deal for everyone, but I know there are some people who are going to save a lot of time because of this!

So that’s it! Two new features that will make your spreadsheet life just that much better!

I hope you found this article to be helpful. Comment and let me know what you think.

Learn more about Google’s amazing office apps with the Google Cloud Productivity course on Udemy.

Watch the video

Watch the video for removing duplicates and trim whitespace.