Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Randomize/ Shuffle List in Excel
  • How to calculate average last N values in a table in Excel
  • How to count table rows in Excel
  • Chart Axes in Excel
  • Conditional Formatting Rules in Excel

References

  • Count rows that contain specific values in Excel
  • Merge tables with VLOOKUP in Excel
  • Two-column Lookup in Excel
  • Offset in Excel
  • Left Lookup in Excel

Data Validations

  • Excel Data validation date in next 30 days
  • How To Create Drop-down List in Excel
  • Excel Data validation no punctuation
  • Excel Data validation with conditional list
  • Excel Data validation number multiple 100

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 duplicate values 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 dates in same month and year 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:   Highlight unique values 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:

SUMX2PY2 function: Description, Usage, Syntax, Examples and Explanation

Next Post:

Everything about Charts 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

Logical Functions

  • Check multiple cells are equal in Excel
  • XOR function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel OR Function
  • TRUE function: Description, Usage, Syntax, Examples and Explanation
  • IFNA function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Get date from day number in Excel
  • Add months to date in Excel
  • Get last weekday in month in Excel
  • Display Date is same month in Excel
  • Count birthdays by month in Excel

Grouping

  • Categorize text with keywords in Excel
  • Map inputs to arbitrary values in Excel
  • Calculate conditional mode with criteria in Excel
  • Group times into 3 hour buckets in Excel
  • Group numbers at uneven intervals in Excel

General

  • How to Insert Cells, Row and Rows in Excel
  • Convert column number to letter in Excel
  • How to create dynamic named range with INDEX in Excel
  • Count cells that contain errors in Excel
  • Basic numeric sort formula in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning