How To Highlight Duplicates in Google Sheets

If you are a regular Google Sheets user, you have probably run into a problem where you accidentally add duplicate entries into your spreadsheet. This situation can throw off the dataset you have been working so hard to put together.

Furthermore, you may not know that the instance occurred, especially when your PC goes haywire or when you bump the trackpad on your laptop.

It is all too easy to miss something when there is a massive amount of data in your spreadsheet. Common results include calculation errors and duplicate cells that are difficult to identify when searching for the source of the problem.

Fortunately, there are several different methods available to highlight duplicates inside of your spreadsheets. This article shows you a few different ways to find duplicates in Google Sheets and how to highlight or delete copies. you will also see a method that identifies unique cells instead.

Finding and Highlighting Duplicates in Google Sheets

Since you want Google Sheets to highlight your information automatically, using a particular formula tells Sheets to highlight individually specific information.

There are two ways to force Sheets to highlight copied information: the first highlights all duplicated information for manual confirmation, while the second one copies specific cells to a selected column, allowing you to check for differences and delete data what is not necessary.

If you are seeking to just remove duplicates in Google Sheets, you can use the built-in Remove Duplicates feature, which is Option #1 below.

Option #1: Use Google Sheets’ Remove Duplicates Feature

Whether you are trying to find duplicates in one column, two columns, or an entire worksheet, the Remove Duplicates feature gets the job done. Here is how to do it.

  1. Step 1

    Highlight the columns you want to check for duplicate data.
  2. Step 2

    Select Data in the top menu and select Remove duplicates.
  3. Step 3

    A dialogue popup will appear. Mark the boxes next to each column in the list that you want to check or mark Select All, and then click Remove duplicates.
  4. Step 4

    Sheets will tell you how many copies were found and removed so that you can be sure the process worked as intended.

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 copies before removing them.

A great way to do that is with color highlighting.

Highlight Duplicates with Color Highlighting

As far as being able to identify errors in your spreadsheets, using color highlights to spotlight any incorrect information is the best way to go.

  1. Step 1

    Open your Google Sheets file and select the column or columns you want to sort.
  2. Highlight Duplicates In Google Sheets then click Format.
  3. Select Conditional Formatting.
  4. Select the range you want from the new menu that appears.
  5. Under Format Rules, change the dropdown section titled Format cells if… to Custom formula is.
  6. Step 2

    Paste the following formula into the box under the Custom formula is option: =countif(A:A,A1)>1.
  7. Step 3

    Under the Formatting style section, select the Fill color icon to highlight your content with a yellow (or any color of your choosing) cell background.
  8. Step 4

    Click done.

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

Note: It is best to highlight a column’s cells starting with row 1 rather than choosing cells in the middle. The formula did not like using row 2 as the first column. It missed a couple of duplicates in the chosen field (A2:B9). Once row 1 was added (A1:B9), it found all duplicates. See the two images below.

Image showing missed duplicates from selecting row 2 cells as the first cells (A2 and B2):

Image showing all duplicates from selecting row 1 cells as the first cells (A1 and B1):

Make sure that any existing duplicates are correct, as some copies are not copies at all. They can be the same number for two different accounts, users, employees, or anything else. Once you confirm the duplicates you do not need, delete them or do anything you want with them. Finally, you can close the formatting menu, and then restore the standard color to your cells.

Copy Only Unique Cells in Google Sheets

Alternatively, if you would prefer to sort your raw data automatically, copying dissimilar cells instead of your duplicates can be useful for fast sorting and filtering. If you are sure that your information is correct and you would prefer to remove the copy instead, try the method below.

To copy unique Google sheets cells, do the following:

  1. Open the Sheets document you wish to sort.
  2. Highlight the column you want to edit.
  3. After highlighting a cell, click on a blank one at the top of an empty column. This step ensures that your information gets 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

There are several online plugins available for use with Google Sheets. You will find the add-ons in the Chrome Web Store, including tools for removing duplicate entries automatically.

A suitable tool known as Remove Duplicates by ablebits allows you to quickly find duplicates throughout an entire sheet of information or by searching up to two columns at once.

You can move, delete, and highlight results. The tool includes two wizard setups: find and delete duplicates and find unique cells or qualities within your document, making 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 rather spend their time doing something else.

Use a PivotTable to Find Duplicate Rows in Sheets

PivotTable is a convenient tool for looking more closely at data. A PivotTable will not automatically delete duplicate rows; it will provide a breakdown of which rows have duplicates so you can manually look at your data and see what, if anything, needs to be removed.

Creating a PivotTable is a little more involved than the other methods I have shown you in this article, but it is worth knowing how to do, and I will walk you through it.

For more information on how to use Pivot Tables, see this TechJunkie tutorial on how to create, edit, and refresh Pivot Tables in Google Sheets.

  1. Select all the table data, then go to Data->Pivot Table.
  2. Step 1

    Adjust the data range as needed then hit Create.
  3.  Select Add next to the Rows category.
  4. Step 2

    Add the row Title.
  5. Step 3

    Next to Values, select Add.
  6. Step 4

    Select Title and 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.

Note that the COUNTA column has the number of times that each title appears. There is a 1 for most of the titles, but Axis & Allies and Castle Risk both show 2.

This occurrence means that there are two instances of each of those titles in the chart. As you can see, the PivotTable method is a little more involved. Still, it gives you a specific report about the location of your duplicate entries, which can be very useful for doing data analysis.

Final Thoughts

A duplicate cell in Google Sheets can cause problems with your data if you’re 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 is positive if you are continually dealing with spreadsheets in your day-to-day workflow. If you are looking for something to sort your content better, you can always use an add-on like Remove Duplicates to make sure your information is well-sorted and well organized.

Leave a Reply

Your email address will not be published. Required fields are marked *

Disclaimer: Some pages on this site may include an affiliate link. This does not effect our editorial in any way.