AI doesn’t always require large, complex applications. Even a simple tool like Flash Fill can make you feel like an AI master:
In this post, we’ll take a look at the well-known Flash Fill feature that is available in most versions of Excel, how to get the most out of it and how it counts as AI. Follow along with the exercise file:
What is Flash Fill?
Flash Fill is an AI-powered feature that automatically predicts patterns in data and fills in adjacent cells accordingly, streamlining data formatting and manipulation tasks for users.
How to use it
There are a few ways to activate Flash Fill, all of them fairly straightforward.
Just start typing!
Probably the easiest way to use Flash Fill: just start typing! In fact, you might have already used it without even knowing. For instance, consider the following example where we aim to extract first names from a range of cells:
While this could have been accomplished with a variety of ways in Excel, including Text to Columns, there’s something so satisfying and well, artificially intelligent about Flash Fill, right?
Through the home ribbon
Here’s another example: extracting the year from a range of dates. If typing doesn’t trigger Flash Fill, access it from the Data tab of the ribbon and choose Flash Fill:
Ctrl + E
You can also activate Flash Fill with the keyboard shortcut Ctrl + E, in this case to extract the last four digits of a credit card number:
What if Flash Fill doesn’t work?
If you encounter issues triggering Flash Fill, ensure that it is turned on. To do this, navigate to File on the ribbon, then go to Options > Advanced > Editing Options, and check the Automatically Flash Fill box.
Limitations of Flash Fill
True to its name, Flash Fill offers a rapid solution for identifying patterns in your data. However, it only operates in cells immediately adjacent to the source data cells. Furthermore, the results of Flash Fill are not dynamic. If you modify or add to the source data, Flash Fill will not automatically adjust to accommodate the changes:
This is why foregoing Flash Fill for more robust methods like formulas or Power Query may be a better option for ongoing data preparation tasks. Fortunately, a Formula by Example feature is rolling out to Excel that combines the auditability or formulas with Flash Fill’s ease of use.
For now, we’ll celebrate our big, quick data cleaning win with Flash Fill:
How is Flash Fill considered AI?
Flash Fill is considered AI because it employs advanced algorithms, pattern recognition, natural language processing, and predictive input to automate data transformations. As users start typing in a new column, the AI engine analyzes adjacent data to identify patterns. It also adapts based on additional user input, continuously improving accuracy. Its ability to automate complex tasks without requiring manual formulas or scripts makes it a textbook AI feature.
Fun fact: Flash Fill started from a serendipitous meeting of a Microsoft researcher and a businesswoman on a flight. To learn more about how a scientific research project became a tool now used regularly by millions, check out this post.
What questions do you have about Flash Fill in Excel specifically or AI for Excel more generally? Let me know in the comments.
Leave a Reply