Working with your data on Google Sheets has become reliable especially when you need to save your spreadsheets on a cloud. It became an alternative to Excel yet, there are some limitations that you can encounter along the way.
For large data on spreadsheets, it is not impossible to run in problems like duplicating entries. Working on increasing data will also increase the likelihood of duplicates, which increase risk especially when dealing with important documents. It can lead to miscalculations and false data entry.
However, there are ways on how you can find duplicates in Google Sheets even with a wide spreadsheet on hand. Check out the methods below:
Highlight Duplicates With Color Highlighting
- Open your Google Sheets and select the spreadsheet you want to edit.
- You can select one column or multiple columns. Click on the columns you want to check.
- Next, go to the toolbar and click Format to open a drop-down menu.
- Select Conditional Formatting.
- On the sidebar, look for Format cell if… Then click on the drop down menu.
- Choose Custom formula is.
- Type =COUNTIF(A:A,A1)>1 in the box to highlight all the cells in the A column from the first cell to the last. If you want to highlight a different column, simple change A:A and A1 to the column you want to highlight. For instance, =COUNTIF(D:D,D1)>1 will highlight column D. Another note to consider is the first cell you want to begin with. If you want to begin with A6 in the column, simply change A1 to A6. For instance,=COUNTIF(A:A,A6)>1
- Once you are finished, click DONE and it will automatically highlight all the duplicate cells in your selected column.
Find Duplicates Using Pivot Table
Another helpful feature of Google Sheet is the Pivot Table. It will only show you the duplicates in your spreadsheet but you have to manually remove them. This is safer for sheets with entries that are the same.
- Select the data you want to highlight.
- Go to the toolbar and click Data.
- Choose Pivot Table. An editor will pop-up.
- Edit the options given by clicking the Add button. Then, adjust the options based on how you want it.
- Once done, the Pivot Table will update the sheet and give you the results. It will show you the number of instances where the data appears in the spreadsheet.
How to Remove Duplicates
Google’s Remove Duplicates Feature is an easy way to quickly remove any duplicate on your spreadsheet. Take note that there are data that should have the same entries so best to check and review these duplicates first before removing them.
- Select the columns you want to check for duplicates.
- Go to the toolbar and click Data. A drop down menu will appear.
- Click Remove Duplicates.
- On the pop-up window, check Data has a header row if the columns you want to highlight has a header. Check the boxes of the columns you want to analyze or you can choose Select all.
- Click Remove Duplicates. A confirmation will appear indicating the number of duplicates found and the unique rows remaining.
Minimize Your Errors
Removing duplicates on your Google Sheets helps in reducing errors on important documents. It makes your data entries more accurate and precise. Did you find the methods helpful? Tell us in the comment section below.
