How to Dynamically Format An Excel Spill Range?

Question

Microsoft is about to release a new "Spill" feature for Excel. At time of post, this not available in the current version, but insiders can use it.

Is there an "easy" (non-vba or conditional formatting) method to dynamically format the spilled range? Example (as shown in this file) is if a user changed a cell, which drives a spill range, is it possible that spilled range could hold certain formatting? Additionally, if the list shortened, I would want the formatting to resort to blank cell formatting.

In the example, I'm trying to use a certain gray format (the Style of output cell ) for the list. If you tinker around you can see the good/bad results.

I realize that Pivot Tables may be the better approach, I'm just more curious from a learning perspective if there's something I'm overlooking.

Answer 1

I use conditional formatting to do this. I just have a rule of "does not contain a blank" that applies to the whole column or my desired range. I also like to have banded rows, so I use a rule above that rule that just has the background the chosen banded color when this formula is satisfied:

=IF(MOD(ROW(),2)=1,TRUE)

Joe