Okay, so you've got a spreadsheet full of data that you need to review. And maybe there are some duplicates hiding in there. Don't worry, we've all been there. While the formula and process you use differs from what you would’ve done in Excel, you’re essentially doing the same thing. One can also argue that it’s easier to do in Sheets. In any case, here’s the straightforward method to highlight duplicates in Google Sheets.

Step 1: Select Your Targets

First, pick the range of cells you want to check. It could be a whole column or just a specific section. Whatever it is, highlight that area.

Step 2: Go into Conditional Formatting 

Head up to the menu bar and find "Format". Click on it, then choose "Conditional formatting". This is where you’ll have to create a formula.

Step 3: Create Your Custom Rule

You're going to create a custom formula to find those duplicates. Here's the formula you need:

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

Replace the $A:$A part with the range you selected in step 1. Don't worry about changing the A1 part.

Step 4: Choose Your Highlight

Click on the paint bucket icon and select any color you like. It's your spreadsheet; as long as it’s not a striking color that makes it hard to read, any color should be fine. 

Step 5: Hit Done, and Voila!

Click "Done," and your duplicates should be highlighted.

Other Things You Can Do When Highlighting Duplicates in Google Sheets

Now that you know how to spot and find those duplicates, are they really just so you can find and remove them? Here are possible steps you can take after you find the duplicates in your spreadsheet:

• Investigate the Duplicates: Why do you have duplicates? Is it a data entry error? A system glitch? Figure out the root cause to prevent future duplications.
• Consolidate Your Data: If you have multiple entries for the same thing, consider combining them into a single row. This can clean up your data and make it easier to analyze.
• Create a Filter: Want to focus on just the duplicates or the unique values? Use the filter function to quickly isolate what you need.
• Make Your Sheet More Comprehensive: Get creative with your conditional formatting! You can change font styles, add icons, or even create data bars to highlight duplicates differently.

Extra Tips and Tricks

• Multiple Columns: Want to check for duplicates across multiple columns? No problem. Just adjust the formula to cover the entire range. For example, if you want to check columns A, B, and C, use =COUNTIF($A:$C, A1:C1)>1.
• Highlighting the First Duplicate: If you only want to highlight the first occurrence of a duplicate, you can use a more complex formula, but we'll save that for the next part of the blog.
• Remove Duplicates: Once you've found your duplicates, you might want to remove them. Google Sheets has a built-in function for that, but we'll talk about that in another post.

Other Functions and Formulas Better Than COUNTIF

While COUNTIF is a valuable tool for identifying duplicate values, it's just one piece of the puzzle. Google Sheets has a massive suite of functions that can be combined to perform more complex data analysis. Here are some additional formulas that you can use that may work better than COUNTIF:

COUNTIFS: Counting with Multiple Conditions

When you need to count based on more than one criteria, COUNTIFS comes in handy. Keep in mind that COUNTIF and COUNTIFs are two different functions.

=COUNTIFS(range1, criteria1, range2, criteria2, ...)

For example, to count the number of orders from a specific customer that were placed in a particular month, you might use:

=COUNTIFS(A:A, "Customer A", B:B, ">="&DATE(2024,1,1), B:B, "<="&DATE(2024,1,31))

SUMIF: Summing Based on Criteria

If you need to calculate a sum based on specific conditions, SUMIF is your go-to function. It adds up values in a range that meet certain criteria.

=SUMIF(range, criteria, sum_range)

For instance, to calculate the total sales for a particular product, you could use:

=SUMIF(B:B, "Product X", C:C)

SUMPRODUCT: Versatile Calculations

SUMPRODUCT has greater flexibility than SUMIF, allowing for calculations across multiple arrays. It's useful if you want conditional summing and counting scenarios.

=SUMPRODUCT((condition1)(condition2)...)

For example, to count the number of cells in range A1:A10 that contain the text "apple" and have a corresponding value in range B1:B10 greater than 5, you would use:

=SUMPRODUCT((A1:A10="apple")*(B1:B10>5))

FREQUENCY: Grouping Data

FREQUENCY helps you categorize data into ranges or bins. Think of it as creating groups based on specific criteria.

=FREQUENCY(data_array, bins_array)

For instance, to count the number of values in range A1:A10 that fall into specific bins (e.g., 0-20, 21-40, 41-60), you would use:

=FREQUENCY(A1:A10, {0,20,40,60})

OFFSET and MATCH: Dynamic Range References

OFFSET and MATCH work together to create flexible ranges. This dynamic duo can be especially helpful when your data is constantly changing.

=OFFSET(reference, rows, cols, [height], [width])

or

=MATCH(lookup_value, lookup_array, [match_type])

For example, to count the number of occurrences of a value in a range that starts at a specific cell and extends for a variable number of rows, you could use a combination of OFFSET, MATCH, and COUNTIF.

Conclusion

That’s how easy it is to highlight duplicates in Google Sheets. And if COUNTIF alone wasn't enough, we've shown you other formulas that could definitely work better in certain scenarios with far better results. 

If you would like to learn more about Excel functions and formulas, we have a comprehensive blog about it, too. We went from medium-level formulas to advanced-level functions that you can use daily.