If you’re a regular Google Sheets user, you’ve probably run into an issue where you accidentally added duplicate entries to your spreadsheet. This situation can disrupt the data set that you have worked so hard to put together. You may not know that the instance occurred, especially when your PC goes haywire or when you hit the trackpad on your laptop.
In any case, it is very easy to miss something when there is a large amount of data in your spreadsheet. Typical results include calculation errors and duplicate cells that are difficult to identify when looking for the source of the problem.
Fortunately, there are several different methods available to highlight duplicates within your spreadsheets.
- Use the built-in deduplication feature.
- Use highlighting to find duplicates.
- Copy single cells and move them to a new sheet.
- Use a third-party duplicate finder.
- Create a pivot table that counts duplicates individually.
The above processes make it easy to find those duplicate entries so that you can remove or ignore them if they match but are not duplicates. Here are your options.
Use the Remove Duplicates feature from Google Sheets
Whether you are trying to find duplicates in one column, two columns, or an entire worksheet, the Remove Duplicates feature accurately removes cells with the same data. Nevertheless, note that it removes all duplicates, even if they are not associated with the same data.
- Highlight the columns that you want to check for duplicate data.
- From the menu at the top, select Dataand then choose Remove duplicates.
- A dialog box will pop up. Check the boxes next to each column in the list that you want to check, or you can check Select all, and then click Remove duplicates.
- Google Sheets shows how many copies were found and deleted so you can make sure the process worked as expected.
Using Google Sheets’ built-in “Find and Remove Duplicates” feature is the easiest way to remove duplicates, but sometimes you may want to review the copies before removing them. A great way to do this is by highlighting the color.
Highlight duplicates using colors for easy removal
When it comes to identifying errors in your spreadsheets, using highlights to highlight any incorrect information is another good way to go.
- Open your Google Sheets file and select the column or columns you want to sort.
- In the menu bar at the top, select Format.
- In the drop-down menu, select Conditional format.
- Select the range you want from the new menu that appears.
- Below Format rules, change the dropdown section titled Format cells if … for The custom formula is.
- Paste the following formula in the box below the The custom formula is option:
- Under the Format style section, select the Fill color icon to highlight your content with a yellow cell background (or whatever color you choose).
- Click on Done to save the changes.
Your spreadsheet will now highlight your duplicate cells in the color you chose, and you can scan the selection for duplicates.
Note: It is better to highlight cells in a column that start with row 1 rather than picking cells in the middle. The formula doesn’t like to use row 2 as the first column. A couple of duplicates were missing from the selected field (A2: B9). Once row 1 (A1: B9) was added, it found all duplicates. See the two images below.
Image # 1: Show lost duplicates when selecting cells from row 2 as the first cells (A2 and B2):
Image # 2: Show all duplicates when selecting cells in row 1 as the first cells (A1 and B1):
Make sure the existing duplicates are correct, as some copies are not copies at all. They can be the same number for two accounts, users, employees, or anything else. Once you confirm the mock cells that you don’t need, delete them. Finally, you can close the format menu and restore the standard color in your cells.
Copy single cells to Google Sheets to easily remove duplicates
If you prefer to sort your raw data automatically, it is better to copy all unique cells instead of duplicates. This process offers faster sorting and filtering. If you are sure your information is correct and you prefer to remove duplicates, please try the next method.
- Open the Spreadsheet document you want to sort and highlight the column you want to edit. This process will record the rank of the column for the next step.
- After highlighting a column, click on a blank one where you want the unique entries to appear. Paste the following formula into the formula input box at the top of the document:
- Type the cell coordinates of the original column inside the parentheses, like: (
- Catch Get into to move your new data to the column you designated earlier.
Once this is complete, you can check the entries manually or import your data into your worksheet.
Use a third party plugin to find and remove duplicates in sheets
Online plugins are available for use with Google Sheets. You will find the accessories in the Google Workspace Marketplace, including tools to automatically remove duplicate entries.
Remove duplicates by Ablebits
Whatuseful tool known as Remove duplicates by Ablebits allows you to quickly find duplicates across an entire data sheet or by searching up to two columns at a time.
You can move, delete, and highlight results. The tool includes two wizard settings: find and remove duplicates and find unique cells or qualities within your document. These two options make it easy to keep track of information on the go.
Overall, using a wizard tool to find your information could pay off in the long run. This statement is primarily for users who continually search for duplicates in their spreadsheets and prefer to spend their time elsewhere.
Use a pivot table to find duplicate rows in sheets
A pivot table is a convenient tool for taking a closer look at your data. A pivot table does not automatically delete duplicate cells or rows; provides a breakdown of the columns that have duplicates so you can manually view your data and see what, if anything, you need to remove.
Creating a pivot table is a bit more complicated than the other methods shown in this article. You must add a pivot table for each column to ensure consistent results and accurately identify duplicates.
Note: If you don’t have column names in your spreadsheet, pivot tables won’t work accurately to find duplicates. Try adding a new row temporarily and then name the columns using that new row.
Here are the steps to use a pivot table to identify duplicate cells or rows.
- Select all the data in the table, then go to Data> Pivot Table.
- Adjust the cell range if necessary, then press Create.
- Please select Add next to Rows. This step will choose the column to search for duplicates. Yes, you read that right. Select the column you want from the drop-down menu. If you lose the pivot table editor, click a populated cell to retrieve it.
- Now select Add next to Values and choose the same column as above, but set it to summarize by TELL O COUNTA. It should already be the default.
- The new pivot table will identify the duplicates, as shown below.
- If you I want to see a different column, you can redo the steps above (to preserve your previous results) or reopen the pivot table editor and then change the existing table.
- The pivot table will change to show the new settings.
As mentioned above, the pivot table method is a bit more complicated. Still, it gives you a specific report on the location of your duplicate entries, which can be very useful for data analysis. For more information on using pivot tables, see the TechJunkie tutorial at create, edit, and update pivot tables in Google Sheets.
In general, a duplicate cell in Google Sheets can cause problems with your data if you’re not careful, especially when trying to sort financial information in a useful spreadsheet.
The most common causes of duplicates are:
- Multiple people added the same customer, invoice, category, item, etc.
- The data imports are added to the data already present a second time.
- Copy / paste actions added duplicate entries.
Fortunately, identifying, deleting, and deleting identical data cells is surprisingly easy in Google Sheets, which is a good thing if you’re continually dealing with spreadsheets in your daily workflow. If you are looking for something to better order your content, you can always use a plugin like Ablebits Remove Duplicates to make sure your information is neat and organized.
Do you have any experiences, tips, or questions about using any of the methods mentioned in this article? Let us know in the comment section below.