When you’re entering information into your spreadsheet it’s too easy to duplicate that information. Whether you’re typing in long lists, or copying and pasting. Once there, often they need to be found and removed, so how do you deal with duplicates in Google Sheets?
What is a duplicate?
This may seem like a silly question, but let’s be clear…
In Google Sheets, a duplicate is where the exact contents of a cell appears more than once.
Everyone on the same cell, err page?
Remove Duplicates
Starting with this one, simply because it’s an easy one, as it’s built into Google Sheets.
The Set-Up
(sorry, I thought it might make you smile)
For this command to work, the list needs to be written in a column, with each piece of information in it’s own cell.
Menu Command
Highlight the cells or column.
In the Data menu, click on Data clean-up and finally from the sub-menu click on Remove duplicates.
In the dialog box, review the settings to make sure that they are right.
E.g. Heading Row/No heading row.
Note: The columns to analyse – unticking Select All also unticks, in this example, Column A.
When you’re ready click Remove duplicates.
Google Sheets will check down the column, and remove the relevant cells.
When it’s finished (and it doesn’t take long), you’ll see a results dialog box, confirming what was changed.
Note: Remember that Undo (CTRL+Z) is available. 😉
What did Google Sheets Remove?
I thought you might be interested in a comparison, so I’ve created one.
Based on the example above – here’s the before (complete list) and after (duplicates removed).
There are five rows removed:
- Christiana x 2
- Catherine
- Lara
- Angie
One thing to note, there’s Catherine and Catherine W.
Google Sheets reads that as two different options. It removed the repeats of Catherine.
This means that based on your list, it does need the content of the whole cell, to be identical for the data to be seen as duplicates.
I.e. you might need to go through the list, manually, to check for errors that are obvious to you.
What about over a number of columns?
Say you’ve got a larger list, it spans over more than one column (i.e. a range of cells), can you use this option?
Erm – no. 😅
I’ll admit that I’ve cheated a bit, but actually it turned out to be a good test.
Here are the dialog boxes, for the command and the result.
As you can see, it’s pretty much the same as a single column, although it does recognise the two columns.
You’ll notice that it found one extra duplicate – it’s the ‘Catherine W’, before I added the ‘W’.
However I was disappointed with what I saw…
I’ve included the original columns so that you can see.
Google Sheets read each column as a single ‘entity’ and only looked down the cells to find the duplicates. Which is why the two columns have ended up with identical results.
Therefore I’m going to slightly amend my earlier sentence (under the set up) to say:
‘the list needs to be written in a SINGLE column, with each piece of information in a cell’
Finding Duplicates
I’ll be honest, I thought that this bit was going to be easy. Because a spreadsheet is a spreadsheet, I figured that finding duplicates in Google Sheets would be easy as it is in MS Excel. Sadly I was wrong!
Sure, like Excel there’s a Conditional Formatting command. But unlike it, Google Sheets doesn’t have any options pre-written – like Highlight Duplicates.
Of course I can’t leave you hanging like this, so I went on a hunt to find an answer.
Everything pointed to using Conditional Formatting. (Makes sense as we want to highlight the cells).
We need to use a formula to work it out:
=COUNTIF(A:A,A1)>1
Conditional Format
You’re data is already in a spreadsheet – with each piece of information in a different cell.
Select the column.
Under the Format menu, click on Conditional Formatting.
A pane is displayed on the right-hand side with the options.
Apply to range is already filled in, because you pre-selected the column.
Note: If it isn’t, go and select the column now. And yes, you can click on the column letter to select it.
Format rules – click on the list, and scroll down to the bottom. Select Custom formula is.
Now copy and paste the formula =COUNTIF(A:A,A1)>1 (left a bigger gap to make it easier to select and copy) into the field underneath the list.
Change the column letters to the column that your list is in. And remember to set the first cell in that column.
Set the formatting option you want to use.
Note: Think about how easy (or not) it will be to highlight the cells (i.e. get a cell background colour), and still read the cell content (i.e. the text colour).
As you select your options, before you click Done. Google Sheets will be looking through your data, and automatically applying the formatting to any matching cells.
If you’re not happy, change the settings.
When you’re happy, click Done.
The Conditional Formatting Rules pane will be closed, and the duplicates remain all highlighted.
Highlighting is more useful
I’m surprised that Google doesn’t have this option pre-built in, because, let’s face it, highlighting duplicates is actually more useful than removing them.
Think it about it:-
- How many people have bought a specific product?
- What different products has a specific client bought?
- Which products have been bought on a specific day, week or month?
All of these types of questions can be answered, once your data is in a spreadsheet, by highlighting duplicates.
Next Steps
Ever wondered how to work with me? I’ve got all the answers in one place for you – the Everything Page.
Related Posts
- How to choose your favourite online spreadsheet
Unsure of which online spreadsheet program to choose? Find out here the features and benefits of both Microsoft’s Excel and Google Sheets, as well as some tips on how to pick the right one for your needs. - How to deal with duplicates in Google Sheets
Learn how to deal with duplicates: find, highlight and remove duplicates from your spreadsheet in a few easy steps. - Popular Formulas and Functions in Google Sheets
Learn about the top 10 popular formulas/functions to use with Google Sheets. How to find them and description of each purpose and examples. - How to Increase Cell Size in Google Sheets
Do you have a lot of data that doesn’t fit in your current cell size? Here’s how to increase the cell size in Google Sheets. - Merge Cells in Google Sheets – when is it useful?
Learn when to merge cells in Google Sheets, and how to do it quickly and easily. This guide will show you the benefits of merging cells and some common use cases.
Dealing with duplicates in Google Sheets, duplicates in google sheets, Dealing with duplicates in Google Sheets 2022