Learn How to Highlight and Remove Duplicates in Google Sheets

Popular

RBI asks HDFC Bank to stop digital launches and credit card...

The Reserve Bank of India (RBI) has asked the country's largest private sector lender, HDFC bank to stop all launches of its digital business...

Assam’s Dibrugarh University Teacher Arrested For Sharing Porn Video: Police

During interrogation, the accused confessed to the crime. (Representational)Dibrugarh: An assistant professor in Assam's Dibrugarh University was arrested today for allegedly uploading pornographic content...

Process to avail full refund on Lockdown Air Tickets

The Supreme Court approved the Directorate General of Civil Aviation's (DGCA) proposal on air tickets refund and credit shell on flights booked prior and...

Why was CDS Gen. Bipin Rawat’s attending China-owned MG Motor event;...

US Ambassador Kenneth Juster greets CDS Gen Bipin Rawat with elbow bump | Watch ...

Explained: How new H-1B visa regime will impact Indians, Indian firms

In yet another policy stance change on H-1B visa within six months, the US administration on October 6 said it was announcing an “interim...

While working in Google Sheets, you may come across large spreadsheets where you have to deal with many duplicate entries. We understand the pain of dealing with duplicates and how difficult it would get if you were to highlight and remove entries one by one. However, with the help of conditional formatting, highlighting and removing duplicates becomes pretty easy. Follow this guide as we tell you how to find and remove duplicate entries in Google Sheets.

Google Sheets: How to highlight duplicates in a single column

Before knowing how to remove duplicate entries from Google Sheets, let’s learn how to highlight duplicates in a single column. Follow these steps.

  1. Open your spreadsheet in Google Sheets and select a column.
  2. For instance, select column A > Format > Conditional formatting.
  3. Under Format rules, open the drop-down list and select Custom formula is.
  4. Enter the Value for the custom formula, =countif(A1:A,A1)>1.
  5. Below Format rules, you can find Formatting styles, which lets you set a different colour for highlighted duplicates. To do that, hit the Fill colour icon and select your preferred shade.
  6. Once you’re finished, hit Done to highlight the duplicates in a single column.
  7. Similarly, if you have to do it for column C, the formula becomes, =countif(C1:C,C1)>1 and you do it so on for other columns as well.

Besides, there’s a way to find duplicates in the middle of the columns too. To learn, follow these steps.

  1. Let’s assume you wish to highlight duplicates between cells C5 to C14.
  2. In that case, go to Format and select Conditional Formatting.
  3. Under Apply to range, enter the data range, C5:C14.
  4. Next, under Format rules, open the drop-down list and select Custom formula is.
  5. Enter the Value for the custom formula, =countif(C5:C,C5)>1.
  6. Set a different colour for the highlighted duplicates by following the earlier steps if you wish to. Once you’re finished, hit Done.
  7. Set a different colour for the highlighted duplicates by following the earlier steps if you wish to. Once you’re finished, hit Done.

Google Sheets: How to highlight duplicates across multiple columns

Just in case if you want to highlight duplicates across multiple columns and rows, follow these steps.

  1. Open your spreadsheet in Google Sheets and select multiple columns.
  2. For instance, select columns from B to E > click Format > click Conditional formatting.
  3. Under Format rules, open the drop-down list and select Custom formula is.
  4. Enter the Value for the custom formula, =countif(B1:E,B1)>1.
  5. Set a different colour for the highlighted duplicates by following the earlier steps if you wish to. Once you’re finished, hit Done.
  6. Similarly, if you want to select the duplicates for column M to P, then you replace B1 with M1 and E with P. The new formula becomes, =countif(M1:P,M1)>1.
  7. Besides, if you want to highlight duplicates for all the columns from A to Z, simply repeat the earlier steps and enter the Value for the custom formula, =countif(A1:Z,A1)>1.

Google Sheets: Remove duplicates from a spreadsheet

After you’re done highlighting duplicate entries in your spreadsheet, the next step is to delete them. Follow these steps.

  1. Select a column from where you want to remove the duplicates.
  2. Click Data > Remove duplicates.
  3. You will now see a pop-up. Tick the box next to Data has header now > click Remove duplicates > click Done.
  4. You can repeat the steps for other columns as well.

This is how you can highlight and remove duplicates in Google Sheets.

For more tutorials, visit our How to section.


Are iPhone 12 mini, HomePod mini the Perfect Apple Devices for India? We discussed this on Orbital, our weekly technology podcast, which you can subscribe to via Apple Podcasts, Google Podcasts, or RSS, download the episode, or just hit the play button below.

For the latest tech news and reviews, follow Gadgets 360 on Twitter, Facebook, and Google News. For the latest videos on gadgets and tech, subscribe to our YouTube channel.

Aman Rashid

Google Messages SMS Categories Feature Is Rolling Out to More Users

Related Stories

Source link

Latest News

Assets under NPS rose to 38% YoY till March 2021: PFRDA

The Pension Fund Regulatory and...

Charges collected on digital transactions in zero-balance accounts have been refunded: SBI clarifies

CBDT had advised Banks to refund the charges collected, if any, on transactions carried out using the digital mode.State Bank of India (SBI) has...

Wipro Q4 Results: Net Profit Rises 28% To Rs 2,972 Crore, Revenue Up 3.9%

<!-- -->Wipro Q4 Results: The company's revenue stood at Rs 16,334 croreIT services company Wipro on Thursday reported its consolidated profit at Rs 2,972 crore...

Amazon announces $250 million fund for SMEs

The company will help build brand new businesses. E-commerce giant Amazon on Thursday announced a $250 million (around ₹1,873 crore) fund that will focus on...

Wipro Gains Ahead Of Fourth Quarter Earnings Announcement

<!-- -->Wipro Share Price Today: Shares were last trading at Rs 2.70 per cent higher at Rs 429.95 on BSEShare price of software services...