Blog

You are probably familiar with Excel’s very powerful Autofill feature (if not, learn more about Autofill here and then read on). However, did you know there is a similar feature that is equally useful called Flash Fill?

Say you have information in Excel not formatted the way you need it to be and going through the entire list manually to correct it seems daunting. In this case, Flash Fill can do the work for you.

What is Flash Fill?

You use the Flash Fill feature to automatically fill-in values in one column from information referenced from another, adjacent column (extract, combine/concatenate, insert characters, reverse data, change case, format, etc.).

Flash Fill Examples

• Splitting a single name column into separate first name and last name columns (or vice versa)
  • Sample Scenario: If you have a column that contains the first and last names of employees, you could enter just the last name of an employee into the adjacent column and then use Flash Fill to fill-in the corresponding values of all of the last names of the employees within the column. This can greatly reduce your dependence on text-parsing functions such as LEFT, RIGHT, MID, FIND, LEN, UPPER, LOWER, etc. within Excel.

• Adding hyphens and parentheses to unformatted phone numbers or social security numbers
  • Sample Scenario: Use Flash Fill to format previously unformatted phone numbers of social security numbers. For example, if you had an adjacent cell with the value “9135551111” and then entered “(913) 555-1111” into the next column for the flash fill entry, all of the other flash-filled values would also use hyphens and parentheses in the formatting.

• Changing lower case to upper case characters (or vice versa)
  • Sample Scenario: You can even use Flash Fill to change the text case. For example, if you had an adjacent cell with the value “Jon Doe” and then entered “doe” into the next column for the flash fill entry, all of the other flash-filled values would also use all lowercase last name entries.

How to Use Flash Fill in Excel

1. Click in a blank cell within the column adjacent to the column that contains the data you want to retrieve.
2. Then enter a data value (formatted as necessary) to extract from the adjacent cell.
3. Finally, click in the next cell below the entered value and then do one of the following:
   • Type a second entry to establish the pattern for Excel to use
   • Press “Ctrl” + “E” on your keyboard
   • Click the “Data” tab in the Ribbon and then click the “Flash Fill” button in the “Data Tools” button group
   • Right-click the autofill handle as you drag down and then choose “Flash Fill”

Excel should then extract all of the corresponding data values from the adjacent column to fill-in the cells within the current column. After you flash fill a column, you can press “Enter” to accept the suggestions or click the drop-down arrow next to the small icon that appears next to the values entered to display a menu of choices:

• Select the “Undo Flash Fill” command to undo an unsuccessful flash fill attempt.
• Select the “Accept suggestions” command to accept the flash fill changes.
• Select the “Select blank cells” and “Select changed cells” commands to select the blank cells or changed cells within the flash filled column.

When using the Flash Fill feature, the referenced data column can be non-adjacent as long as there is adjacent column data between the flash filled column and the referenced column. In other words, you cannot have an empty or blank column between the column that is being flash filled and the column values referenced. In this case, Excel will display a message box that informs you that it cannot find a pattern in the adjacent data.

A Word of Caution

Flash fill works best when the referencing values have a consistent pattern of data entry. For example, if you are referencing values within a column where some names have a middle name, some do not, and others have just an initial, Flash Fill may parse the incorrect information from the fields that it references when it fills-in the column’s values. So be sure to double-check the values returned by the Flash Fill feature.

If errors occur, you can type more examples to help Flash Fill to “see” what you want it to do or you can undo or delete the entries made by the Flash Fill feature and then use the traditional text-parsing functions within Excel (mentioned above) to try to extract the needed values instead.

Conclusion

As you can see, Flash Fill can be a tremendous time saver. Now that you know what it is, when and why you should use it, and how to use it, experiment with your own data!

For more information about Flash Fill and other Excel features, take an Excel Class from Centriq.

Be sure to watch for future blog articles in this Excel series. The next topic will feature Fantastic Formulas and Functions in Excel and later posts will discuss Awesome Add-ins and Super Settings. Centriq occasionally hosts free Excel Lunch and Learns that feature live demonstrations of items covered in posts like this. See the latest Excel Lunch and Learn here.

And, if you’d like more information about Excel or other training classes available, contact a Centriq Training Advisor online or at 913.322.7062 for assistance.