Suggestion to Microsoft for improvement with SPILL feature

Ludwig

Board Regular
Joined
Apr 7, 2003
Messages
90
Office Version
  1. 365
Platform
  1. Windows
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!
 

Attachments

  • Screenshot 2024-05-02 053207.png
    Screenshot 2024-05-02 053207.png
    68.2 KB · Views: 14
Last edited:

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top