MarketNews360
  • APPS
  • Tech
  • Entertainment
  • Business
  • NEWS
  • FAMILY
  • Home improvement
  • Lifestyle
  • GAMES
  • Fashion
No Result
View All Result
Plugin Install : Cart Icon need WooCommerce plugin to be installed.
  • APPS
  • Tech
  • Entertainment
  • Business
  • NEWS
  • FAMILY
  • Home improvement
  • Lifestyle
  • GAMES
  • Fashion
No Result
View All Result
MarketNews360
No Result
View All Result
Home GENERAL

How to Find Duplicate in Google Sheets

nouman khan by nouman khan
February 11, 2023
in GENERAL
36 3
0
How to Find Duplicate in Google Sheets

How to Find Duplicate in Google Sheets

32
SHARES
356
VIEWS
Share on TwitterShare on Facebook

You can easily locate duplicate values in Google Sheets through the Conditional Formatting tool. Using Conditional Formatting, Excel formats cells that match the set condition.

If you’re familiar with Excel, you may have realized there isn’t a dedicated tool as “Highlight duplicates” in Google Sheets. However, you can still use a custom formula to highlight the duplicate values in the selected cells. You could also use the Find tool to locate the duplicate values.

Table of Contents

Use COUNTIF in Conditional Formatting

For our custom formula, we will be using the COUNTIF function. The COUNTIF function is a library function that counts values that meet the set criteria.

In the function, You must pass an array and less than 1 condition as arguments. Using the COUNTIF function, the conditional formatting tool will run the array and apply the format to the cells with all repeated values.

The formula will vary slightly depending on the range you choose to pass as an array.

  1. Open your Google Sheets.
  2. Select the cells from the row you wish to apply the formatting.
  3. From the menubar, select Format > Conditional Formatting.
    Conditional Formatting GSheets
  4. In the Conditional format rules panel, drop down the list under Format cells if.
  5. Choose Custom Formula is.
    Conditional Formatting Custom Formula
  6. In the Value or Formula section, use the formula with the COUNTIF function in this format =COUNTIF(range, cell) > 1.
    Enter Custom Formula Conditional Formatting
  7. Under Formatting Style, customize the way you want the highlighted cell to look.

Example 1: Find Duplicates in Individual Columns

In the following sheet, we’re looking to find the most preferred fruit in the classroom. The row with the list of fruits is C, so our range will be C:C. The list of fruits begins from the C2 cell so we will pass that argument first.

=COUNTIF(C:C, C2) > 1

Using this formula, Sheets will apply the set formatting to cells that have repeated values.

Example 2: Find Duplicates in Multiple Columns

Find Duplicates in Gsheets

In this example, we are looking to find the most preferred lunch between four students. The most duplicated value will be the most preferred lunch among these students.

The range will start from $B:$C. The dollar sign ($) before a cell is used to create an absolute cell reference. This means even if you change the values, the location remains the same.

=COUNTIF($B:$C, B2) > 1

Sheets will run this formula in these two rows and highlight the cells with the repeated values.

Find Tool

You could use the Find Tool to highlight specific repeated values from the grid. You can simply enter the value in the tool and use the arrow keys to switch between the repeated cells. In addition to locating the repeated values by highlighting the cell, the Find tool also displays the number of times the item has been repeated.

Refer to the following steps to use the Find tool to highlight the repeated values:

  1. Open your worksheet.
  2. Use the shortcut, Ctrl + F, to open the Find tool.
  3. In the message box, enter the repeated keyword.
    Find command GSheets

Furthermore, you could configure the Find tool to perform an even more advanced search. To explore more about what the Find tool can do to locate the repeated values, follow these steps:

  1. From the Find tool pop-up, select the three-dot menu.
    Advanced Find Tool GSheets
  2. In the Search section, select the drop-down menu to choose where you wish the Find tool to run.
  3. Select the boxes for the additional settings you prefer.
    Find Settings GSheets
Tags: Conditional FormattingfunctionIndividual Columnsrepeated values.
Tweet8Share13Share3Share
Previous Post

Business Buildings for Sale: Investing in Commercial Real Estate

Next Post

Navigating the THCV Wholesale Market

nouman khan

nouman khan

Next Post
Navigating the THCV Wholesale Market

Navigating the THCV Wholesale Market

Discussion about this post

Youtube Channel

Currently Playing

Follow Our Page

Popular Post

    About Us

    Email Us :
    [email protected]

    Follow Us

    Category

    • APPS
    • BEAUTY
    • Behavior
    • Boxer
    • Bulldog
    • Business
    • Culture
    • Entertainment
    • FAMILY
    • Fashion
    • GAMES
    • GENERAL
    • German Shepherd
    • Golden Retriever
    • HEALTH
    • Home improvement
    • Labrador Retriever
    • Lifestyle
    • NEWS
    • Research
    • Rottweiler
    • Sports
    • Tech
    • Tips
    • Training
    • Uncategorized
    • Wellness

    Recent News

    Ghislaine Maxwell: The Life and Scandal of Jeffrey Epstein's Former Partner

    Ghislaine Maxwell: The Life and Scandal of Jeffrey Epstein’s Former Partner

    March 27, 2023
    Understanding the Concept of Functional Medicine

    Understanding the Concept of Functional Medicine

    March 25, 2023

    © Copyright 2022 - Market News 360. All Rights Reserved..

    No Result
    View All Result
    • About
    • Advertise
    • Privacy & Policy
    • Contact

    © Copyright 2022 - Market News 360. All Rights Reserved..

    Welcome Back!

    Login to your account below

    Forgotten Password?

    Retrieve your password

    Please enter your username or email address to reset your password.

    Log In