As one of many in style spreadsheet functions, you should utilize Google Sheets for varied duties, from information entry to information evaluation. You can even take assist of Google Sheets for information cleansing. An instance is when you’ve got duplicates of a dataset, you may make use of Google Sheet’s information cleansing capabilities to clear the dataset.
Google Sheets has totally different capabilities to determine the duplicates or filter out the duplicates. You can use both of them relying in your desire. Here are the alternative ways you possibly can spotlight and take away duplicates out of your Google Sheets:
Table of Contents
How to Highlight Duplicates in Google Sheets Using Conditional Formatting
If you solely need to determine the information duplicates in your worksheet, conditional formatting in Google Sheets’ is the best choice. Most different information cleansing capabilities in Google Sheets take away the duplicates immediately with out leaving the choice for elimination to the consumer. Here’s how one can spotlight duplicates in Google Sheets with conditional formatting.
Step 1: Open Google Sheets in your most well-liked internet browser.
Step 2: Create a brand new spreadsheet and enter your dataset or open your present spreadsheet with the dataset.
Step 3: Select the dataset you desire to to spotlight duplicates from.
Step 4: On the Ribbon, click on the Format tab.
Step 5: Click the Conditional Formatting possibility to launch a aspect panel.
Step 6: On the aspect panel, click on the ‘Format cells if’ drop-down.
Step 7: Select ‘Custom formula is.’
Step 8: Enter the next method in the Value or method discipline:
=COUNTIF( Range,Criterion)>1
Where Range refers to the dataset you need Sheets to search from and Criterion is the sample or check to apply to the dataset. Your method ought to look related to the under:
=COUNTIF(D26:D33,D26)>1
Step 9: In the Formatting fashion discipline, choose your most well-liked format.
Step 10: Click Done to save your modifications.
You ought to now see the duplicates in your dataset highlighted and take away them.
Check for Duplicates in Google Sheets Using UNIQUE
Another method you possibly can examine for duplicates in Google Sheets is through the use of the UNIQUE method. The UNIQUE method doesn’t spotlight the duplicates in your spreadsheet, however it’s going to determine the distinct datasets in your spreadsheet and reproduce them. Here’s the way it works:
Step 1: Open Google Sheets in your most well-liked internet browser.
Step 2: Create a brand new spreadsheet and enter your dataset or open your present spreadsheet with the dataset.
Step 3: Select an empty cell in the spreadsheet and sort:
=UNIQUE
Step 4: Drag over the cells or enter the cell vary you need to examine distinctive information for. You ought to have one thing like under:
=UNIQUE(D26:D33)
Step 5: Press Enter and the method ought to return the distinctive information in your dataset.
This technique is preferable when coping with a smaller dataset.
When working with a big dataset, you may make use of the Remove Duplicates software in Google Sheets to clear duplicates. Here’s how to accomplish that.
Step 1: Open Google Sheets in your most well-liked internet browser.
Step 2: Create a brand new spreadsheet and enter your dataset or open your present spreadsheet with the dataset.
Step 3: Select the dataset you desire to to take away duplicates from.
Step 4: On the Ribbon, click on the Data tab.
Step 5: Click the Data clean-up possibility.
Step 6: Select Remove duplicates. This will launch the Remove duplicates dialog field.
Step 7: Select which columns to embrace and whether or not or not the information has headers.
Step 8: Click Remove duplicates.
Google Sheets will take away duplicates and present a abstract of what was eliminated.
Converting Microsoft Excel Spreadsheet to Google Sheets
If you’ve got to collaborate on a spreadsheet with different customers inside your group, you possibly can share a hyperlink with them out of your OneDrive to allow them to entry it. However, for those who want to collaborate with customers exterior your group and with out an Office account, you should utilize Google Sheets. You can convert your Excel spreadsheet to Google Sheets and vice versa.
