Learn how to work with Flash Fill.
Working with Flash Fill
Much like AutoComplete and AutoFill, whereby Excel guesses what you want to type or do based on the nature and location of content already in your worksheet, Flash Fill looks at any target range of cells and based on the content in contiguous cells, it will fill them in for you, based on a sample selection.
To explain that further with a practical example, imagine you have a list of people’s names, currently in two columns – First Name and Last Name. In a third column, you have their Full Name, which to this point – because you didn’t know about Flash Fill – you would have combined manually, or filled in with a CONCATENATE function, which we cover in a different video.
Well, if you can do it manually for the first person in that list, Flash Fill will do the rest of the work for you, combining the first and last names for everyone else in your list. Let me demonstrate.
Here I’ve got a list of people who work for a company. I’ve got their first and last names, and in a third column, I have space for their full names. I’ve combined the first person’s first and last names, and that appears in the Full Name column. If I then click in the cell below that – where the second person in the list’s full name would go, I can press Ctrl + E (or go over to the Data tab and click Flash Fill, but the shortcut is easier), and like magic, everyone’s full name is filled in!
Want more magic? Check out my new 4th column, which creates a Last Name, comma, First Name configuration. All I have to do is complete the first combination, and then click where the second person’s combination would go, press Ctrl + E, and Excel does the work for me. All the names combined, exactly like the first one.
If you’re like me, the first time you do this with data you were not looking forward to entering manually or trying to wrangle with a function, you might let out a small gleeful sound when you see those names – or whatever you’re combining – appear in a flash.
TIP! If Flash Fill doesn't seem to work, it might not be turned on. To enable Flash Fill for now and in the future, go to the File tab, choose Options, and from the Excel Options dialog box, select Advanced, and in the Editing Options section, be sure that there’s a checkmark next to the Automatically Flash Fill option.