糖心vlog官网观看

When dealing with a lot of data, you may sometimes want to double-check and make sure no duplicates exist鈥攐r, conversely, you may want to highlight any duplicates to call attention to them. In that case, you can use a straightforward formula in Google Sheets. Learn how to find duplicates in Google Sheets with step-by-step instructions.

Practice with Google Sheets: Highlight duplicates

To begin, you'll need your tab open to your spreadsheet. If you鈥檙e not already working with your own data set and want to follow along with our examples, make a copy of to practice.听

How to highlight duplicates in Google Sheets

Highlighting duplicates in Google Sheets requires conditional formatting using the custom formula =COUNTIF (A:A, A1)>1. Follow these steps to learn how to use it.听听

How to find duplicates in Google Sheets with a browser add-on

TIP: If you鈥檇 rather not dive into formulas just yet, you can download an from Google Sheets that will find and highlight duplicates for you.

1. Highlight the column you want to find duplicates in.

Using our practice sheet, see if the Director (1) column has any duplicates.听

2. Click 'Format' in the top menu.

3. Click 'Conditional formatting.'

This will populate a box on the right-hand side of the screen. You鈥檒l see a prompt called 鈥淔ormat cells if鈥︹�� Click on that and scroll to the bottom.

Learn more: How to Use Conditional Formatting in Google Sheets

4. In the 'Format cells if' box, click 'Custom formula is.'

5. Use the COUNTIF formula to find duplicates.

The COUNTIF formula [=COUNTIF (A:A, A1)>1] tells Sheets where to look for duplicates. The information in the parentheses represents the column you want to track and the specific cell you want to start with. The information outside the parentheses states that you want Sheets to count duplicates or anything appearing more than once (>1).听

Since you're looking for duplicate directors, you want to adjust the formula to read the C column. Your formula should become =COUNTIF (C:C, C2)>1. You can see how it begins to highlight repeat directors.听

How to count duplicates in multiple columns

Now that you know how to count duplicates in one column, learn how to adjust the process to count duplicates in multiple columns next.

Let鈥檚 say you want to check movie titles and directors, so columns B and C in this case. We鈥檝e purposely added an error in the titles column, repeating Batman twice. Clear any previous conditional format rules, and repeat the steps above until you get to the box where you鈥檒l input your custom formula.听

You can go about this in two ways:听

1. Use 'Apply to range.'

By highlighting the columns you want to check, you鈥檒l automatically tell Apply to range what to concentrate on, but you鈥檒l have to adjust your custom formula to start with the value of that first column and first row.听

For your purposes, you鈥檙e looking at columns B and C, so your function should be =COUNTIF(B:B, B1)>1. That tells Sheets to start with B1 and go from there.听

You can adjust the range in Apply to range as needed. Let鈥檚 say you were looking at columns B and C, but now you want to include columns B through F. Rather than clear the conditional formatting, highlight your new columns, and start over, you can simply update the 鈥淎pply to range鈥� to read 鈥淏1:F999.鈥澨�

Make sure the syntax of your formula matches the first value. For example, if you want to look at columns C through F now, you鈥檒l update 鈥淎pply to range鈥� to 鈥淐1:F999鈥� and then make sure the function reads =COUNTIF(C:C, C1)>1.听

2. Use absolute values.听

Absolute values are a way to specify where Sheets should look for duplicates with the 鈥�$鈥� symbol. You鈥檒l need to frame every cell with a 鈥�$.鈥� Our function becomes =COUNTIF ($B$1:$C$50, B1)>1.听

Learn more: Google Sheets vs. Excel: What's the Difference?听

How to highlight multiple columns using different colors

Performing these steps will highlight your duplicates using one color. But if you have multiple duplicates, you won't be able to see how many of each duplicate you have.听

In that case, you鈥檇 want to do a pivot table, which can help you see and better understand the relationship between data.

Explore further how to work with data.

Highlighting duplicates in Google Sheets is one way to filter and clean your data. Interested in strengthening your abilities to work with data using Google Sheets? Enroll in the Google Data Analytics Professional Certificate. You鈥檒l learn more about spreadsheets and other key analysis tools.听

Build job-ready skills with 糖心vlog官网观看 Plus

Start 7-day free trial

• 
• 
• 
• 

Start 7-day free trial