Learn how to use Excel's Flash Fill feature to save time and increase efficiency in your data entry tasks. Discover how Flash Fill can autofill cell content based on your initial input and understand its practical applications in organizing and managing data.
Key Insights
- Flash Fill in Excel is a tool that helps automate data entry based on the content in adjacent cells.
- By providing a sample selection, Flash Fill can effectively predict and fill in the remaining cells in your target range.
- For example, if you have a list of first names and last names in separate columns, Flash Fill can automatically combine these into a full name in a third column, after you manually input the first full name.
- The process can be quickly executed by pressing Ctrl + E or by selecting Flash Fill from the Data tab.
- Flash Fill can also rearrange data in new formats, like creating a Last Name, First Name configuration from existing data.
- If Flash Fill does not seem to work, you may need to enable it in Excel's Options under the File tab.
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.