How to Easily Identify and Remove Duplicates in Google Sheets

If you’ve accidentally entered the same information multiple times in your Google Sheets spreadsheet or are simply looking for a quick way to find and highlight cells with identical content, Google Sheets offers a convenient solution. Let’s explore how you can effortlessly identify and remove duplicates in Google Sheets with just a few simple steps.

Using the Conditional Formatting Tool

Step 1: First, select the cells you want to search for duplicates. You can choose specific cells, a column, or even entire rows. For the purpose of this demonstration, let’s focus on the A column.

Selecting a column in Google Sheets to highlight duplicate data

Step 2: Next, click on the “Format” tab and choose “Conditional formatting.”

Conditional formatting tab in Google Sheets

Step 3: In the “Conditional format rules” section, you’ll notice that the “Apply to range” field is already populated with the column you’ve selected (in our case, column A). You can manually change this field if necessary.

Apply to range box for highlighting duplicate data

Step 4: Now, click on the “Format cells if” dropdown menu and select “Custom formula.”

Format cells if dropdown menu and Custom formula option

Step 5: In the “Value or formula” box, enter the following formula: =countif(A:A,A1)>1. Note that if you’ve selected cells starting from a different position, such as A1 or A2, you should still use the same formula.

Entering a custom formula to highlight duplicate data

Step 6: Click “Done” to apply the formatting. Now, any duplicates in the A column will be highlighted.

Google Sheets also provides additional customization options, allowing you to make the duplicates bold or change their color.

Formatting style section for customizing highlighted duplicate data

Step 7: If you no longer want to see the duplicates highlighted, simply click the “Remove rule” button represented by the trash icon.

Trash icon button for removing highlighted duplicate data

Deleting Duplicates in Google Sheets

If you come across duplicate entries in your spreadsheet that you want to remove, Google Sheets offers a convenient “Remove duplicates” tool.

See also  How to Recover Lost Word Documents Easily

Step 1: Select the column containing the duplicates (e.g., column A).

Step 2: Navigate to the “Data” tab, then click on “Data cleanup” and choose “Remove duplicates.”

Remove duplicates tool in Google Sheets

Step 3: Click the “Remove duplicates” button. If you’ve applied the highlight duplicates formula to other columns, such as column B, and you want to remove those duplicates as well, you can select the “Expand to A:B” option before clicking the “Remove duplicates” button.

Remove duplicates button in Google Sheets

In addition to these helpful features, if you’re a new Google Sheets user, we recommend checking out our guides on searching and merging cells in Google Sheets for more useful tips and tricks.

Editors’ Recommendations

Related Posts

MacBook Pro 16: A Comprehensive Buying Guide

Are you considering purchasing the Apple MacBook Pro 16, but not sure which model suits your needs? Look no further! In this buying guide, we will walk you…

The Top Slack Issues and Their Solutions

The Top Slack Issues and Their Solutions

Even if you’re well-versed in the best Slack hacks and tips, you may still encounter some occasional hiccups with the platform. Whether you’re working from home or relying…

How to Appear Offline on Discord

How to Appear Offline on Discord

Discord is a popular platform known for its seamless communication features, allowing users to effortlessly connect with various groups and servers. Whether you prefer text or voice chatting,…

How to Boost Your Productivity with Windows Keyboard Shortcuts

Are you tired of constantly right-clicking just to perform simple tasks like cut, copy, and paste? It may not be overly time-consuming, but it can become quite annoying…

The Thinnest Laptops: Slim, Powerful, and Portable

Introduction The world of laptops has seen incredible advancements over the years, and one area that has seen significant improvement is the slimness of these devices. Today’s thinnest…

How to Create an Out-of-Office Reply in Microsoft Outlook

How to Create an Out-of-Office Reply in Microsoft Outlook

If you’re planning to take some time off work for a vacation or leave of absence, it’s helpful to set up an out-of-office reply in Microsoft Outlook. This…