Quick as a Flash with Excel 2013

A new feature that's not perfect yet

Excel’s brand new Flash Fill feature can be a time saver, but it’s not fool proof. Flash Fill is a new feature in Excel 2013.

Flash Fill allows you to add entries to a table of data based on the other entries around it. For example if you have two columns, one for First Name and one for Last Name, you could create a third column for Full Name and have Excel populate it for you.

All you need to do is to put the first entry in the cell and then start typing the second entry. Excel will watch what you do and suggest the remainder of the range – see image below which shows Flash Fill working on the right or the left of the data.

Flash Fill example

Flash Fill looks in the column to the left or to the right and tries to figure out what you are entering and it tries to copy that for the other cells. If it’s wrong you can undo it.

Flash Fill only works on a column. You can’t select a range of cells across columns and use Flash Fill.

A Flash Fill Icon is displayed after it has entered the values. The options are shown in the image below.

Flash Fill icon

You can also type the first entry and then drag the cell’s Fill Handle with the RIGHT mouse button to see the Flash Fill option.

Flash Menu item

In terms of splitting cells up, it’s not quite as reliable. See the example below where I try to extract the first, middle and last names from a full name.

I tried the first example below and it worked for first name and last name and middle name if all the rows had a middle name.

Flash_04If there was no middle name in one of the entries, as in row 3 of the bottom example,  Flash Fill didn’t work correctly.

I even tried selecting two cells B2 and B3 in the example below, one with a middle name and one blank (no middle name). It still didn’t help when I dragged with the Flash Fill. See below result.

Flash Fill two cells

Flash Fill is not dynamic, so adding an entry to a range won’t automatically extend it, you have to re-apply it.

Conclusion

Flash Fill seems to be the answer to a lot of questions, but it does have limitations, so use it carefully. It looks like, in the short term, you’ll still need to know how to use Excel’s text functions.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.