Google Sheets, similar to Microsoft Excel, provides users with a wide range of features and functionalities, making the tool very versatile for various tasks. One of these features is the ability to automatically highlight duplicate entries. It’s a handy feature for anyone working with large datasets or for those who need to verify data input. This blog post will walk you through the steps on how to have Google Sheets highlight duplicates for you.
Step 1: Open Your Google Sheets Document
Head to Google Sheets and open the spreadsheet you want to work on. Ensure that you’ve logged in with your Google account.
Step 2: Select the Range of Cells to Check
Click and drag your mouse across the cells you want to check for duplicates. You can select a range of cells, a whole column, a whole row, or even the entire sheet. It will depend on where you want Google Sheets to look for duplicates.
Step 3: Go to the Format Menu
After selecting the range, navigate to the top menu and click on the Format option. This will open a dropdown menu with a variety of formatting options.
Step 4: Click on Conditional Formatting
In the dropdown menu, click on the Conditional formatting option. This will open the Conditional format rules sidebar on the right side of the screen.
Step 5: Create a New Rule
In the Conditional format rules sidebar, click on the “+” button to add a new rule. This will open a panel with various options for creating a new rule.
Step 6: Format Cells If…
Under the Format cells if… dropdown, select Custom formula is.
Step 7: Enter the Formula
In the formula field, you need to enter the formula that checks for duplicates. The formula we’re going to use is the COUNTIF function. This function counts the number of cells within a range that meet the given condition.
The formula you’ll enter in the field will look like this:
=COUNTIF(A:A,A1)>1
This formula tells Google Sheets to count the cells in column A that match the value in cell A1. If the count is greater than 1, then there are duplicates, and Google Sheets will apply the formatting you specify.
Step 8: Format Your Duplicates
Next, you’ll need to specify the formatting you want to apply to the duplicates. Under the Formatting style section, you can choose a fill color, text color, and other formatting to apply to the duplicates. For example, you could choose to fill duplicates with a red color to make them stand out.
Step 9: Click ‘Done’
Finally, after setting up your rule, click the Done button. Google Sheets will now highlight any duplicates in your selected range according to the formatting you’ve specified.
With these easy steps, you can now efficiently and effectively spot duplicates in your Google Sheets. Happy data handling!