How to Highlight Duplicates in Google Sheets

Sheets cannot do everything that Excel can do, but odds are you do not need it to do everything Excel can do. If Sheets does everything you need it to do, then you do not need to buy the Excel. Well, in this article, we are going to talk about How to Highlight Duplicates in Google Sheets. Let’s begin!

But, Sheets does still have some limitations, and some things that are trivial in Excel are a little more difficult to make work in Sheets.

You have probably run into a problem where you accidentally add duplicate entries to your spreadsheet. After all, the more data you add, the more likely you are to accidentally insert duplicated data into a spreadsheet. That can throw off the dataset you have been working so hard to put together.

Since spreadsheets can be lengthy documents. It becomes difficult to spot and remove duplicates, leading to a calculation error in your work. Without an obvious source for where the problem is coming from.

Fortunately, we have found a lot of different methods to highlight duplicates inside of Google Sheets. In this article, I’m going to show you a few different ways to find duplicates in Google Sheets, then highlight or delete copies.

Find and Highlight Duplicates in Google Sheets

Since we want Google Sheets to highlight our information for us automatically, we’ll be using a formula to tell Sheets to bring forward and highlight specific, unique information.

There are actually two ways to force Sheets to highlight copied information: the first highlights all duplicated information for manual confirmation, while the second will copy unique cells to a selected column, allowing you to check for differences and delete where necessary.

If you are just seeking to find duplicates then remove them, you can use the built-in Remove Duplicates feature in Sheets.

Use Sheets’ Remove Duplicates Features

Whether you are trying to find duplicates in one column, two columns, or a whole worksheet, the Remove Duplicates feature can get the job done.

First, highlight the columns you want to check for duplicate data, then select Remove Duplicates from the Data drop-down menu.

Next, a dialogue box will pop up. Check “Data has header row” if there’s a row with headers for the columns, make sure the checkbox is checked next to columns you want to check or choose “Select All,” then click Remove Duplicates.

Third, confirm the remove duplicates by reviewing how many copies “now found and removed” and how many records remained after Sheets removed the duplicates.

Using Google Sheets’ built-in Find and Remove duplicates feature is the most straightforward way to get rid of duplicates, but sometimes you might want to review the duplicates before removing them.

A great way to do that is with color highlighting.

Highlight Sheets Duplicate With Color Highlighting

As far as being able to identify errors in your spreadsheets goes, using color highlights to spotlight any information that has been inputted incorrectly is the best way to go. By highlighting information, it is easy to identify the mistakes very quickly. Since you can simply run down the list of content that you need to recognize.

1. Open your Sheets file.
2. Select the column or columns you want to sort.
3. Select Format from the toolbar.
4. Then select Conditional Formatting.
5. Select the range you from the new menu that appears.
6. Change Format cells if to Custom formula is in the drop-down menu and paste the following formula into the box below the menu: =countif(A:A,A1)>1.
7. Change the formatting style to highlight your content with a yellow (or any color of your choosing) cell background.
8. Click done.

Your spreadsheet will now highlight your duplicate cells in red, and you can also scan the selection for any duplicates.

Make sure that any existing duplicates are correct, then delete the ones that are not or whatever else you wanted to do with duplicates. Finally, you can close the formatting menu, and you can restore the standard color to your cells.

Copy Only Unique Cells in Google Sheets

Alternatively, if you’d rather automatically sort your raw data, copying unique cells instead of your duplicate cells can be useful for fast sorting and filtering. If you are sure that your information is correct and you’d rather just outright remove the duplicate information you do not need. You should try this method instead.

To do this, follow these steps:

1. Open the Sheets document you wish to sort.
2. Highlight the column you want to edit.
3. After highlighting a cell, click on an empty cell at the top of an empty column to make sure that your information is moved to the side of the chart.
4. Paste the following formula in the formula input box at the top of the document: =UNIQUE(). Type the cell coordinates inside the parenthesis (e.g. (A1:A75)).
5. Hit enter to move your new data to the column you designated earlier.

Once this is complete, you can either check manually or import your data into your working spreadsheet.

Use an Add-on to Find and Remove Duplicates in Sheets

We should note that there are many plugins available for use with Google Sheets online via the Chrome Web Store. That includes a tool for removing duplicate data entries automatically from the document.

Remove Duplicates is an aptly-named tool offered by developer ablebits. That allows you to quickly find duplicates throughout an entire sheet of information or by searching up to two columns all at once.

You can move, delete, and highlight results, just as you can with the formula tasks above, although this process is far more automated than we have seen previously. So keep that in mind if you want more control over the process. The tool includes two wizard setups that allow you to both find and deletes duplicates. Or unique qualities from your document. That makes it easy to track the information as you go.

Overall, using a wizard tool to find your information might be worth it in the long-run for users. Who are continually looking for duplicates in their spreadsheets and would understandably rather spend their time doing something else. But users who only need to check once or twice every few months may be better off just using the formula tools above to identify their information manually.

Use the Remove Duplicates Tool to Find and Delete Duplicate Cells in Google Sheets

Remember, I talked above about how Google keeps rolling out new features to add to the power of Sheets?

Well, since this article was initially written. They have actually added a full-featured Remove Duplicates tool to the core package. It is a straightforward tool to use, and I will walk you through it.

Let uss take a typical spreadsheet, with a list of board games, and if you read carefully, you’ll see that I’ve put a couple of duplicates into the list.

To use the tool, all we need to do is select the data area where we want to de-duplicate. Note that we can choose rows or columns freely; if we include the Price column, then the Remove Duplicates function will look at both Title and Price to decide if a row is a duplicate or not.

Once the data area is selected, go to Data->Remove Duplicates. Accept the default values in the dialogs, and voila – the duplicates are removed automatically.

Use a Pivotable to find Duplicate Rows in Sheets

Sheets implements a full set of PivotTable functionality, which is a convenient tool for looking more closely at data. Using a PivotTable will not automatically delete the duplicate rows; rather, it will provide a breakdown of which rows have duplicates, so you can manually look at your data and see what, if anything, has gone awry.

Creating a PivotTable is a little more involved than the other methods I have shown you in this article, but it’s worth knowing how to do, and I will walk you through it. We’ll use a pivot table, a table that summarizes data from other tables, to get this done.

First, select all the table data, and then go to Data->Pivot Table.

When we hit “Create,” a blank PivotTable will open at the insertion point…but a blank PivotTable does not do us any good. We have to tell it which information to analyze.

We’re going to select “Add” next to Rows, and add the row “Title.” Then under Values, we’re going to select “Add” and select “Title” again, then accept the COUNTA function as the default.

As soon as we make these selections, the PivotTable automatically updates, and now we can see the final result.

FINAL THOUGHTS

In order to Keep a duplicate cell in your files can cause some serious problems with your data. If you are not careful, especially when trying to sort financial information into a useful spreadsheet.

Luckily, identifying, removing, and deleting identical cells of data is surprisingly easy in Google Sheets, something that’s a positive if you’re constantly dealing with spreadsheets in your day-to-day workflow.

And if you are looking for something that makes it a little easier to sort your content. You can always use an add-on like Remove Duplicates to make sure your information is well-sorted and well organized.

Conclusion

Also See:User Guide On How To Delete DoorDash Credit Card