How to Highlight Adjacent Duplicates in Google Sheets

In this post, let’s learn to highlight consecutive or adjacent duplicates in Google Sheets.

The conditional formatting for the same is simple.

Sometimes we may require to find duplicate values in rows or columns in consecutive cells, not in distant cells.

I’ve conditional format rules for the same for both row and column-wise data.

That will help you identify such values.

For example, we have the text “apple” in cells A2, A4, A5, and A7. The formula should only highlight A4 and A5 since those are the consecutive cells with duplicates.

You can also decide whether to conditional format both A4 and A5 or A5 alone, i.e., from the second occurrence onwards.

Here are examples of highlighting adjacent duplicates in Google Sheets.

Highlight Adjacent Duplicates Row or Column-Wise

In the first example, we will use a conditional format custom formula rule to highlight all the occurrences of duplicates in consecutive cells.

Later we can see how to exclude the first occurrence.

Highlight All the Occurrence of Consecutive Duplicate Cells

Please see the screenshot below.

Highlight All Adjacent Duplicates in Google Sheets
screenshot # 1

Column-Wise Data

We can use the following LEN and Logical AND based rule for the column-wise data in B2:I2.

=and(len(B2),B2=C2)

You must apply this twice, first for the range B2:I2 and then for the range C2:I2. Here is how?

Go to Format > Conditional formatting.

It will open the sidebar panel to enter the range of cells to highlight. Also, to add the above formula rule.

Find “Apply to range,” and enter B2:I2, which is the range to highlight for consecutive or adjacent duplicated cells.

Then select “Custom formula is” under “Format rules.”

Just enter the above formula and choose the color to fill the cells that contain the adjacent duplicate values.

Below that, find the option “Add another rule.”

Open that and modify “Apply to range” to C2:I2 and “DONE.”

Consecutive Duplicates in Google Sheets
screenshot # 2

Note:- To include another row, i.e., B3:I3, you are only required to modify the “Apply to ranges” B2:I2 and C2:I2 to B2:I3 and C2:I3, respectively.

Row-Wise Data

We can follow the above method when we want to highlight adjacent duplicates in rows in Google Sheets.

Please see the range B8:B15 on screenshot # 1 above.

We can use the following conditional format formula for B8:B15 and B9:B15 (twice as above)

=and(len(B8),B8=B9)

Note:- When you have one more column, i.e., C8:C15, just modify the “Apply to range” to B8:C15 and B9:C15.

Highlight Adjacent Duplicates Except for First Occurrence

Please see screenshot # 3 below.

Highlight Adjacent Duplicates Except for First Occurrence
screenshot # 3

You can see the difference!

The consecutive duplicate cells are highlighted, both in column-wise and row-wise data, except for the first occurrence.

We require to delete one rule to achieve this.

Horizontal Data:- Delete the rule added for B2:I2. We only require the C2:I2 one.

Vertical Data:- Delete the rule added for B8:B15. We only require the B9:B15 one.

This way we can highlight adjacent duplicates in Google Sheets.

That’s all. Thanks for the stay. Enjoy!

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.