How To Use Flash Fill In Excel?

Trying to fill cells in Excel that contain text or data from multiple other cells in a sheet can be a very time-consuming process. This is especially true if there are hundreds or thousands of rows in the spreadsheet.

Once you know how to properly use Flash Fill in Excel, you can let Excel do all the heavy lifting. You just need to provide a few manual cell entries to help Excel understand exactly what you’re trying to do. Then Excel does the rest of the work for the rest of the spreadsheet.

If this sounds like a time-saving tip you want to try, let’s see how you can use this feature to improve your own productivity.

Remark: The Flash Fill in Excel function is only available in Excel 2013 and later.

How To Use Flash Fill In Excel?

The easiest application of Flash Fill in Excel is to combine two words. In the example below, we’ll show you how to use Flash Fill to quickly combine a first name and last name in a third cell for the full name.

In this example, column C contains the first name, column D is the last name, and column E is the column for the full name.

  1. First, type the full name in the first cell as you want it to look (combination of the contents of the First Name cell and the Last Name cell.
  1. After that, start typing the same in the next cell (the first name and last name of the cells on the left). You’ll notice that Excel’s Flash Fill function automatically detects the pattern from the contents of the cell above it that you typed manually.

    Using this “learn” Excel gives a preview of what it thinks you want to type. It will even show you how the rest of the cells will fill in the rest of the column.

  1. Just press Enter to accept this example. You will see the Excel Flash Fill function work its magic as it fills all the other cells in this column for you.

As you can see, the Flash Fill function can save a lot of time if you compare manually typing one cell and Enter with manually typing the names for all cells in the new column.

If you find the Flash Fill function not working, you need to enable the Flash Fill function in Excel. You can read how to do that in the last section of this article.

Excel Flash Fill options

When you perform the above Flash Fill steps, you will see a small icon 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 you can use with the Flash Fill feature.

Using the Flash Fill options in this drop-down list, you can:

  • Undo Flash Fill: undo the action that filled the entire column after pressing Enter
  • Accept suggestions: This will tell Excel’s Flash Fill function for this column that you agree with the changes in the column and want to keep them
  • Select xx blank cells: Allows you to identify all cells that are unfilled and empty so that you can correct them if you need to
  • Select xx changed cells: Allows you to select all cells that changed automatically after Flash Fill function updated those cells

Once you select Accept suggestions, you will see that the “select” numbers for the “changed cells” will drop to zero. This is because once you accept the changes, that cell contents are no longer considered “modified” by the Flash Fill function.

How to Enable Flash Fill in Excel

If you’ve noticed that Excel doesn’t provide a Flash Fill preview when you start typing the second cell, you may need to enable the feature.

To do this:

Select FileOptionsAdvanced† Scroll down to the Editing options section and make sure both Enable auto-complete for cell values and Automatic Flash Fill are selected.

Select Okay to finish. Now, the next time you start typing in the second cell after filling in the first, Excel should detect the pattern and give you a preview of how it thinks you want to fill in the rest of the cells in the column.

You can also activate the Flash Fill function for the cell you marked by selecting the Flash Fill icon in the Facts menu under the Data Tools group in the ribbon.

You can also use it by pressing Ctrl + E on the keyboard.

When do you use Flash Fill In Excel?

Concatenating full names from two columns is a simple example of how to use Flash Fill in Excel, but there are many more advanced uses for this powerful feature.

  • Extract a substring from a large string (such as a zip code from a full address) in another column
  • Extract numbers from alphanumeric strings
  • Remove spaces before or after strings
  • Insert substrings (such as a comma or dash) into existing strings
  • Reformat dates
  • Replace part of a string with new text

Keep in mind that as useful as the Flash Fill feature is, it doesn’t update automatically when you change the original cells.

For example, in the first and last name example in the first section of this article, you can achieve the same output by using the Merge function and then filling the rest of the column with that function.

= CONCATENATE(C2,” “,D2)

When you do this and change one of the first two cells, the full name is updated. One drawback to this is that if you delete one of the first two columns, the Full Name column will be cleared or an error will be displayed.

This is why the Flash Fill function in Excel is best used when you want to completely and permanently convert the original columns to a newly formatted string or number.

Leave a Reply

Your email address will not be published.