Wednesday, March 29, 2023
HomeWINDOWSHOW-TOHow to highlight duplicates in Google Sheets?

How to highlight duplicates in Google Sheets?

-


It’s not unusual to find duplicates when working with spreadsheets containing large datasets. Even if it’s not necessary to remove every duplicate, manually finding them for review can be a task in itself. Excel provides an easy way to find duplicates with conditional formatting. Google Sheets currently does not provide such an opportunity; however, it is still possible to highlight duplicates using a custom formula and some conditional formatting rules.

In this article, we will show you how to highlight duplicates in Google Sheets in the following ways:

  1. Highlight duplicate cells in the same column in Google Sheets.
  2. Highlight the entire row if there are duplicates in one Google Sheets column.
  3. Highlight repeating cells in multiple Google Sheets columns.
  4. Highlight the actual duplicates, leaving the 1st instance.
  5. Highlight full duplicate rows in Google Sheets.

How to highlight duplicates in Google Sheets?

We are going to use the COUNTIF function to highlight duplicates in Google Sheets. COUNTIF counts by columns, line by line. Syntax of the COUNTIF function:

f(x)=COUNTIF (range, criterion)

Where range refers to the range of cells to which the function is to be applied, and criterion refers to the condition to be met. The COUNTIF function returns TRUE or FALSE depending on the match.

Now let’s see how the formula works in Google Sheets.

1]Highlight duplicate cells in the same column in Google Sheets.

Let’s say we have a spreadsheet with the names written in it. The sheet contains several duplicate name entries, which we are going to find and highlight using the COUNTIF function and conditional formatting in Google Sheets.

Open spreadsheet you would like to work. Press Format over the spreadsheet. Choose Conditional formatting.

How to highlight duplicates in Google Sheets?

You will see the conditional formatting rules on the right side of the screen. Click the icon next to Apply to range and use your mouse to select data range. For the dataset shown in the image above, we have set the range to A1:A10.

Selecting a Data Range in Google Sheets

Choose custom formula located in the Format Rules section and enter the COUNTIF function into a value or formula.

Defining a custom formula in Google Sheets

We used the following formula for the above dataset:

f(x)=COUNTIF ($A$1:$A$10,$A1)>1

In this formula, the condition is specified as $A1. The $ sign locks the column/row and tells the formula to only count cells from the specified column/row. So the $ sign here indicates that the condition is based on column A only. This will select a cell value from column A (A1, A2, etc.), match it against all other cell values ​​in column A, and return True if the match is found. By adding >1, we further highlight all instances of duplicates found in column A.

By default, the cells will be highlighted with a shade close to blue. You can choose custom colors in the Format Style section to highlight the cells with the color of your choice. In our example, we highlighted the duplicates in yellow.

Highlight duplicate cells in one Google Sheets column

Press Done to close the Conditional Formatting Rules panel.

2]Highlight the entire row if the duplicates are in the same Google Sheets column.

Highlight entire row if duplicates are in same column Google Sheets

Using the same formula, we can highlight the entire row if there are duplicates in one Google Sheets column. The only change here would be the selected range (Apply to range). We chose A1:C10 here, so the conditional formatting will highlight the entire row, not individual cells.

3]Highlight duplicate cells in multiple Google Sheets columns.

Highlight duplicate cells in multiple Google Sheets columns

We can tweak the same formula to highlight duplicate cells in multiple Google Sheets columns. We make the following 2 changes to the formula:

  1. Change the range to cover all columns of data.
  2. Remove the $ sign from the criterion.

After removing the $ sign, the formula will count cells from all columns, including columns A, B, C, and so on. For the dataset shown in the image above, the formula would look like this:

f(x)=COUNTIF ($A$1:$C$5,A1)>1

4]Highlight the actual duplicates leaving the 1st instance

Highlight actual duplicates, leaving the first instance

Again, we can use the same formula to highlight the actual duplicates, ignoring the 1st instance. To do this, we need to lock the column at the end of the range, but not the row. With this arrangement, each line will only look for duplicates in its previous lines.

For the dataset shown in the example above, we used the following formula:

f(x)=COUNTIF ($A$1:$A1,$A1)>1

5]Highlight complete duplicate rows in Google Sheets.

Highlight complete duplicate rows in Google Sheets

We can use ArrayFormula with COUNTIF to highlight complete duplicate rows in Google Sheets. ArrayFormula concatenates data from multiple columns into one row before applying the COUNTIF rule.

So, for the above data set, the function would be:

f(x)=COUNTIF (ArrayFormula ($A$1:$A$5&$B$1:$B$5&$C$1:$C$5),$A1&$B1&$C1)>1

This summarizes the different ways to highlight duplicate cells in Google Sheets. Hope you find it useful.

How to find duplicates in Google Sheets?

Google Sheets offers flexible ways to find, view, or correct duplicate data. For example, you can highlight a single cell containing a duplicate value, or an entire row of data if there is a duplicate in a particular column. Duplicates can be highlighted based on a condition and conditional formatting rules. The condition can be defined using a custom formula such as COUNTIF . Refer to the article above for how to use the COUNTIF function in Google Sheets.

How to highlight duplicates in columns?

Let’s say we have a spreadsheet that contains data in the cell range A1:C5. To highlight duplicates in multiple columns in this dataset, we can use the COUNTIF function, like so:

f(x)=COUNTIF ($A$1:$C$5,A1)>1.

This will select a cell in the column, compare it to the rest of the cells, and return TRUE if a match is found. The comparison will occur for each column in the dataset.

How to edit a conditional formatting rule?

Follow the instructions for editing a conditional formatting rule in Google Sheets:

  1. Choose cell range to which the rule is applied.
  2. go to Format > Conditional formatting.
  3. Press rule in the conditional formatting rule on the right side of the table.
  4. Edit the formula or formatting style as needed.
  5. Press Done.

To apply another rule to the same range, you can use the “Add another rule” option. To delete a rule, you can click on the trash can next to the rule.

How to Highlight Duplicates in Google Sheets

Above is the information on how to highlight duplicates in Google Sheets?. If you have any questions please ask useguide.net in the comment below. I’m happy to help you.



Source link

windows789.com

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular