Story of the Flash Fill Feature in Excel

https://blog.sigplan.org/2021/09/14/the-story-of-the-flash-fill-feature-in-excel/

by azhenley • 5 years ago

109 45 5 years ago

blog.sigplan.org

willyg123• 5 years ago

She opened up her laptop, *fired up* Excel...

"fired up" in this context needs a place in the CS Hall of Fame

ExtraE• 5 years ago

…why?

tibbar• 5 years ago

I went from searching for the hardest problem I can solve to searching for the simplest problem that will have the most impact.

This is a fantastic framework to pursue when, for instance, joining a new organization. You can quickly have an outsized impact just by looking for common pain points. As a corollary, it’s very useful to spend lots of time using your own product and talking to the users!

agumonkey3• 5 years ago

this is something I'd pay to be addressed in most companies / institutions.

Every tool they buy (physical or digital) is always 60% fit, but adding a few bits on top would propel it above 80% and make everybody's days a bliss.

Most applications I've seen are designed for generic tasks, except that workers have a very regular set of tasks, if software could be tailored a bit to this (prefill stuff, save clicks) you cut fatigue and errors buy a huge figure.

This can have a serious social impact, last gig there were thousands of lawsuits waiting in a hall because nobody wants to type them in since the software requires to rewrite everything from scratch even if 80% of the data is identical.

CraigJPerry5• 5 years ago

This is one advantage of the world switching to webapps over desktop apps. It’s trivial for me to create a chrome extension that enriches or modifies the UI of a bought product.

Add an extra menu of common tasks- no problem. Add a datalist dropdown of options to a textbox where the options come from another service dynicamlly? - again no bother but usability of the vendor product went up substantially.

peterburkimsher2• 5 years ago

Yes, and it's surprisingly easy to find out where to help! The best colleagues and best teams are where there are a lot of "thank you"s.

The same is true in the article:

"I am indebted in particular to one woman, whose name I will never know". "I also thank the Excel product team for shipping this technology without which this work wouldn’t be as famous."

I wish I knew how to reach out to the author (Sumit Gulwani) or other people in the Excel product team, about a feature that would be really hard to code but really useful to me.

Hierarchical menus of Sheets.

Like bookmarks folders, or Playlist Folders in iTunes, clicking on the Sheet Folder would show every row inside, concatenated into a giant Sheet. Clicking on the Sheet Folder would bring up a menu, which I could then navigate to find a sub-sheet. From a programming perspective I know that it's really hard to do hierarchical nested databases. But from a usability perspective, it would make some of our Excel documents with hundreds of Sheets much easier to navigate.

mshariff• 5 years ago

Do you mean like an index sheet with links so you can then go to the specific page?

We might have something for you…

MrHus• 5 years ago

If you do not know what flash fill is, like me, this page shows a video of the feature:

https://support.microsoft.com/en-us/office/save-time-with-fl...

duskwuff• 5 years ago

Alternatively, this video:

https://youtu.be/fRjsK-NXVEA

(Key word: "Maruary".)

sumitgulwani7• 5 years ago

Cool find! The 100-second video segment starting at 10:43 in this link that I am sharing would be more fun: <https://www.youtube.com/watch?v=421gU482xFE&t=643s>

willhinsa• 5 years ago

Seems like a lot more work than doing Data > Text to Columns :)

II2II• 5 years ago

As someone who has been handling delimited text for decades, Text to Columns is the most natural approach. That being said, different people have different backgrounds and approach problems from different angles. Flash Fill may be more appropriate in their cases.

sumitgulwani7• 5 years ago

Here's an illustrative demo of the Flash Fill capability. Check out the 3 minute video segment starting at 4:30. <https://www.youtube.com/watch?v=X1YXge3C8RI&t=270s>

ZeroGravitas• 5 years ago

This is very cool and I was unaware of this feature.

Are there any open source libraries that perform similar tasks? I can think of a few uses for this in data cleanup as long as it can handle the input not being super clean.

thristian• 5 years ago

As I understand it, this technology is heavily patented.

Hjfrf• 5 years ago

Powershell has the same functionality in Convert-FromString

sumitgulwani7• 5 years ago

Our program synthesis APIs are available publicly, but for non-commercial use only. <https://github.com/microsoft/prose> You want to look for API samples corresponding to Transformation.Text capability. This is a more powerful capability than Flash Fill. If you happen to try this out, we'd be very interested in getting your feedback on whether this is powerful enough to handle your use cases, and if not, we would love to be inspired by your use cases for a future version of this technology. You may reach us at prose-contact@microsoft.com.

thedudeabides5• 5 years ago

am I the only one that finds this feature distracting and unhelpful? Most of the times the recommendations are wrong, and then it's just more visual noise/keystrokes.

n8cpdx• 5 years ago

Do you try giving more examples? I find it usually only takes a few corrections to get it right.

You can turn it off if you don't like it. https://superuser.com/questions/766967/how-can-i-turn-off-fl...

pophenat• 5 years ago

It's mostly correct in my experience, but maybe it's because I've learned what it can/can't do. And when it does what it is supposed to do, it can be very useful.

copperx2• 5 years ago

You might be alone in that. Flash Fill saves me from doing so much manual work that I consider it a killer feature of Excel. I will never try another spreadsheet software until they get flash fill functionality (and I believe the feature is encumbered by patents, and hard to replicate anyways unless you are versed in program synthesis).

sumitgulwani7• 5 years ago

Thank you for your feedback. I would love to get more insights into your use cases---we can use them as inspiration as we continue to improve the technology in this space. Please feel free to send us your use cases where Flash Fill fails at prose-contact@microsoft.com.

You may consider watching this 5-minute video segment starting at 4:30 to get a sense of the current scope of Flash Fill (i.e., when it is expected to work, and when it isn't): <https://www.youtube.com/watch?v=X1YXge3C8RI&t=270s>

st0le• 5 years ago

Fun fact: Powershell 5.1 has a `Convert-String`[1] cmdlet that is a Flash Fill implementation for CLI. You can do these inferences in Command Line.

[1]https://docs.microsoft.com/en-us/powershell/module/microsoft...

CraigJPerry5• 5 years ago

I was hoping to browse the source for that cmdlet on github but it’s missing :-(

copperx2• 5 years ago

The code uses program synthesis, which I imagine is unreadable unless you're versed in that field.

julienfr112• 5 years ago

How does it handle middle name, or composite first name and second name ?

sumitgulwani7• 5 years ago

You would have to give multiple examples, at least one for each "different kind" of name (for which you would have programmed a different handling logic if you were programming the task yourself---the tool needs to see at least an instance of each of those logics---it has no understanding that something is a name; it simply treats the input as a sequence of characters).

bajsejohannes• 5 years ago

The paper outside a paywall: https://www.microsoft.com/en-us/research/wp-content/uploads/...

pbhjpbhj2• 5 years ago

I'm a very occasional Excel user, I can never seem to get it to do what I want on real spreadsheets -- it always trounces my number formats, or background colours, or repeats a short-periodic series instead of creating a series over the whole range. Seems like it could use an idiot mode with a pop-up box with options like "don't change any styles" and "make a series over the whole range" that also gives tips like "use $ in from of a reference to prevent it changing over the series".

TuringTest2• 5 years ago

That touches an important point of this kind of "automation magic" in autocompletion tools. They are great when they guess your intent correctly, and terrible when they misfire - specially when, as is common with Microsoft's tools, they are applied automatically and you have to take extra action to reverse them.

It annoys me that they research and apply complex algorithms to analyse the available data, detect the most likely ways to fill the document, build alternative programs to solve the problem and rank which is the most useful; only to then choose one of the inferred options and discard all the rest of the analysis work, getting only a (possibly incorrect) guess of the data completion task.

If, once the work is done, the user were allowed to see what the process was to detect the data, and were allowed to choose between the discarded options or give additional clues as to what the original intention was (the "active-learning session with the user to resolve the ambiguity" that the author of the article discards), the technique could be controlled more precisely and be useful in more situations, not just the ones that work the first time. Giving control to the user would make the technique more robust in my opinion instead of being hit-or-miss.

sumitgulwani7• 5 years ago

I absolutely agree with you! The next version of Flash Fill that we are aspiring to build will not only handle a much larger class of transformations, but will hopefully also show you the code, and suggest you corner cases for inspection and soliciting more examples from the user. Allowing the user to browse through multiple alternatives is also a good idea.

What I meant to say was that such a rich interactivity should not be the default experience for simple and common cases. For simple and common cases, the technology should just work automatically without requiring much user interaction, thereby promoting usability and discoverability. However, you are absolutely right that for more sophisticated cases, instead of letting the user fall off the cliff, we should invest in a rich interactive experience where the AI can partner with the user to help complete more sophisticated tasks.

Taniwha• 5 years ago

Let me tell you another story about fill in Excel ..... many years ago when the Mac was getting its very first graphics cards I worked for 3rd party hardware manufacturer who had decided to build our first graphics card, in particular 24-bit screen were deathly slow .... people would switch to 8-bit or even 1-bit to use things like Excel and word processors.

I started out intending to design fast vector hardware (because autocad was a thing back then), the end though some serious benchmarking of real world usage meant that we zeroed in on simply making solid fills go really gas (Gbytes/sec, faster than any SGI machine of the time) - one of the big surprises was how fast Excel went with just this hardware speedup - turns out that at times excel would clear a window up to 9 times before ever drawing a useful pixel - all those 'important' black pixels I wanted to make hardware for (character rendering and vectors) were just noise in the traces.

Not just MS: Pagemaker? did something stupid that invalidated the font cache many times a page, Quark? put 2 blank spaces at the end of every line that caused ATM to do stupid N*2 clipping behaviour ..... we could make great hardware and the software guys would just piss it away

leeoniya• 5 years ago

we could make great hardware and the software guys would just piss it away

my #1 annoyance with web dev at-large.

you load any web page and your CPU spends 90% of its capacity rendering inefficient, shitty, injected ad code. (speaking as someone who tries to squeeze the most out of modern JS JITs and web platform APIs)

nojito• 5 years ago

I wonder if this is the same tech behind columns from examples in Power Query?

That and flash fill has saved countless hours to my team.

Story of the Flash Fill Feature in Excel

Related Stories