You are reading the article How To Highlight Duplicates In Google Sheets? updated in September 2023 on the website Chivangcangda.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 How To Highlight Duplicates In Google Sheets?
In this article, we are going to show you how to highlight duplicates in Google Sheets in the following ways:
Highlight duplicate cells in a single column in Google Sheets.
Highlight the entire row if duplicates are in one Google Sheets column.
Highlight duplicate cells in multiple Google Sheets columns.
Highlight actual duplicates, leaving the 1st instance.
Highlight complete row duplicates in Google Sheets.
How to Highlight Duplicates in Google Sheets?We are going to use the COUNTIF function to highlight duplicates in Google Sheets. The COUNTIF function counts across columns, row-by-row. The syntax of the COUNTIF function is:
f(x)=COUNTIF(range, criterion)
Where range refers to the cell range on which the function needs to be applied and criterion refers to the condition which needs to be met. The COUNTIF function returns TRUE or FALSE based on the match.
Now let’s see how the formula works in Google Sheets.
1] Highlight duplicate cells in a single column in Google SheetsSelect Custom formula is under the Format rules dropdown and enter the COUNTIF function in the value or formula textbox.
We have used the following formula for the above dataset:
By default, the cells will be highlighted in a shade close to light blue. You can select custom colors under the Formatting style option to highlight cells in the color of your choice. We have highlighted duplicates in yellow color in our example.
2] Highlight the entire row if duplicates are in one Google Sheets columnUsing the same formula, we can highlight the entire row if duplicates are in one Google Sheets column. The only change here would be the selected range (Apply to range). We have selected A1:C10 here, so conditional formatting will highlight the entire row, instead of highlighting individual cells.
Also Read: How to count checkboxes in Google Sheets.
3] Highlight duplicate cells in multiple Google Sheets columnsWe can adjust the same formula to highlight duplicate cells in multiple Google Sheets columns. We are making the following 2 changes to the formula:
Modifying the range to cover all data columns.
Removing the $ sign from the criterion.
After removing the $ sign, the formula will count cells from all the columns, including columns A, B, C, and so on. For the dataset shown in the above image, the formula will be:
4] Highlight actual duplicates, leaving the 1st instanceAgain, we can use the same formula to highlight actual duplicates by ignoring the 1st instance. For this, we need to lock the column in the end range, but not the row. In this arrangement, each row will look for duplicates in its above rows only.
For the dataset shown in the above example, we have used the following formula:
5] Highlight complete row duplicates in Google SheetsWe can use ArrayFormula with COUNTIF to highlight complete row duplicates in Google Sheets. ArrayFormula concatenates the data in multiple columns in a single string before applying the COUNTIF rule.
So for the above dataset, the function will be:
This sums up different ways of highlighting duplicate cells in Google Sheets. Hope you find this useful.
How do I find duplicates in Google Sheets?Google Sheets offers flexible ways to find, review or fix duplicate data. For example, you can highlight a single cell carrying the duplicate value, or the entire row of data if there’s a duplicate in a particular column. Duplicates can be highlighted based on a condition and conditional formatting rules. The condition may be defined using a custom formula, such as COUNTIF. Refer to the above article to learn how to use the COUNTIF function in Google Sheets.
How do I highlight duplicates in columns? How do I edit a rule in conditional formatting?Follow the steps to edit a rule in conditional formatting in Google Sheets:
Select the cell range on which the rule has been applied.
Edit the formula or formatting style as required.
Read Next: How to install Custom Fonts on Google Docs.
You're reading How To Highlight Duplicates In Google Sheets?
Update the detailed information about How To Highlight Duplicates In Google Sheets? on the Chivangcangda.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!