Flash Fill works similar to the way that Autofill works in a web browser but is much more intelligent. Excel’s AutoFill works somewhat differently, allowing you to copy formula’s and values across horizontal or vertical arrays in a spreadsheet. Excel actually uses machine learning for Flash Fill, which allows it to make clever suggestions for what you might want to be entering.
How to Use Flash Fill in Excel
There are two ways to use flash fill in Excel. It is always working in the background and will make suggestions as you type into any cell. The simplest and most common way to use Flash Fill is to accept these suggestions by hitting the Enter key. The second way to use Flash Fill is using a command on the DATA tab of the ribbon to automatically fill a number of cells at once. Flash Fill is currently quite limited in its capabilities. It only works when the column you’re entering data into is directly adjacent to the column containing the source data. The source data is the data containing the pattern that Flash Fill will use to generate values. Somewhat strangely it only works with columns and not rows, so you can’t automatically populate a row of data.
Using Flash Fill As You Type
While you’re typing, Flash Fill is always working in the background. If it thinks it can see what you’re trying to enter in the column, it will make a suggestion for how you could instantly complete the whole entry. Used in this way, you can think of Flash Fill as an extension of Excel’s AutoComplete. While AutoComplete makes suggestions for what you might be typing into an individual cell, Flash Fill allows you to complete whole columns of data. To use Flash Fill while you’re typing, follow these steps: You can continue to use Flash Fill in subsequent columns to extract different items of data. As long as the column containing the pattern isn’t separated from the column where you are entering the values, Excel should detect the pattern. Continuing the previous example, the names could also be extracted.
Using Flash Fill From the Ribbon
Where Flash Fill detects a pattern, it usually shows the preview while you’re typing. Hitting Enter to accept this suggestion is normally the easiest way to use Flash Fill. However, it can also be used via a command on the ribbon. To use it in this way follow these steps:
Combining Data From Multiple Columns
Flash Fill isn’t only able to split values into separate columns. It can also be used to merge columns using any pattern you choose. This can be a lot quicker and more intuitive than using concatenation formula. For example, to create a single address column from individual address fields you could follow these steps: Note that in this example commas were used between the street, city and country but not the number and street. Flash Fill should work with any format you choose to use.
Other Capabilities of Flash Fill
Flash Fill can simplify many data entry tasks. As well as splitting and combining columns, Flash Fill can remove leading zeros, where only some entries have them. It can be used to format text, number and dates. It can replace part of a cell content, perhaps creating a secure version of sensitive data by replacing some numbers with XXXX. Different functions such as combining values and adding characters can even be combined. To perform any of these functions, the process is the same. You follow these steps: Once you start to get to grips with what Flash Fill is capable of you’ll find it an extremely powerful and useful tool. If you’re interested in another really useful tool to simplify data entry, check out how to use AutoFill in Excel, this can copy formulae and values in an intelligent way across a spreadsheet.
