I've become a fan of using functions like FILTER, optionally combined with SORT and/or UNIQUE, and other formulas that use the SPILL feature to extract and report on data.
One annoying wrinkle for me is that you need to format all possible cells used in the spill to get a suitable presentation layout. You need to ensure sufficient room so the SPILL doesn't run into other data, but at the same time, you don't know how much there will be. Therefore how far do you need to go formatting cells to ensure the SPILLed data is formatted appropriately is the question.
If you don't do sufficient formatting, then things like dates show as numbers(!) and currency show just as numbers e.g. $2.10 ends up showing as 2.1 as in attached screenshot. My circumvention, for now at least, is often to format the *entire* column as a date or currency or whatever I need, then change any individual cells (usually above the SPILLing formula) if they require a different format e.g."General" or "Text".
My suggestion to Microsoft is that if the cells in the SPILL range have not been explicitly formatted by the user (e.g. the default as provided when you create a workbook or add a new worksheet), then the format of the cell with the formula is also SPILLed. You would need to format other cells that are part of that 'starter' cell and have their formatting SPILLed too where multiple columns of data are involved - for example, see attached screenshot which shows SPILL behaviour and also what you'd probably actually want to have Excel do.
This is a link to my Feedback Hub on this SPILL feature improvement posting for anyone who would like to upvote it. Thanks!
One annoying wrinkle for me is that you need to format all possible cells used in the spill to get a suitable presentation layout. You need to ensure sufficient room so the SPILL doesn't run into other data, but at the same time, you don't know how much there will be. Therefore how far do you need to go formatting cells to ensure the SPILLed data is formatted appropriately is the question.
If you don't do sufficient formatting, then things like dates show as numbers(!) and currency show just as numbers e.g. $2.10 ends up showing as 2.1 as in attached screenshot. My circumvention, for now at least, is often to format the *entire* column as a date or currency or whatever I need, then change any individual cells (usually above the SPILLing formula) if they require a different format e.g."General" or "Text".
My suggestion to Microsoft is that if the cells in the SPILL range have not been explicitly formatted by the user (e.g. the default as provided when you create a workbook or add a new worksheet), then the format of the cell with the formula is also SPILLed. You would need to format other cells that are part of that 'starter' cell and have their formatting SPILLed too where multiple columns of data are involved - for example, see attached screenshot which shows SPILL behaviour and also what you'd probably actually want to have Excel do.
This is a link to my Feedback Hub on this SPILL feature improvement posting for anyone who would like to upvote it. Thanks!
Attachments
Last edited:
