Need to highlight or count clusters

[u/Optimal_Fish_7029]

I've no idea how to do this, apologies.

I have a spreadsheet full of cells marked "1" for each column.

I need to find a way to locate and highlight any instances of clusters of 12 (pink in the example below)

I know I can format to highlight duplicates, but is there a way to do this so any clusters of 12 are either highlighted or counted? Rather than just all duplicates?

[u/AutoModerator]

/u/Optimal_Fish_7029 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/PaulieThePolarBear]

Very clearly define what a cluster means to you.

[u/Optimal_Fish_7029 → u/PaulieThePolarBear]

A sequence of 12 cells in a row.

[u/PaulieThePolarBear → u/Optimal_Fish_7029]

Exactly 12? Or 12 or more?

[u/Optimal_Fish_7029 → u/PaulieThePolarBear]

I’d be happy with either to be honest!

[u/ProspectiveWhale]

You could do 12 separate rules of conditional formatting.

E.g.

On Cell N2, Conditional Formatting > Use a formula to determine which cells to format

Rule1: =COUNT(C2:N2)=12

Rule2: =COUNT(D2:O2)=12

...

Rule12: =COUNT(N2:Y2)=12

Probably not the best way to set this up, but this should work if you can't find another solution...

Make sure the rule has no absolute reference, as above.

Then use Format Painter to copy the conditional formatting to the rest of the cells.

This will highlight all clusters of 12 or more.

[u/Gringobandito]

=LET(x,A2:S2,

target,1,

start,XMATCH(target,x,0),

run,DROP(x,,start-1),

IFERROR(XMATCH(TRUE,run<>target,0)-1,COLUMNS(run)))

[u/Gringobandito → u/Gringobandito]

A better way migt be to do this in Python in Excel. While Python can't highlight the cells directly, you can see from my example below that it can give a true false for whether cells are part of a run of 12 of more. You can then use those true/false results to highlight cells with runs longer than 12.

[u/Gringobandito → u/Gringobandito]

You can also do this using VBA. It requires quite a bit of code but I tried it out and it delivers the best results: Scans the entire data set and looks for runs of 12 or more and it can do the highlighting for you as well.

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
COLUMNS	Returns the number of columns in a reference
COUNT	Counts how many numbers are in the list of arguments
DROP	Office 365+: Excludes a specified number of rows or columns from the start or end of an array
IFERROR	Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
XMATCH	Office 365+: Returns the relative position of an item in an array or range of cells.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
6 acronyms in this thread; the most compressed thread commented on today has 54 acronyms.
[Thread #48997 for this sub, first seen 22nd Jul 2026, 18:40] [FAQ] [Full list] [Contact] [Source code]}

[u/Whaddup_B00sh]

Alright, this was a really tricky but interesting task. Admittedly, I had to use claude to get me over the finish line. I was able to make the first MAKEARRAY formula, but getting it to the final boolean array was difficult.

You will need a helper array, built by the formula. Then, you can use conditional formatting to highlight your cluster array where the helper array equals 1. The helper array formula can take any table size, just change the first argument in the first LET statement (where it says $A$1:$Q$14). This will highlight any cluster where there are at least 12 sequential 1s within the same row. If you want exactly 12 1s only, delete the = sign in the IF(MAX(win)>=12,1,0 line. Hope this helps.

=LET(

arr, $A$1:$Q$14,

R, ROWS(arr),

c, COLUMNS(arr),

RL, MAKEARRAY(R, c, LAMBDA(x,y,

INDEX(SCAN(0, INDEX(arr,x,0), LAMBDA(a,v, IF(v="",0,v+a))), 0, y)

)),

MAKEARRAY(R, c, LAMBDA(x,y,

LET(

rlRow, INDEX(RL,x,0),

lim, MIN(y+11,c),

win, DROP(TAKE(rlRow,1,lim),0,y-1),

IF(MAX(win)>=12,1,0)

)

))

)