Forum Discussion

nprager1450's avatar
nprager1450
Copper Contributor
Mar 12, 2026

Conditional Formatting Duplicates in Two Columns

Hello,

I have a spreadsheet where I track calls my office receives. I have a table that includes the date and name for each call. Sometimes calls are entered in the spreadsheet multiple times. I would like to create a conditional formatting formula that will flag if a name is entered twice in one day. Is there a way to highlight cells that are duplicated in two rows of a table?

 

Thank you!

excel
Formulas and Functions

4 Replies

  • Olufemi7's avatar
    Olufemi7
    Iron Contributor
    Mar 13, 2026

    Hello nprager1450​,

    Select the Name column, go to Conditional Formatting, choose New Rule, Use a formula to determine which cells to format, and enter the formula =COUNTIFS($A$2:$A2, $A2, $B$2:$B2, $B2) > 1 assuming dates are in column A and names in column B starting at row 2. This will highlight only the second and later duplicates for the same day.

    • m_tarler's avatar
      m_tarler
      Silver Contributor
      Mar 13, 2026

      Please Note that the APPLIED TO range for the formula from Olufemi7​  MUST start with A2 NOT be the whole column nor starting at A1.  Otherwise the highlighted cells will be off by a row.  Basically the row numbers in that formula must match the upper most row of the Applied To range.

    • m_tarler's avatar
      m_tarler
      Silver Contributor
      Mar 12, 2026

      My only comment is if your Date column is actually Date-Time then a minor tweak like the following is needed:

      =COUNTIFS($D$3:$D$36,">="&INT($D3), $D$3:$D$36,"<"&INT($D3)+1, $E$3:$E$36,$E3)>1

      note: you can also replace $D$3:$D$36 with something like $D$3:.$D$999 where the period after the colon is a shortcut to the TRIMRANGE function to automatically find the end of the 'used' range so you can use this formula on columns of various length and/or continue to add rows and still have the formula work.