Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

 How to highlight consecutive duplicate cells from a list in Excel?

doc highlight consecutive values 3

Supposing, I have a long list of data which contains some consecutive duplicate values, how could I highlight those consecutive duplicate cells as following screenshot shown in Excel?

Highlight consecutive duplicate cells from a list with Conditional Formatting

Highlight duplicate cells from a list with Kutools for Excel


arrow blue right bubble Highlight consecutive duplicate cells from a list with Conditional Formatting


In Excel, to highlight the consecutive duplicate values, the Conditional Formatting feature may do you a favor, please do as follows:

1. Select the data list that you want to use, and click Home > Conditional Formatting > New Rule, see screenshot:

doc highlight consecutive values 1

2. In the New Formatting Rule dialog box, do the following operations:

(1.) Click Use a formula to determine which cells to format option under the Select a Rule Type;

(2.) In the Format values where this formula is true text box, please enter this formula: =OR($A1=$A2, $A2=$A3);

(3.) Then click Format button to go to the Format Cells dialog box;

(4.) In the popped out Format Cells dialog box, please choose one color you want to highlight the consecutive duplicate values under the Fill tab.

doc highlight consecutive values 2

3. After finishing the settings, then click OK > OK to exit the dialogs, and the consecutive duplicate cells have been highlighted at once, see screenshot:

doc highlight consecutive values 3

Note: With the above formula, you can highlight all the consecutive duplicate cells, if you want to highlight the consecutive duplicate cells without the first occurrences, please apply this formula: =$A1=$A2 in the Conditional Formatting, and you will get the following result:

doc highlight consecutive values 4


arrow blue right bubble Highlight duplicate cells from a list with Kutools for Excel

If you have Kutools for Excel, with its Select Duplicate & Unique Cells utility, you can quickly highlight and select all the duplicate or unique values from a list as you need.

Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 60 days. 

After installing Kutools for Excel, please do as follows:

1. Select the data list that you want to select the duplicate cells, then click Kutools > Select > Select Duplicate & Unique Cells, see screenshot:

doc highlight consecutive values 5

2. In the Select Duplicate & Unique Cells dialog box, please do as follows:

(1.) Select All duplicate (Including 1 st one) option from the Rule section;

(2.) Then choose a background color or font color that you want to highlight the duplicate cells under the Processing of results;

(3.) And then click Ok button, and all the duplicate values in the list have been selected and highlighted as you want. See screenshot:

doc highlight consecutive values 6

Note: To highlight the duplicate cells without the first occurrences, please select Duplicates (Except 1 st one) in the Select Duplicate & Unique Cells dialog box, and you will return the following result:

doc highlight consecutive values 7

Free Download Kutools for Excel Now!


Related article:

How to highlight consecutive or non-consecutive numbers from a column in Excel?


Recommended Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 71%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 82% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Tom · 10 months ago
    Its there such a things as consecutive partial match?
  • To post as a guest, your comment is unpublished.
    Greg · 1 years ago
    This did not highlight consecutive cells - it highlighted the two cells prior to the two consecutive cells.