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?


Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
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 · 1 years ago
    Its there such a things as consecutive partial match?
  • To post as a guest, your comment is unpublished.
    Greg · 2 years ago
    This did not highlight consecutive cells - it highlighted the two cells prior to the two consecutive cells.