Highlight Consecutive Duplicates
Excel Tips and Tricks

Highlight the Consecutive Duplicates in Excel using Conditional Formatting

In this article you will learn how to highlight the Consecutive Duplicates in Excel using Conditional Formatting. You will also learn how to highlight consecutive weekly off in an Attendance Sheet.

Let’s say we have month wise sales wherein we have month name duplicate. We want to highlight the consecutive duplicate month as given in below image

Consecutive duplicate month
Consecutive duplicate month

Below are the steps to put the Conditional Formatting to highlight the consecutive duplicate month-

  • Select the Range (“A2:A30”)
  • Go to Home >> Conditional Formatting >> New Rule or Press Alt+D+O+N
  • Select “Use a formula to determine which cells to format”
  • Put the formula “=OR(A2=A1,A2=A3)”  in the box.
  • Click on Format button and select some background color.
  • Click on OK.

 

Conditional Formatting Window
Conditional Formatting Window

 

Let’s say we have a Attendance Sheet wherein we need to highlight the Consecutive Weekly Off (WO) like given in below image –

Employee Attendance
Employee Attendance

Below are the steps to put the Conditional Formatting to highlight the Consecutive Weekly Off (WO) –

  • Select the Range (“B2:AF11”)
  • Go to Home >> Conditional Formatting >> New Rule or Press Alt+D+O+N
  • Select “Use a formula to determine which cells to format”
  • Put the formula “=AND(OR(B2=A2,B2=C2),B2=”WO”)”  in the box.
  • Click on Format button and select some background color.
  • Click on OK.

 

highlight the Consecutive Weekly Off
Highlight the Consecutive Weekly Off

 

Click here to download the practice workbook.

Watch the step by step video tutorial:

 

PK
My Name is PK. I am founder of PK-AnExcelExpert.com. I am a Microsoft Certified Professional. I have more than 10 years of experience in Data Visualization, Excel Automation and dashboard creation. Excel is my passion and I like to do always something innovative in Excel and share it with other people.
https://www.pk-anexcelexpert.com