How to highlight a word in google sheets? Filtering duplicate data in Google Sheets is an important method for effective work management. In this article, we will learn how to filter duplicate data in Google Sheets.
This guide will help you perform on laptops running Windows 10 operating system and using Cốc Cốc browser. You can also apply the same steps on other laptops running the same operating system or on MacOS laptops with similar operations.
How to highlight a word in google sheets? Benefits of filtering duplicate data
Filtering duplicate data is a process of identifying and removing duplicate values from a data set. This can be done for a variety of reasons, such as to save storage space, improve performance, or improve accuracy.
Benefits of filtering duplicate data:
- Save storage space: Duplicate data can take up a lot of storage space, especially in large data sets. Filtering duplicate data can help to free up space on your computer or storage device.
- Improve performance: Duplicate data can slow down your computer or storage device when accessing or processing data. Filtering duplicate data can help to improve performance by reducing the amount of data that needs to be processed.
- Improve accuracy: Duplicate data can lead to errors and inaccuracies in your analysis. Filtering duplicate data can help to improve accuracy by ensuring that you are only working with unique data.
How to highlight a word in google sheets? 5 ways to filter duplicates in Google Sheets
Highlight (find) duplicate data
Quick guide:
- Select the data range in the spreadsheet > Click Format > Click Conditional formatting to display the Conditional formatting rules dialog box.
- In the Conditional formatting rules dialog box that just appears > Select the Apply to range > In the Custom format rule section, select Custom formula is > Enter the formula > Select the Formatting style > Click Done.
Detailed guide:
Step 1: Select the data range in the spreadsheet > Click Format > Click Conditional formatting to display the Conditional formatting rules dialog box.
Step 2: In the Conditional formatting rules dialog box that just appears > Select the Apply to range > In the Custom format rule section, select Custom formula is > Enter the formula =COUNTIF(A:A;A1)>1 as shown > Select the Formatting style > Click Done.
Notes:
- If the “;” is not correct for your machine, please change it to a comma.
- Change the letter A to the letter of the column you are formatting in your file.
Step 3: The result is that you can easily see the duplicate data.
Filter out duplicate or non-duplicate data
Step 1: Select the cell next to the data you want to find duplicate data > Enter the formula =countif(A:A;A1)=1 As shown.
Step 2: Press Enter and drag the formula down to the end of the entire column, as shown.
Step 3: To filter the TRUE data to get the non-duplicate results. You insert an additional row above as shown.
Step 4: Select the row you just inserted > Select Data > Select Filter as shown.
Step 5: Select the filter icon you just initialized > Uncheck the FALSE item > Select OK, as shown.
Step 6: The result obtained when keeping the non-duplicate value TRUE. You can do the same with the FALSE value.
Filter out duplicate data using the UNIQUE function
Step 1: Select any cell in the data table > Enter the function =UNIQUE(A2:B8) as shown.
Step 2: Press Enter > The result is displayed as shown.
If you want, you can copy and paste into another data table.
Choose to paste only values (Paste values only) or the keyboard shortcut Ctrl + Shift + V to paste to avoid the case where the result is the copied formula.
Filter out data that appears for the second time or more
Step 1: In the data table > Enter the function =IF(COUNTIF($A$2:A2;A2)=1;A2;”Duplicate result”) as shown in the cell next to the data you want to filter.
Step 2: Press Enter and drag the formula down to the end of the entire column, as shown.
Step 3: You initialize the filter by > Select the first row, row A1 > Select Data > Select Filter.
Step 4: The result after filtering.
Filter duplicate data using the utility tool
Step 1: Select Add-ons > Select Install add-ons
Step 2: Search for the keyword “remove duplicates” and select the add-on by the author EXPAND9 as shown. Because I think it’s the best and easiest to use.
Step 3: Click the Install button.
Step 4: Click the Continue button > Select your Gmail > Allow > Finish.
Step 5: Go back to the Google Sheet page with duplicate data > Select Add-ons > Select Remove Duplicates > Select Start to start filtering duplicate data.
Step 6: Click the Select columns (Select columns) button > Click or uncheck the columns that contain the data you want to filter > Select Save (Save).
Step 7: Highlight or delete duplicate data in Google Sheet. If you leave Highlight > click Search, you will see the rows of data that are duplicated for the second time or more are highlighted in yellow as shown.
Here are easy ways to filter duplicate data in Google Sheets. I hope this guide will help you be more productive at work and study. If you have any feedback, please leave a comment below. Don’t forget to share if you find this guide helpful.
