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.

Install

For the record, I think the right list would make a great team. – Winson

@urdearboy, on excel, FILE --> ACCOUNT --> Select Office Insider, JOIN OFFICE INSIDER. Close, reopen and make sure it has time to update. – Install

Seems like you already ruled out the ways we'd typically perform "automatic" formatting... – Displease

@TimWilliams, that may very well be the answer. I'm not sure if you've experimented with the Spill functionality much, but it "feels" like something that should hold the formatting (similar to the way a table or a pivot table works). Since this is brand new, maybe this is just something I'll have to get used to, but I figured I would throw the question out there. – Install

I suspect the answer is "not yet, but soon..." – Canonicity

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

Harmonium

Conditional formatting is a good workaround. But this formula will just format each second row. So you need to combine it with an ISBLANK formula. In the above example it would be =NOT(ISBLANK($B4) - or =AND(NOT(ISBLANK($B4));MOD(ROW();2)=1) to color each second row. – Varico

This does not answer the Q. OP specifically says Is there an "easy" (non-vba or conditional formatting) method... so they clearly already know CF'g can do the job, but don't want to use it – Canonicity

Chris - unfortunately, I just remembered the Q asking for non VBA, sorry! :/ Michael - That formula is for the banded rows, the second sentence of my reply is to use "does not contain a blank". Either way, the OP wants a method that doesn't use conditional formatting :( – Harmonium

@JoeKell thanks for trying. Your workaround is indeed a functional workaround, and it's probably what most folks should do (maybe not the whole column...). However the purpose of this post is to seek a dynamic "non-hack" answer. At present time I don't think there is one, so hopefully Microsoft takes notice. I gave you an up-vote since it's your first answer and your answer isn't terrible (by first time answers standards 🙂). – Install

Answer 2

This solution utilizes conditional formatting, however it is pretty simple:

Select the spill cell and apply a conditional format for whichever condition you want to apply, for me I just used 'Greater than 0.'

For the 'Applies to' value in the conditional formatting menu, add a '#' after the cell reference that contains the spill range formula.

For example, if you have a UNIQUE() function in Cell B3, apply the conditional format to B3, you will see it appears as '$B$3' in the conditional formatting 'Applies to' range. Change that to '$B$3#' and it should dynamically update with the spill range.

Alternatively, you could set up a named range that uses the same '$B$3#' reference and apply the conditional format to the named range.

Olive

unfortunately, this does NOT work dynamically. It only works on one occasion and then is stagnant. Using the example from my original post, you'll note that if we have the original spill range of a 5 players, https://i.sstatic.net/kbAWT.png, and then mark conditional formatting as B4#, yes it looks good for cells B4:B8. However if that range GROWS to B4:B28 the conditiaional formatting does NOT dynamically adjust. Nice idea, though. – Install

Answer 3

You can apply conditioanl formatting to the entire sheet, and use formula to leverage the area using cell's row and/or column number.

For example, for the scenario above, *Cell that contains spill range formula is $B$4

To leverage the area using cell's row number

Apply to range: A:XFD

Formula: AND(ROW(A1)>=ROW($B$4), ROW(A1)<ROW($B$4)+ROWS($B$4#))

To leverage the area using cell's column number, simply change row() function to column. Or, you can also use both row and column to make the exact area if needed.

AND(ROW(A1)>=ROW($B$4),ROW(A1)<ROW($B$4)+ROWS($B$4#),COLUMN(A1)>=COLUMN($B$4),COLUMN(A1)<COLUMN($B$4)+COLUMNS($B$4#))

Selby

That's a good effort for a first answer so I'll try to provide some helpful feedback. First, note that I asked for a solution without using conditional formatting. There's a specific reason for this limitations as conditional formatting is a volatile formula which means it must recalculate on every change irrespective of if the cell impacted has a direct impact. Unfortunately your proposal would be a bit catastrophic in that you'd be doing literally billions of unnecessary calcs as you have that condition span so many cells. – Install