Google Sheets is a powerful tool for working with data. With it, you can work on massive amount of data on a spreadsheet. The cloud application from Google is a great alternative to Microsoft Excel.
When working with large spreadsheets containing a lot of data, chances are that you can have duplicates on your sheets. This could be due to anything; your computer behaving funny, or errors made on your own part. The result of this can be miscalculations or errors which will lead you start finding the source of error.
Manually searching through a large spreadsheet can be difficult and that why you’ll want to use any of the methods listed here. Let’s get straight to the methods of removing duplicates in Google Sheets:
Using the Remove Duplicates Feature
The Remove Duplicates feature is an handy feature that lets you find duplicates in columns or even an en entire worksheet. Here’s how to use the feature:
1. Select the columns you wish to locate duplicate data in.
2. Go to the menu at the top, and select Data. From there, tap Data Cleanup > Remove Duplicates.
3. You’ll see a dialogue. Select the checkbox beside each column on the list, and hit Remove Duplicates. You can also check off Select All.
You’ll see how many copies were found and removed. The drawback of this method is that you won’t be able to review the copied items before removing them.
Using Colors to Highlight Duplicates
If you’ll want to see the errors on your spreadsheet on the go, using highlights will help you do that. Here’s how to use it on Google Sheets:
1. Launch Google Sheets and select the Column you’re interested in.
2. At the top in the menu bar, select Format. From the drop-down menu that appears, click Conditional Formatting.
3. Specify the range you are interested in from the menu that shows.
4. Go to the Format Rules section, and change the section called Format Cells if… to Custom Formula is.
5. Paste the formula below in the box below Custom formula The column name will depend on the column you actually selected on your sheet. For this example, we used the column “F”.
=countif(F:F,F1)>1
6. Go to the formatting style section and pick a fill color from the color section which will serve as your highlight color.
7. When you’re through, click Done to save changes.
This will highlight all the duplicate cells in your spreadsheet.
NOTE: Make sure you highlight cells starting from row 1 other that starting from the middle
Also ensure to check that the existing duplicates are correct. This is because some cells that look like copies are not really copies. They may be values from another user or collaborator on the sheet.
After confirming the cells you want to delete, you can then proceed to remove them.
Copy Unique Cells to Find Duplicates
If you prefer the automatic way of finding duplicates, then copying all unique cells may be best for you. This is a quick way of identifying those duplicate cells. Here’s how to use it:
1. Open Google Sheets and highlight the cells you wish to edit.
2. Next, on the sheet, select a blank area where you want all the unique values to be placed.
3. Next, paste the formula below in the formula box:
=UNIQUE()
Enter the cell area of the original column into the brackets in the formula. For example: (F1:F44).
Hit Enter and the unique data will be move to the new column
And then you will have the duplicates left and you can work with the original data.
Rounding Up…
Working with a lot of data can prove like much work, but not until you have duplicates that leads to error and you realise that there’s even more work. But it doesn’t have to be that hard; with the methods listed above, you should be able to get those duplicates quickly and easily, and save yourself a whole deal of stress. We hope you found this guide helpful? If you did, kindly leave a comment and don’t forget to share…