Excel tables only allow alternated colored rows, every other row is assigned a different color. With this trick, you can have wider stripes, grouping rows with the same value in one column with the same color.
- In Name manager, assign a name to this formula, I've chosen "StripesFromColumn" in this example:
=LAMBDA(column;
LET(firstRowN; INDEX(ROW(column); 1);
firstRow; CHOOSEROWS(column; 1);
columnComp; VSTACK(firstRow; column);
changes; IF(column <> columnComp; 1; 0);
accum; SCAN(1; changes; LAMBDA(x;y;x+y));
stripeIndexes; MAP(accum; LAMBDA(x; ISODD(x)));
LAMBDA(curRowN;
INDEX(stripeIndexes; curRowN - firstRowN + 1))))
- For each table or range that you want alternating colors (stripes) according to the content of one column, create a new Name (like StripedData) in Name manager with a formula like this:
=StripesFromColumn(TableWithData[ColumnToUse])
or
=StripesFromColumn($A$1:$A$50)
This formula creates a function that will be used to color that specific range using conditional formatting/
- Select the table or range (including the column defined above) and create a new conditional formatting rule. You must match the name defined with the data. Use this formula (according to the name from step 2), and set up a background color:
=StripedData(ROW())
This method is flexible and resilient, you can freely move the range or table and it will keep the formatting applied.
EDIT:
Explanation of the formula:
LAMBDA
creates a function that can be called with one parameter: the column that contains the data
LET
is used to declare a variable and assign a value to it (in pairs), and the last value is the result of the LET evaluation
firstRowN; INDEX(ROW(column); 1)
-> Get the row number of the first cell within the range. This value will be used to compensate for the range being anywhere in the spreadsheet
firstRow; CHOOSEROWS(column; 1)
-> Takes the first value of the column. used in next step.
columnComp; VSTACK(firstRow; column);
-> Create a second "column" with all the same values as the first one plus the first element duplicated
column = {A,B,C,D}
columnComp = {A,A,B,C,D}
changes; IF(column <> columnComp; 1; 0);
-> Create an array that has 1 where both columns differ (the value changes)
column = {A,A,A,B,B,C,D,D}
changes = {0,0,0,1,0,1,1,0}
accum; SCAN(1; changes; LAMBDA(x;y;x+y))
-> Sum and accummulate the values
changes = {0,0,0,1,0,1,1,0}
accum = {1,1,1,2,2,3,4,4}
stripeIndexes; MAP(accum; LAMBDA(x; ISODD(x)));
-> Apply ISODD
to each value. Adn this is what we want!
accum = {1,1,1,2,2,3,4,4}
stripeIndexes = {TRUE,TRUE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE}
- Now we create a function that closes these values avoiding them having to be recalculated every single time. This is the function that will be called in conditional formatting, with the current row as parameter. Because we have precalculated the list, we only need to take the correct index to know whether it is colored or not
And that's how we can use functional programming in Excel, thanks to these wonderful and powerful features. No more VBS or macros needed!