Google Sheets is one of the most powerful Google products and one of the most popular spreadsheet and data management solutions on the web. However, it has its weak points, which we will discuss in this article.
Fortunately, Sheets supports custom functions and formulas that help overcome some of these shortcomings. If users are good at formulas or a little scripting, Sheets can be as powerful as possible.
Google Sheets“/>
One special feature that Sheets doesn’t natively support is the ability to find duplicates in Google Sheets – be it duplicate data in cells or rows. While there is a way to quickly remove duplicate records using the Remove Duplicates option in the Data menu, you cannot isolate just those duplicates. The good news is there is a way to do this.
In this article, we will look at how to find duplicates in Google Sheets using conditional formatting.
What is conditional formatting in Google Sheets?
Conditional formatting is a feature found in many spreadsheet editors that allows the user to apply text and other visual formatting to a cell based on certain conditions.
For example, a user might record their monthly budget using a spreadsheet, wanting to know immediately if they are over the $ 500 spending limit. In the column of the spreadsheet that calculates the total for the month, a conditional formatting rule can be set for rows in bold that are worth more than $ 500, making it easy to see where this happened.
– /
Google Sheets“/>
In Google Sheets, conditional formatting allows users to apply formatting based on a long list of predefined rules or by entering their own formula. Available formatting style changes include bold, italic, underline, strikethrough, text color, and cell color.
In addition, users can use a color scale instead of a single color and define the minimum and maximum values ??and their corresponding colors to automatically scale the color.
How to use conditional formatting to find duplicate rows in Google Sheets
For anyone working with a long sheet of data, duplicate records can be a problem that sometimes disrupts the flow of things. Users might not just want to delete this data, which is natively maintained in Sheets, but instead parse and work with it.
Google Sheets“/>
This is possible using a custom conditional formatting formula. The first step to achieving this is to select the entire column in which you want to search for duplicates by clicking on the column label above it.
Google Sheets“/>
Once highlighted, right-click and select Conditional Formatting. A panel will appear on the right side of the window that shows the conditional formatting rules. The selected column range will be pre-populated in the Apply to Range box, so it does not need to be touched.
Then you want to click the dropdown menu under Format Rules to change this condition to Custom Formula. After that, a text box will appear under the drop-down menu, in which you must enter the following formula:
= countif (A: A, A1)> 1
Google Sheets“/>
However, it will need to be modified to replace all instances of “A” with the letter of the column being searched for in Google Sheets, which will appear in the “Apply to Range” box.
Google Sheets“/>
The final step is to set up formatting to apply to all cells in this row that contain data found more than once. When working with a large dataset, the fill color may be the best format for customization. With it, a bright and visible color such as yellow, hot pink or lime green really stands out and allows you to immediately notice duplicates when scrolling.
Google Sheets“/>
Now all rows containing data found in at least one other row will be highlighted or formatted in any way you choose. If you remove duplicate rows, the conditional formatting of the remaining single row will be removed automatically.
Someone might ask: “Why is this necessary if Google supports deleting duplicates in two simple clicks?” Well, this system is inflexible and imperfect. For example, it is not possible to change the deleted duplicate. Some users might want to delete the first instance and others might want the last – Google doesn’t provide any options and removes all duplicates except the very first.
Using this simple rule of conditional formatting, the user has complete control over the duplicates so as not to damage their sheet. Best of all, conditional formatting is completely dynamic, so you never have to remove formatting if you decide to remove all duplicates in any way.
Google Sheets“/>
However, if you ever need to remove a conditional formatting rule, it’s as easy as clicking the Format menu and then Conditional Formatting, which displays the same menu on the right as before. Users can hover over the line that displays the formula they just created and click the trash can icon that appears to delete it.
When Google Sheets‘ built-in duplicate finding feature to remove duplicates is simply not efficient enough, or encrypts data in a way that is unaffordable, the simple custom formula highlighted in this article allows users to take matters into their own hands. / p>
Do you have any questions or comments about using this conditional formatting rule? If so, please leave us a comment below.
–