How to Find Duplicate in Google Sheets

Many people want to know how to Find Duplicate in Google Sheets. Google Sheets is used by many people to set up data tables. When Google’s spreadsheet app is used to set up large tables, there are times when columns and rows contain duplicate numbers or text. Large data tables can make it slow and tedious to look for duplicated data to remove by hand. We need a way to highlight duplicated data in a Google Sheets spreadsheet so that it is easier to find. But Sheets doesn’t come with a built-in way to mark duplicates. Even so, you can still use custom formulas and a Power Tools add-on to highlight duplicates in Google Sheets.

The Conditional Formatting tool in Google Sheets makes it easy to find duplicate values. With Conditional Formatting, Excel changes the look of cells that meet the set criteria. If you know Excel, you may have noticed that there is no “Highlight duplicates” tool in Google Sheets. But you can still use a custom formula to make the duplicate values in the chosen cells stand out. You could also use the Find tool to find the values that are used more than once. We mentioned below are the steps how to Find Duplicate in Google Sheets.

How to highlight duplicates in Google Sheets

Google Sheets does have a way to find and get rid of duplicate data in your spreadsheets, but it’s best to start by highlighting the data you want to look at so it’s easy to see where the duplicates are. In this step, you should choose to highlight the column(s) you want to look at.

Make sure you have the Editor permissions you need to make changes before you start. For files you made yourself, that will always be the case. Also, make sure that the spreadsheet has headings above each column and that all the data is set up in columns.

1. Open the Google Sheets file you’d like to look at.
2. Mark the cell or column whose data you want to look at.
3. Sort the text in a large file by putting it in alphabetical order, either going up or down.
4. Choose “Conditional formatting” from the “Format” tab.
5. On the right, a drop-down menu will show up. Make sure it’s the right range of cells (if it’s not, you can change it yourself).
6. Choose “Custom formula is” from the “Format cells if…” drop-down menu.
7. In the value formula, type (=countif(A:A,A1)>1), but change the second and third letters to match the column you want to use. It is (A:C,C1) in this case.
8. In the “Formatting Style” section, choose the highlight color you want to use and click “Done.”
9. Now, the chosen column should show up with your chosen color used to highlight any duplicate text.

How to Find Duplicate in Google Sheets

Formulas are one way to use Google Sheets to find duplicates. A “duplicate” is a word or phrase that is found more than once in the chosen column.

1. You can choose to highlight the column you want to look at by following the steps above or by clicking the “fill color” tab.
2. Click on a blank cell, preferably near your data on the right.
3. Type the formula (=UNIQUE) into the cell, and then add the cell coordinates where you saw duplicates. It is =UNIQUE (B11:B13) in this case.
4. Hit the “Enter” button on your keyboard. Now, all duplicate information should be in that cell or column.
5. If a duplicate is found in the highlighted column, it will be filled in the chosen cell. Duplicates from other columns will be listed below. In this case, ‘Before‘ is the extra duplicate.

How to Compare Duplicates in Google Sheets

1. Open the Google Sheets spreadsheet that contains the data you want to compare.
2. Select the data you want to compare.
3. Go to the Data menu and select Duplicates.
4. In the Duplicates dialog box, select the Compare sheets option.
5. In the Select sheets to compare section, select the sheets that you want to compare.
6. In the Select columns to compare section, select the columns that you want to compare.
7. Click OK.

How to remove duplicates in Google Sheets

When you find duplicate information, you might want to get rid of it. Before moving on to the next steps, it’s best to save the original file and then save the edited file as a copy with a different name after the duplicates have been removed. This way, if you need to, you can always go back to the original.

1. Choose the range of cells to check to get rid of duplicates.
2. Click “Remove duplicates” in the “Data” tab.

Importance of Removing Duplicates

1. Data Accuracy: Removing duplicates ensures that the data is accurate and free from any redundant or repeated information. Duplicate entries can lead to misleading analysis and incorrect conclusions.
2. Save Storage Space: Duplicate data occupies unnecessary storage space, especially in large datasets. Removing duplicates helps optimize storage and allows for efficient data management.
3. Enhance Data Analysis: When duplicates are present, it can skew statistical analysis and reporting. By eliminating duplicates, analysts can work with a cleaner dataset, leading to more reliable insights.
4. Improved Decision Making: Duplicate data can cause confusion and lead to erroneous decisions. By eliminating duplicates, decision-makers can rely on accurate and consistent information.
5. Efficient Data Processing: Removing duplicates streamlines data processing tasks, as there is less redundant information to handle. This can result in faster and more efficient data operations.
6. Prevent Data Inconsistency: Duplicate entries may have different values, which can create data inconsistency issues. Removing duplicates ensures data consistency and coherence.

Questions and Answers