Flash Fill lets you enter data into Microsoft Excel much faster. It monitors trends in the data you enter and makes suggestions to complete your input by populating entire columns with relevant values. Read on to learn how to use Flash Fill in Excel to increase data entry speed and accuracy.
The instructions in this article apply to Excel 2019, 2016, and 2013; Excel for Microsoft 365, Excel Online, and Excel for Mac. Flash Fill is not available in earlier versions of Excel.
Flash Fill works the same as Autofill in a web browser, but is much smarter. Excel autofill works a little differently, allowing you to copy formulas and values into horizontal or vertical tables in a spreadsheet. Excel actually uses machine learning for Flash fill, which allows it to make clever suggestions about what you might want to type.
There are two ways to use Flash Fill in Excel. It’s always running in the background and giving you suggestions as you type in a cell. The easiest and most common way to use Flash Fill is to accept these suggestions by clicking the button Enter Key. The second way to use Flash Fill is to use a command on the button DATA Ribbon to automatically fill multiple cells at once.
Flash Fill is currently fairly limited in its capabilities. It only works if the column you are entering data into is right next to the column that contains the source data. Source data is the data that contains the pattern that Flash Fill uses to generate values. Oddly, it only works with columns, not rows, so you can’t autofill a row of data.
Use Flash Fill as you type
Flash Fill is always working in the background while you type. If it thinks it can see what you are trying to type in the column, it will suggest how to complete the entire entry immediately. Used in this way, you can think of Flash Fill as an extension of Excel’s AutoComplete. While AutoComplete suggests what you might type in a single cell, Flash Fill lets you complete entire columns of data.
To use Flash Fill as you type, follow these steps:
- Add a new column next to the source data column.
- Enter the required value in the first cell of the new column.
- Start typing the value in the second column. When Excel recognizes the pattern, a preview appears with the values it will use for the remaining cells.
- Press the key Enter and the values will be inserted automatically.
- As simple as that !
You can still use Flash Fill on subsequent columns to extract other data. As long as the column with the pattern is not separate from the column in which you enter the values, Excel should recognize the pattern. Continuing the previous example, names could also be extracted.
Using Flash Fill from the ribbon
Normally, when Flash Fill recognizes a pattern, it shows a preview as you type. Meet at Enter Accepting this suggestion is usually the easiest way to use Flash Fill. However, it can also be used via a command on the ribbon. To use it this way, follow these steps:
- Add a new column next to the source data column.
- Enter the required value in the first cell of the new column.
- Choose DATA on the tape.
- Choose flash fill ordered.
- When Excel recognizes a pattern, it fills the remaining cells with its suggested values.
Combining data from multiple columns
Flash fill does not simply split values into separate columns. It can also be used to merge columns with the pattern of your choice. This can be much faster and more intuitive than using a concatenation formula. For example, to create a single address column from individual address fields, you can do the following:
- Enter each address field in its own column.
- Create an adjacent address column.
- Enter the first address in the required format.
- Start typing the second address and check the values suggested by Excel.
- Press Enter to accept the Flash fill suggested by Excel.
Note that in this example, commas were used between street, city, and state, but not between house number and street. Flash Fill should work with whatever format you choose to use.
Other flash fill skills
Flash Fill can simplify many data entry tasks. In addition to splitting and combining columns, Flash Fill can remove leading zeros if only certain entries have leading zeros. It can be used to format text, numbers and dates. It can replace part of a cell’s content and potentially create a secure version of sensitive data by replacing certain digits with XXXX. Different functions like combining values and adding characters can even be combined.
To perform any of these functions, the process is the same. You follow the following steps:
- In a column, enter the raw data that needs to be cleaned or reformatted.
- Enter the desired data in the first cell of an adjacent column.
- Start typing the second value and Flash Fill will suggest how to fill all other values in the column using the same pattern.
As you begin to explore what Flash Fill can do, you’ll find that it’s 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, which can intelligently copy formulas and values into a worksheet.