How to mark duplicates in Google Sheets | Scribe

    How to mark duplicates in Google Sheets

    • |
    • 12 steps |
    • 19 seconds
    information ordinal icon
    This Scribe shows you how to visibly mark duplicates in a single column in Google Sheets using conditional formatting. The same logic came be applied to multiple columns i.e. entire datasets
    1
    Navigate to your spreadsheet
    2
    Select the second cell in the column where you want to mark duplicates. If your column does not have a column header, select the first cell
    3
    Highlight the entire column of data: For PCs, press [[ctrl]] + [[shift]] + [[down]]. For Macs, press [[cmd]] + [[shift]] + [[down]]
    4
    Click "Format" and then "Conditional formatting"
    Click "Format" and then "Conditional formatting"
    5
    Open up the "Format cells if..." field and click "Custom formula is"
    6
    Click the "Value or formula" field.
    7
    Type "=countif("
    8
    Click this field to copy it by pressing [[cmd]] + [[c]]
    9
    Return to the "Value or formula" field and paste in the range via [[cmd]] + [[v]]
    10
    Make sure to lock the data set. Do this manually typing in the [[$]] in front of both the letter and number of each cell
    information ordinal icon
    The final formula for this example is: =countif($A$2:$A$8,$A2)>1
    11
    Click "Done"
    12
    Now you'll see the duplicates highlighted in your specified color