Update: Please check the new menu item Format > Remove Duplicates – How to Use Remove Duplicates Menu Command in Google Sheets.īelow you can find a few different custom formulas based on COUNTIF and COUNTIFS to highlight duplicates in Google Sheets. The use of UNIQUE or SORTN may not be handy in highlighting duplicates. But surprisingly, there is one more function called SORTN.īut for conditional formatting, like in Excel, we can depend on the functions COUNTIF or COUNTIFS. You may be familiar with one of them, and that’s UNIQUE. Google Sheets has two main functions to eliminate duplicates. The efficient way to identify duplicates in Google Sheets is by highlighting cells using the conditional formatting feature, which is built-in.īut to get the maximum output from this built-in conditional formatting feature, you should know how to use custom formulas in it.Īs a side note, the best option to eliminate duplicate values is to use formulas. All the duplicates in two or more columns except for the first occurrence/instance.All the occurrences of the duplicates in two (or more) columns.Formula to Highlight Duplicates in Multiple Columns in Google Sheets. All the occurrences, except for the first instance.Ģ.All the occurrences of the duplicate values in a single column.Formula to Highlight Duplicates in a Single Column in Google Sheets. This post covers the below conditional formatting tips.ġ. So for the former, we can use COUNTIF and the latter, the Format menu > Conditional formatting. Then apply the fill color or text color based on the count. The shortest answer is to count values in a column(s) as we do in running count. in rangesīy highlighting duplicates in your ranges you’ll be better able to warn users if they have accidentally typed a value that needs to be unique.How to Highlight Duplicates in Google Sheets? =COUNTIF(INDIRECT("A1:Z1"), INDIRECT("RC", FALSE)) + COUNTIF(INDIRECT("C3:F5"), INDIRECT("RC", FALSE)) > 1Īnd this formula being pasted in each custom formula’s conditional formatting range (i.e. Representing the repeated formula again for each contiguous range. Is the range you want to check for duplicates and the =COUNTIF(INDIRECT("range"), INDIRECT("RC",FALSE)) +. If you want to highlight duplicates in non-contiguous ranges modify the formula slightly by making the formula: Matches the highlighted range applied for the conditional formatting. To highlight cells that are duplicated in a range, use the conditional formatting custom formula functionality with the formula Multiple times per unique range and set conditional formatting on each range.Īs you can see from the result, the highlighted cells which can be found in both the top row and the rectangular ranges are highlighted. To highlight duplicates in non-adjacent ranges use the Here’s the result of applying the above formula to the two ranges which are not connected or joined (I’ve highlighted the entire sheet to show that conditional formatting custom formulas will be needed in both the ranges): =COUNTIF(INDIRECT("topRow"),INDIRECT("RC",FALSE)) +ĬOUNTIF(INDIRECT("rect"),INDIRECT("RC",FALSE)) > 1 You could make it easier by naming the ranges, like so: =COUNTIF(INDIRECT("A1:Z1"),INDIRECT("RC",FALSE)) +ĬOUNTIF(INDIRECT("C3:F5"),INDIRECT("RC",FALSE)) > 1Įach range you would want to check again would need to be defined in its ownįunction, with every range wrapped in the first parameter with The same formula is used, however, for each range, you would need to perform one slight change: you need to useĪnd see if both ranges contained duplicates my new formula for checking these ranges would now look like this: How can you highlight duplicates in ranges that are not joining? Highlight Duplicates In Multiple Non-Contiguous Ranges Using the same formula and concept can apply to rows tooĬan the same formula work with multiple non-joining ranges?
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |