10

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.

enter image description here

enter image description here

enter image description here

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.

pgSystemTester
  • 8,979
  • 2
  • 23
  • 49
  • 4
    For the record, I think the right list would make a great team. – urdearboy Feb 24 '19 at 20:29
  • @urdearboy, on excel, FILE --> ACCOUNT --> Select Office Insider, JOIN OFFICE INSIDER. Close, reopen and make sure it has time to update. – pgSystemTester Feb 24 '19 at 20:52
  • 1
    Seems like you already ruled out the ways we'd typically perform "automatic" formatting... – Tim Williams Feb 24 '19 at 22:30
  • @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. – pgSystemTester Feb 25 '19 at 00:03
  • 2
    I suspect the answer is "not yet, but soon..." – chris neilsen Feb 25 '19 at 02:10

2 Answers2

4

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

Jared Forth
  • 1,577
  • 6
  • 17
  • 32
Joe Kell
  • 51
  • 2
  • 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. – Michael Wycisk Aug 04 '20 at 22:50
  • 2
    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 – chris neilsen Aug 05 '20 at 09:33
  • 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 :( – Joe Kell Aug 05 '20 at 13:12
  • 1
    @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 ). – pgSystemTester Aug 07 '20 at 05:28
0

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.

Javi Smith
  • 69
  • 1
  • 1
  • 3
  • 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.stack.imgur.com/kbAWT.png](https://i.stack.imgur.com/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. – pgSystemTester Aug 14 '23 at 21:09