Forum Discussion
Conditional Formatting for Spilled Arrays
I have recently composed a bunch of functions to make using conditional formatting with spilled arrays more manageable. You can check out those formulas at the end of my post. I am curious if anyone has a different way to accomplish this goal. I will briefly describe the overall method and give a use case.
In essence, to use conditional formatting you need to 1)specify a range of cells where the rule will be conditionally applied (the "Conditional Formatting Range") and 2) specify the condition for application. In many cases, the formatting is conditioned upon the placement of the cell/column/row/etc. relative to the spilled array which generated it (the "Spilled Array Range"). The problem (so far as I know) is that Excel's conditional formatting manager does not allow dynamic range references to specify the Conditional Formatting Range, so there is a fundamental disconnect between the Conditional Formatting Range and the possible range of the Spilled Array Range you want to format. It occurred to me that one possible solution to this problem is to A) set the Conditional Formatting Range to a defined range that is certain to be larger than your Spilled Array Range and B) create conditions that inherently self limit themselves to the Spilled Array Range regardless of the size of your Conditional Formatting Range.
Mindful of the perils of using volatile functions, I wanted to create a solution that avoids turning values (string references primarily) into range references via functions like indirect and offset. That meant that I was limited to selecting a subrange from a larger range and constructing a dynamic range by way of a pattern like "index(array1, starting_row, starting_col):index(array2, ending_row, ending_col)" where the first index returns a single cell range reference to the first cell of the target range and the second index returns a single cell range reference to the last cell of the target range. This idea sort of distills down to the last few lines of the function I created:
result,
IF(
NOT(base_in_container),
NA(),
LET(
start_ref, INDEX(container_array, idx_base_in_container_first_row, idx_base_in_container_first_col),
end_ref, INDEX(container_array, idx_base_in_container_last_row, idx_base_in_container_last_col),
start_ref:end_ref
)
),
If you name the range created in this way, you can simply enter "=named_range" into the applies to field in the conditional formatting interface and Excel will, at the time you apply the rule, resolve that name into whatever the then current range reference is of named_range, e.g. "A$7$:$G$52". Assuming your spilled array is contained in that range, your conditional formatting rule will apply to the spilled array. I call this larger containing range something like "CF_OVERSIZED_ARRAY."
Once CF_OVERSIZED_ARRAY is set to a rule, you never have to change the Conditional Formatting Range again unless your spilled array size possibly exceeds whatever range you initially selected. (For instance, if your oversized array is 50 rows and 12 columns you need not change its size unless your spilled array suddenly grows from say 7 columns to 14). The elegance of this method over directly hardcoding the value is that if you have many conditional formatting rules, by entering "=named_range" for the applies to range in each rule, you both eliminate the possibility of inconsistent application ranges and have a visual confirmation when entering each rule that the value you are setting is exactly what you intend (rather than something like "=worksheet1!$A$23:$H$79"). Furthermore, by programmatically defining the oversized array, you can make it as "small" as reasonable, thereby avoiding having conditional formatting apply to many unused cells (such as the whole worksheet).
At this point, the next computation minimization occurs - a guard clause for each condition is specified such that any cell in CF_OVERSIZED_ARRAY outside of the Spilled Array Range immediately returns a false and no further condition checking is performed. The general formula for checking if a cell is within the Spilled Array Range is as follows along with an example of a guard clause:
is_within_array = LAMBDA(
range,
LET(
start_row, ROW(TAKE(range, 1, 1)),
start_col, COLUMN(TAKE(range, 1, 1)),
AND(
ROW() >= start_row, ROW() < start_row + ROWS(range),
COLUMN() >= start_col, COLUMN() < start_col + COLUMNS(range)
)
)
);
is_in_row_of_array = LAMBDA(
range,
start_index,
[end_index],
IF(
NOT(is_within_array(range)),
FALSE,
LET(
Now that the basic structure has been established, a number of workhorse functions are established:
is_in_row_of_array - cell is within a specified row or range of rows in the spilled array such as "is in row 1" or "is in rows 4 through 6",
is_in_col_of_array - cell is within a specified column or range of columns in the spilled array such as "is in column 1" or "is in columns 4 through 6",
is_in_slice_of_array - cell is with a specified contiguous portion of the spilled array such as "is between rows 5 through 7 and columns 2 through 12"
is_in_interval_of_array - cell is in set of every N rows or N columns such as "is one of every other row" or "is one of every third column"
is_in_recurring_band_of_rows - cell is in a recurring grouping of rows such as "is 2nd and 3rd row of every group of 4 rows"
is_in_recurring_band_of_cols - cell is in a recurring grouping of columns such as "is last column of every group of 7 columns"
Here is an example function:
is_in_col_of_array = LAMBDA(
range,
start_index,
[end_index],
IF(
NOT(is_within_array(range)),
FALSE,
LET(
num_cols, COLUMNS(range),
current_col, COLUMN() - COLUMN(TAKE(range, 1, 1)) + 1,
start_resolved, IF(start_index > 0, start_index, num_cols + start_index + 1),
end_resolved,
IF(
ISOMITTED(end_index),
start_resolved,
IF(
end_index > 0,
end_index,
num_cols + end_index + 1
)
),
final_start, MIN(start_resolved, end_resolved),
final_end, MAX(start_resolved, end_resolved),
AND(current_col >= final_start, current_col <= final_end)
)
)
);
On top of the basic structure, convenience functions are created - the names of which should indicate how they solve common formatting needs:
alternate_cols
alternate_rows
is_in_first_row_of_recurring_band_of_rows
is_in_last_row_of_recurring_band_of_rows
is_in_first_col_of_recurring_band_of_cols
is_in_last_col_of_recurring_band_of_cols
is_in_header_of_col
is_in_last_row_of_col
is_in_first_data_row_of_col
is_between_first_and_last_row_of_col
One major benefit flowing from this design is that these conditions are composable, e.g. alternate_cols * is_in_header_of_col would give you alternating formatting on the headers of a spilled array, such as an array with empty columns between each of the substantive columns.
While I do not promise that the following formulas are perfect, what I can say is that they presently permit me to write rules like this:
=cf.is_in_row_of_array(ins_rep.dynamic_array, 1)*cf.alternate_cols(ins_rep.dynamic_array,FALSE)
=cf.is_in_first_data_row_of_col(ins_rep.dynamic_array,9)
=cf.is_between_first_and_last_row_of_col(ins_rep.dynamic_array, 9,TRUE)
=cf.is_in_last_row_of_col(ins_rep.dynamic_array,9)
Which effectively gives me a rule for all headers (shading with underlining and bold), the ability to set the first data cell in column 9 to be a dollar format, the last cell in column 9 to be a dollar format with a top border, and all of the other cells in column 9 to be simple integers. So something like this (imagine the first cell is shaded as described and the last cell has a top border) is what I get for column 9 of a dynamically generated and spilled array:
| Fees |
| $175 |
| 175 |
| 175 |
| 175 |
| 175 |
| 175 |
| 175 |
| 175 |
| 175 |
| $1,575 |
Please let me know if you have found any other ways to address the problem of the Conditional Formatting Range being disconnected from the Spilled Array Range. I'm happy to answer any questions about my method or formulas, so feel free to ask. I'd also appreciate any feedback/suggestions/improvements on my idea/formulas.
Here are the complete formulas (I have them saved within Excel Labs Advanced formula environment in separate modules)
// _range module
create_expanded_from_subset_of_containing_range = LAMBDA(
base_array, desired_height, container_array, [desired_width],
LET(
req_width, IF(ISOMITTED(desired_width), COLUMNS(base_array), desired_width),
/* --- Resolve anchors (references, not values) --- */
base_array_first_cell, INDEX(base_array, 1, 1),
base_array_first_row, ROW(base_array_first_cell),
base_array_first_col, COLUMN(base_array_first_cell),
container_array_first_cell, INDEX(container_array, 1, 1),
container_array_first_row, ROW(container_array_first_cell),
container_array_first_col, COLUMN(container_array_first_cell),
container_array_rows, rows(container_array),
container_array_cols, columns(container_array),
idx_base_in_container_first_row, base_array_first_row - container_array_first_row +1,
idx_base_in_container_first_col, base_array_first_col - container_array_first_col +1,
idx_base_in_container_last_row, idx_base_in_container_first_row + desired_height - 1,
idx_base_in_container_last_col, idx_base_in_container_first_col + req_width - 1,
base_in_container,
and(
idx_base_in_container_first_row > 0,
idx_base_in_container_first_row <= idx_base_in_container_last_row,
idx_base_in_container_last_row <= container_array_rows,
idx_base_in_container_first_col > 0,
idx_base_in_container_first_col <= idx_base_in_container_last_col,
idx_base_in_container_last_col <= container_array_cols
),
result,
IF(
NOT(base_in_container),
NA(),
LET(
start_ref, INDEX(container_array, idx_base_in_container_first_row, idx_base_in_container_first_col),
end_ref, INDEX(container_array, idx_base_in_container_last_row, idx_base_in_container_last_col),
start_ref:end_ref
)
),
result
)
);
//cf module
is_within_array = LAMBDA(
range,
LET(
start_row, ROW(TAKE(range, 1, 1)),
start_col, COLUMN(TAKE(range, 1, 1)),
AND(
ROW() >= start_row, ROW() < start_row + ROWS(range),
COLUMN() >= start_col, COLUMN() < start_col + COLUMNS(range)
)
)
);
is_in_row_of_array = LAMBDA(
range,
start_index,
[end_index],
IF(
NOT(is_within_array(range)),
FALSE,
LET(
num_rows, ROWS(range),
current_row, ROW() - ROW(TAKE(range, 1, 1)) + 1,
start_resolved, IF(start_index > 0, start_index, num_rows + start_index + 1),
end_resolved, IF(ISOMITTED(end_index), start_resolved, IF(end_index > 0, end_index, num_rows + end_index + 1)),
final_start, MIN(start_resolved, end_resolved),
final_end, MAX(start_resolved, end_resolved),
AND(current_row >= final_start, current_row <= final_end)
)
)
);
is_in_col_of_array = LAMBDA(
range,
start_index,
[end_index],
IF(
NOT(is_within_array(range)),
FALSE,
LET(
num_cols, COLUMNS(range),
current_col, COLUMN() - COLUMN(TAKE(range, 1, 1)) + 1,
start_resolved, IF(start_index > 0, start_index, num_cols + start_index + 1),
end_resolved, IF(ISOMITTED(end_index), start_resolved, IF(end_index > 0, end_index, num_cols + end_index + 1)),
final_start, MIN(start_resolved, end_resolved),
final_end, MAX(start_resolved, end_resolved),
AND(current_col >= final_start, current_col <= final_end)
)
)
);
is_in_slice_of_array = LAMBDA(
range,
start_row,
start_col,
[end_row],
[end_col],
[include_slice_neg1_to_exclude],
if(
not(is_within_array(range)),
FALSE,
LET(
final_end_row, IF(ISOMITTED(end_row), start_row, end_row),
final_end_col, IF(ISOMITTED(end_col), start_col, end_col),
row_match, is_in_row_of_array(range, start_row, final_end_row),
col_match, is_in_col_of_array(range, start_col, final_end_col),
selection, AND(row_match, col_match),
mode, IF(ISOMITTED(include_slice_neg1_to_exclude), 1, include_slice_neg1_to_exclude),
IF(mode = -1, NOT(selection), selection)
)
)
);
is_in_interval_of_array = LAMBDA(
range,
row_interval,
col_interval,
[start_at_row],
[start_at_col],
[include_interval_neg1_to_exclude],
if(
not(is_within_array(range)),
FALSE,
LET(
row_idx, ROW() - ROW(TAKE(range, 1, 1)) + 1,
col_idx, COLUMN() - COLUMN(TAKE(range, 1, 1)) + 1,
start_row, IF(ISOMITTED(start_at_row), 1, start_at_row),
start_col, IF(ISOMITTED(start_at_col), 1, start_at_col),
row_match, IF(row_interval <= 1, TRUE, MOD(row_idx - start_row, row_interval) = 0),
col_match, IF(col_interval <= 1, TRUE, MOD(col_idx - start_col, col_interval) = 0),
selection, AND(row_match, col_match),
mode, IF(ISOMITTED(include_interval_neg1_to_exclude), 1, include_interval_neg1_to_exclude),
IF(mode = -1, NOT(selection), selection)
)
)
);
alternate_cols = lambda(
array,
[start_with_even_df_TRUE],
is_in_interval_of_array(array,1,2,,1+if(isomitted(start_with_even_df_TRUE),1,start_with_even_df_TRUE))
);
alternate_rows = lambda(
array,
[start_with_even_df_TRUE],
is_in_interval_of_array(array,2,1,1+if(isomitted(start_with_even_df_TRUE),1,start_with_even_df_TRUE))
);
is_in_recurring_band_of_rows = LAMBDA(
range,
rows_in_pattern,
first_row_in_band,
[band_thickness],
[include_selected_df_TRUE],
IF(
NOT(is_within_array(range)),
FALSE,
LET(
relative_row, ROW() - ROW(TAKE(range, 1, 1)),
row_in_pattern, MOD(relative_row, rows_in_pattern) + 1,
actual_thickness, IF(ISOMITTED(band_thickness), 1, band_thickness),
is_in_band, AND(
row_in_pattern >= first_row_in_band,
row_in_pattern <= (first_row_in_band + actual_thickness - 1)
),
include_mode, IF(ISOMITTED(include_selected_df_TRUE), TRUE, include_selected_df_TRUE),
IF(include_mode, is_in_band, NOT(is_in_band))
)
)
);
is_in_first_row_of_recurring_band_of_rows = lambda(
range,
rows_in_pattern,
[include_selected_df_TRUE],
is_in_recurring_band_of_rows(range, rows_in_pattern, 1, 1, include_selected_df_TRUE)
);
is_in_last_row_of_recurring_band_of_rows = lambda(
range,
rows_in_pattern,
[include_selected_df_TRUE],
is_in_recurring_band_of_rows(range, rows_in_pattern, rows_in_pattern, 1, include_selected_df_TRUE)
);
is_in_recurring_band_of_cols = LAMBDA(
range,
cols_in_pattern,
first_col_in_band,
[band_thickness],
[include_selected_df_TRUE],
IF(
NOT(is_within_array(range)),
FALSE,
LET(
relative_col, COLUMN() - COLUMN(TAKE(range, 1, 1)),
col_in_pattern, MOD(relative_col, cols_in_pattern) + 1,
actual_thickness, IF(ISOMITTED(band_thickness), 1, band_thickness),
is_in_band, AND(
col_in_pattern >= first_col_in_band,
col_in_pattern <= (first_col_in_band + actual_thickness - 1)
),
include_mode, IF(ISOMITTED(include_selected_df_TRUE), TRUE, include_selected_df_TRUE),
IF(include_mode, is_in_band, NOT(is_in_band))
)
)
);
is_in_first_col_of_recurring_band_of_cols = LAMBDA(
range,
cols_in_pattern,
[include_selected_df_TRUE],
is_in_recurring_band_of_cols(range, cols_in_pattern, 1, 1, include_selected_df_TRUE)
);
is_in_last_col_of_recurring_band_of_cols = LAMBDA(
range,
cols_in_pattern,
[include_selected_df_TRUE],
is_in_recurring_band_of_cols(range, cols_in_pattern, cols_in_pattern, 1, include_selected_df_TRUE)
);
is_in_header_of_col = LAMBDA(
array,
[column_no],
IF(ISOMITTED(column_no),
is_in_row_of_array(array, 1),
is_in_slice_of_array(array, 1, column_no)
)
);
is_in_last_row_of_col = LAMBDA(
array,
[column_no],
IF(ISOMITTED(column_no),
is_in_row_of_array(array, -1),
is_in_slice_of_array(array, -1, column_no)
)
);
is_in_first_data_row_of_col = LAMBDA(
array,
[column_no],
IF(ISOMITTED(column_no),
is_in_row_of_array(array, 2),
is_in_slice_of_array(array, 2, column_no)
)
);
is_between_first_and_last_row_of_col = lambda(
array,
[column_no],
[exclude_first_data_row_df_FALSE],
is_in_slice_of_array(
array,
if(isomitted(exclude_first_data_row_df_FALSE),FALSE,exclude_first_data_row_df_FALSE)+2,
if(isomitted(column_no),1,column_no),
-2,
if(isomitted(column_no),-1,column_no),
)
);
7 Replies
Can you please attach a sample file with few use cases? It would helpful for visitors to understand the scenario.
Hi Harun,
I wrote you a pretty lengthy reply that somehow got deleted/lost. Sorry mate. If I figure out the issue with NikolinoDE 's file, maybe I can add some dumby data in there to show you a use case.
Hi Harun,
It is a little difficult to make an example file without adding a bunch of fake data/reports. I'll try a bit of a narrative and see if that helps. Imagine you had a customer list and you are trying to show how much in sales you had. Say you had 5 customers and 5 products and you wanted a presentation where there is a header row like, "Name", "Product 1 Sales", "Product 2 Sales", etc. (a total of six columns), a body where each customer gets their own row "(Customer A", "Customer B", etc.) and a totals row for each product so like "Totals", "=sum(product 1 sales)", "=sum(product 2 sales)", etc. Now imagine you want that report formatted in a particular way, but your data is still changing. So maybe you end up with 20 customers or 12 products, but you given the dynamic nature of the data, that could change between when you create the report and when you use it.
Traditionally what would do is just hand format each of the rows/columns/cells in the report range - say A1 to E1 for headers, A2 to A6 for the customer names, B2 to E6 for the total product amounts by customer, and A7 to A6 for the total amounts by product.
A fairly early alternative was to use pivot tables, so the customers would generate automatically and you could add a column for each new product - the main problem being that you are stuck with whatever pivot table formatting is made available to you within the general constraints of a pivot table.
More recently you can use something like groupby (a dynamic array formula that spills into a range of indefinite size). So as you add products, the number of columns grows and as you add customers, the number of rows grow. The big problem is that Excel does not natively have a way for you to format the header rows, the total rows, or the data rows except using the manual traditional method. An alternative might be to use conditional formatting where you keep going back into the "applies to" range and updating the ranges to match the current size of your report's spilled array.
What my file does is sort of fudge the dynamic nature of a pivot table's formatting with the dynamism of the groupby spilled array. It can't perfectly replicate the pivot table's built in conditioning, but it can approximate it without a huge amount of overhead. In effect, by creating a conditional formatting applies to range that you know is always going to be bigger than your dynamic array's spilled range, you ensure that your spilled range is always formatted according to your rules. For example, if you say a conditional formatting rule applies to cells A1:Z1000, you've created the possibility for your dynamic array to have about 1000 customers and 25 products and still get covered by your rule. Anything less than that, you are fine - anything more than that, your conditional formatting stops working. The problem is, if you have your applies to range be the entire sheet, you may very well have a million cells checking to see what conditional formatting should be applied to them - if you had a dozen rules that might apply, that means you might have twelve million conditional checks. The only way to avoid either not having your conditional formatting apply to your entire dynamic range and to avoid having every cell on the sheet checked to see how it should be formatted is to find a goldilocks sort of zone - one where just the right number of cells are checked - not too many, not too few.
My system is designed to automate the calculation of the goldilock zone and make it simple for you to set the applies to range for each conditional formatting rule. The system also adds the ability for you to use something known as "declarative programming" like statements to select what portion of a dynamic array you want the rule applied to. Rather than having to figure out how to have a condition apply just to the last row of your dynamic array (within the goldilocks applies to range), you just write a formula like cf.in_last_row(named_dynamic_array). In this way, it is dead simple for you to understand what the conditional formatting rule is meant to achieve.
It is not the sort of solution the typical excel user would employ - it is very "abstract" in the sense that it is meant to apply to any problem within the general category of problems of conditionally formatting dynamic arrays. You need to be able to understand what that problem is in the first place to have any appreciation for why you might use the tool offered. So the likely user base is someone that is designing automated reporting for people that are otherwise unable to use macro enabled workbooks (VBA), ExcelScripts (effectively javascript), or custom addons into excel. In other words, it is an entirely formulaic solution that will work on any computer that has excel 365 installed absent the system's admin disabling excel's name manager or modern excel 365 functionality.
Maybe this will help you a bit.
Code in the workbook module.
I included...
- CreateExpandedNamedRange — creates or updates a workbook-level named range that refers to an explicit start:end inside a container range (safe fallback to a single-cell if requested size doesn't fit).
- UpdateCF_AppliesTo_FromNamedReferences — scans conditional formatting rules and, where it finds a formula that contains a named range, updates that rule's AppliesTo to the named range's current RefersToRange.
- CreateExpandedNamedRangeAndUpdateCF — wrapper to create a named range and immediately update CF rules (convenience).
- Detailed comments, usage examples, and safety notes inside the module.
My answers are voluntary and without guarantee!
Hi Nikolino,
I tried opening your file, but there does not appear to be anything in it. Can you try uploading it again?
Enable Makros. The code is in Module 1.
I've also inserted the code as text into the worksheet. See the attached file.
