Want to manipulate your data as quick-as-a-flash? Don’t rely on a complex combination of functions such as FIND, LEFT, CONCATENATE, or Text to Columns. Flash Fill is a powerful and time-saving tool that automatically fills your data when it senses a pattern.
Let’s say you have a list of GL accounting codes, and you want to extract the integer part of the code. Just complete the first cell, and Excel will generate a preview as you type to complete the list based on your provided pattern. Press Enter to accept the suggestion.
Just like magic, your data entry is complete and free from errors.
You can even use Flash Fill to combine and adjust your data.
To turn on Flash Fill go to File > Options > Advanced > Editing Options > select the Automatically Flash Fill box.
Or run it manually by clicking Data > Flash Fill, or use shortcut Ctrl+E.
Things to remember:
1. Flash fill works best when the source data is consistent.
2. Flash Fill is not dynamic, so if you change the source cell, the result doesn’t update automatically.
3. To ensure that Flash Fill recognises a pattern, you need to use Flash Fill close to the source data.
You can see another example of Flash Fill on Microsoft's Support page.