How to Use Flash Fill in Excel
Flash Fill is an Excel feature that intelligently spots patterns in your data to fill a range of cells.
It can be used for things like:
- combining a list of first and last names
- splitting a list of names
- returning the initials from a list of names
- generating a list of email addresses
The process is:
- Type an entry into a column cell adjacent to the original data.
- Go to Data (tab) > Flash Fill (button) or press Ctrl + E.
- If Flash Fill determines a pattern, the cells will be filled accordingly.
As an alternative to step 2, type a second entry for Excel to display suggestions, which you can confirm by pressing Return — or Esc to reject them.
Turn off the suggestions by going to File (tab) > Options (button) > Advanced (button) and unticking the Automatically Flash Fill checkbox.
Flash Fill is best suited for quickly generating flat data. Use a formula for complex patterns, as it is likely to struggle to solve them. Formulas should also be used in cases where source data is subject to change, as Flash Fill will not update the cells automatically.