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
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.
Let’s say we have a Attendance Sheet wherein we need to highlight the Consecutive Weekly Off (WO) like given in below image –
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.
Click here to download the practice workbook.
Watch the step by step video tutorial:
