Flash Fill in Excel
Excel is one of the powerful suits provided by Microsoft. It incorporates user friendly tools and amazing features and one such incredible feature is Flash Fill. This tool eliminates the manual process where the user had to invest their time by typing the data. Not only does it take hours to be performed but often leads to many typing errors. To save time and prevent typo errors Excel came with the Flash Fill option that executes the same operation automatically (if it involves any pattern) in a flash.
In this tutorial we will briefly discover the fundamentals of the Flash Fill functionality including its definition, shortcuts, various Flash Fill options and will perform examples using Flash Fill in Excel.
What is Flash Fill?
"Excel Flash Fill is a special feature introduced by Microsoft that examines the data entered by the user and if it involves any kind of pattern it automatically fills the remaining information in a single click."
Flash Fill quickly copes with several different tasks that otherwise need complicated formulas or even lengthy VBA code, unlike splitting and merging strings, sorting the data and correcting deviations, formatting the content or the numbers, turning dates figure to the required format, and a lot more.
Every time you apply Flash Fill to your Excel worksheet at the backend to accomplish that, it merges millions of multiple small programs, sorts those program snippets utilizing machine-learning algorithms, and ascertains the flawless job. To our surprise, all these activities are done in milliseconds. Therefore delivering the output almost immediately!
Where is Flash Fill in Excel?
In Excel, to locate the Flash Fill tool, follow the given steps:
Flash Fill shortcut key
One of the reasons why Excel is popular among its users is because of its shortcut keys. Excel provides shortcut key keys for almost every operation. Those who quickly want to access Flash Fill without using a mouse can run it directly from the keyboard using the shortcut keys: Ctrl + E.
Steps to implement Flash Fill in Excel
Whenever Excel comes across any type of pattern, it automatically suggests the output using the Flash Fill tool. Therefore to implement this tool, the user only needs to provide a pattern.
How to apply Flash Fill using shortcut
In most conditions, Flash Fill fits in automatically as soon as Excel verifies the data in the form of some pattern. If, due to any reason, the suggestion or the preview does not appear, you can apply the Flash Fill tool manually by following the below steps:
Excel Flash Fill options
Flash Fill is a very versatile tool that allows the user to automate their data entry in Excel. The Flash Fill Options button is displayed next to the auto-filled cells. Clicking on the options menu button will open the following window:
Excel Flash Fill examples
While working in Excel, you often need to format the data, such as pin code, phone numbers, country code, etc. You may also want to remove the leading or trailing spaces between your content or the improper use of punctuation in your content. If you work manually, it could take long hours to fix all these errors, and still, there are chances of errors, whereas the same task can be accomplished in a blink of time with the help of Flash Fill.
Let's understand the capabilities of this versatile tool through some real-life examples:
1. Extract text from cell (split columns)
Extracting content from Excel data is one of the basic operations carried out by millions of Excel users daily. Before Flash Fill came into the picture, users needed to do complicated text manipulation or even intricate VBA coding.
NOTE: Flash Fill is used to extract any substring, numbers from string, zip codes, or even allows the user to split cells in Excel.
2. Concatenate data from different cells
Flash Fill allows the user to concatenate the data in an Excel spreadsheet. Earlier, without Flash fill, it was a lengthy process as the user needed to apply the formula first to concatenate and then format it with proper punctuation.
In the below spreadsheet, we need to combine the first name, middle name, and last name to a new column.
It can combine two cells that can further be separated with characters such as space, comma, semicolon, etc., Type in the value in the first two cells and apply the flash fill.
After implementing Flash Fill, all the cells will be automatically filled and will have the following output.
3. Clean data
Maybe you are working with Excel worksheets that are extracted from a database or other data source platforms. The data extracted comes along with unfinished formattings, such as it could contain leading space or undefined punctuations.
For example, in the below spreadsheet, we have undefined characters and leading and trailing spaces in our data.
Type the first name without any preceding space, repeat the same for the second cell and apply the Flash Fill. You will get the following output.
4. Format text, numbers and dates
Formatting your Excel spreadsheet is necessary as it makes your data look clean and make it more readable. Sometimes while working with Excel your data is formatted in some style while you want it in another. In the new column type the first two values exactly as you want them to look like , and apply the Flash Fill.
Limitation of Flash Fill
Undoubtedly, Flash Fill is a special tool, but it too has some restrictions. Below given are some limitations of this tool:
How to turn off Flash Fill
Though by default Excel automatically turns on the Flash Fill feature. But sometimes, the automatic suggestion or the preview could be disturbing, or the user doesn't want the data to be filled automatically. In that case, they can turn off or disable the Flash Fill tool from their Excel worksheet. Follow the below steps: