Trying to fill cells in Excel that include text or data from several other cells in the worksheet can be very time consuming. This is especially true if there are hundreds or thousands of rows in the table.
If you know how to use Flash Fill correctly in Excel, you can let Excel do all the hard work. You just enter a couple of cells manually so Excel knows exactly what you are trying to do. Excel will then do the rest of the work for the rest of the table.
If this sounds like a time-saving tip you’d like to try, let’s see how you can use this feature to increase your productivity.
Note. Flash Fill in Excel is only available in Excel 2013 and later.
How to use Flash Fill in Excel
The simplest use of Flash Fill in Excel is to combine two words together. In the example below, we’ll show you how to use Flash Fill to quickly concatenate the first and last name into a third cell to get the full name.
In this example, column C contains the first name, column D contains the last name, and column E is the column for the full name.
– / pre>
- First enter the full name in the first cell as you wish (by combining the contents of the First Name cell and the Last Name cell.
- Then start typing the same into the next cell (First and Last name from the cells on the left). You will notice that Excel’s Flash Fill function will automatically detect the template based on the content of the cell above it that you entered manually.
Using this “training”, Excel will provide a preview of what it thinks you want to enter. It will even show you how the rest of the cells will fill the rest of the column.
- Just press Enter to accept the preview. You will see Excel’s Flash Fill function perform its magic by filling in all the other cells in this column for you.
As you can see, Flash Fill can save you a huge amount of time when you compare manually entering one cell and Enter versus having to manually enter names for all the cells in a new column.
If you notice that Flash Fill is not working, you need to enable Flash Fill in Excel. You can learn how to do this in the last section of this article.
Excel flash fill options
After completing the instant fill steps above, you will notice that a small icon appears next to the filled cells. If you select the drop-down arrow to the right of this icon, you will see some additional options that can be used with the Flash Fill feature.
Using the Flash Fill options in this dropdown list, you can:
- Undo Flash Fill: Undo the action that fills the entire column after pressing Enter.
- Accept Suggestions: This will tell the Flash Fill Excel function for this column that you agree with the changes in the column and would like to save them.
- Select xx Blank Cells: Lets you identify any cells that are not filled and remain blank so that they can be corrected as needed.
- Select xx Modified Cells: allows you to select all cells that have automatically changed after Flash Fill has refreshed those cells
After you click on “Accept offers”, you will see the “select” numbers for “modified cells” drop to zero. This is because once you commit the changes, these cell contents are no longer considered “modified” by the Flash Fill function.
How to enable flash fill in Excel
If you notice that Excel does not provide a preview of the Flash fill when you start typing the second cell, you may need to enable this feature.
To do this:
Select File> Options> Advanced. Scroll down to the Editing Options section and make sure Enable AutoComplete for Cell Values ??and AutoFill is selected.
Click OK to finish. Now, the next time you start typing the second cell after filling the first one, Excel should detect the template and give you a preview of how it thinks you want to fill in the rest of the cells in the column. P>
You can also activate Flash Fill for a selected cell by choosing the Flash Fill icon from the Data menu in the Data Tools group on the ribbon.
You can also use it by pressing Ctrl + E on your keyboard.
When to use flash fill in Excel
Combining full names from two columns is a simple example of how you can use Flash Fill in Excel, but there are many more complex uses for this powerful feature.
- Extract a substring from a large string (like a zip code from a full address) in another column.
- Pull numbers from alphanumeric strings.
- Remove spaces before or after lines.
- Insert substrings (such as comma or dash) into existing strings
- Change date format
- Replace part of a string with new text
Keep in mind that as useful as Flash Fill is, it won’t automatically update when the original cells change.
For example, in the first and last name example in the first section of this article, you can achieve the same result by using the Concatenate function and then filling the rest of the column with this function.
= CONCATENATE (C2; “”; D2)
When you do, if one of the first two cells changes, the full name will be updated. One disadvantage of this is that if you delete one of the first two columns, the “Full Name” column will clear or display an error.
This is why the Flash Fill feature in Excel is best used when you want to completely and permanently convert the original columns to a new formatted string or number.
–