Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • How to use Excel FORMULATEXT function
  • Left Lookup in Excel
  • VLOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel VLOOKUP Function
  • Find Closest Match in Excel Using INDEX, MATCH, ABS and MIN functions

Data Analysis Examples

  • Calculate Conditional Percentile ‘IF’ in table in Excel
  • Add Outline to Data in Excel
  • Use Data Form to input, edit and delete records in Excel
  • How to Create Column Chart in Excel
  • How to create dynamic reference table name in Excel

Data Validation Examples

  • Excel Data validation exists in list
  • Excel Data validation require unique number
  • Excel Data validation don’t exceed total
  • Excel Data validation date in next 30 days
  • Excel Data validation must contain specific text

Only Find Duplicates in Excel

by

This example teaches you how to find duplicates (or triplicates) in Excel.

Sometimes duplicate data is useful, sometimes it just makes it harder to understand your data.

When you just want to identify but not remove duplicate;

Use conditional formatting to find and highlight duplicate data. That way you can review the duplicates and decide if you want to remove them.

Select the cells you want to check for duplicates.

1. Select the range A1:C10.

Worked Example:   Highlight rows that contain in Excel

2. On the Home tab, in the Styles group, click Conditional Formatting.

3. Click Highlight Cells Rules, Duplicate Values.

4. Select a formatting style and click OK.

Result. Excel highlights the duplicate names.

Note: select Unique from the first drop-down list to highlight the unique names.

As you can see, Excel highlights duplicates (Juliet, Delta), triplicates (Sierra), quadruplicates (if we have any), etc. Execute the following steps to highlight triplicates only.

5. First, clear the previous conditional formatting rule.

Worked Example:   Highlight missing values in Excel

6. Select the range A1:C10.

7. On the Home tab, in the Styles group, click Conditional Formatting.

8. Click New Rule.

9. Select ‘Use a formula to determine which cells to format’.

10. Enter the formula =COUNTIF($A$1:$C$10,A1)=3

11. Select a formatting style and click OK.

Result. Excel highlights the triplicate names.

Explanation: =COUNTIF($A$1:$C$10,A1) counts the number of names in the range A1:C10 that are equal to the name in cell A1. If COUNTIF($A$1:$C$10,A1) = 3, Excel formats the cell. Because we selected the range A1:C10 before we clicked on Conditional Formatting, Excel automatically copies the formula to the other cells. Thus, cell A2 contains the formula =COUNTIF($A$1:$C$10,A2)=3, cell A3 =COUNTIF($A$1:$C$10,A3)=3, etc. Notice how we created an absolute reference ($A$1:$C$10) to fix this reference.

Worked Example:   Conditional Formatting Rules in Excel

Note: you can use any formula you like. For example, use this formula =COUNTIF($A$1:$C$10,A1)>3 to highlight the names that occur more than 3 times.

Post navigation

Previous Post:

How To Load Analysis ToolPak in Excel

Next Post:

How To Create Drop-down List in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • Join Text Strings Using Concatenate or ‘& Operator’ in Excel
  • How to Separate Text Strings in Excel
  • TEXT function: Description, Usage, Syntax, Examples and Explanation
  • EXACT function: Description, Usage, Syntax, Examples and Explanation
  • Remove file extension from filename in Excel
  • Find Last Day of the Month in Excel
  • How to calculate nth day of year in Excel
  • Count dates in current month in Excel
  • Get day from date in Excel
  • How to calculate future date say 6 months ahead in Excel
  • ACCRINT function: Description, Usage, Syntax, Examples and Explanation
  • AMORLINC function: Description, Usage, Syntax, Examples and Explanation
  • RRI function: Description, Usage, Syntax, Examples and Explanation
  • Calculate interest rate for loan in Excel
  • Calculate loan interest in given year in Excel
Acronyms, Abbreviations, Initialism & What They Stand For
© 2022 xlsoffice . All Right Reserved. | Teal Smiles