highlight duplicates in google sheets in colums

Interested in strengthening your abilities to work with data using Google Sheets? Enroll in the Google Data Analytics Professional Certificate. In that case, you’d want to do a pivot table, which can help you see and better understand the relationship between data. But if you have multiple duplicates, you won't be able to see how many of each duplicate you have. Performing the steps we’ve outlined above will highlight your duplicates using one color. How to highlight multiple columns using different colors You’ll need to frame every cell with a “$.” Our function becomes =COUNTIF ($B$1:$C$50, B1)>1. Use absolute values.Ībsolute values are a way to specify where Sheets should look for duplicates with the “$” symbol. For example, if we want to look at columns C through F now, we’ll update “Apply to range” to “C1:F999” and then make sure the function reads =COUNTIF(C:C, C1)>1. Make sure the syntax of your formula matches the first value. Rather than clear the conditional formatting, highlight your new columns, and start over, you can simply update the “Apply to range” to read “B1:F999.” Let’s say you were looking at columns B and C, but now you want to include columns B through F. You can adjust the range in Apply to range as needed. That tells Sheets to start with B1 and go from there. Use 'Apply to range.'īy highlighting the columns you want to check, you’ll automatically tell Apply to range what to concentrate on, but you’ll have to adjust your custom formula to start with the value of that first column and first row.įor our purposes, we’re looking at columns B and C, so our function should be =COUNTIF(B:B, B1)>1. There are now two ways to go about this: 1. Clear any previous conditional format rules, and repeat the steps above until you get to the box where you’ll input your custom formula. We’ve purposely added an error in the titles column, repeating Batman twice. Let’s say you want to check movie titles and directors, so columns B and C in this case. Now that you know how to count duplicates in one column, let’s talk about how to adjust the process to count duplicates in multiple columns. How to count duplicates in multiple columns You can see how it begins to highlight repeat directors. Our formula should become =COUNTIF (C:C, C2)>1. Since we’re looking for duplicate directors, we want to adjust the formula to read the C column. The information outside the parentheses states that you want Sheets to count duplicates, or anything appearing more than once (>1). The information in the parentheses represents the column you want to track and the specific cell you want to start with. The COUNTIF formula tells Sheets where to look for duplicates.

Use the COUNTIF formula to find duplicates. In the 'Format cells if' box, click 'Custom formula is.'ĥ. Learn more: How to Use Conditional Formatting in Google Sheets 4. You’ll see a prompt called “Format cells if…” Click on that and scroll to the bottom. This will populate a box on the right-hand side of the screen. Using our practice sheet, let’s see if there are any duplicates in the Director (1) column.

Highlight the column you want to find duplicates in.

Highlight duplicates in google sheets in colums