Many of us deal with data sets on a daily basis. Generally, it doesn’t come in the format that you need it to! In Excel 2013, Microsoft decided to tackle this issue.
What Is Flash Fill?
The Flash Fill function guesses the pattern you are typing and AUTOMATICALLY fills the rest of the column with a button click. Massive time saver!
I’ve found that this is really useful when trying to separate a column into several meaningful columns.
When To Use Flash Fill In Excel?
Let’s use an example – say I want to extract the numbers in the middle of this sequence. Instead of using a complicated formula, I can use the Flash Fill function.
- Type an example of what you want in cell B1. In this example “86”
- Select cell B2 and type “67” to activate the Flash Fill function
- Press Enter to fill the entire column with the same pattern. Alternatively, press Escape to manually fill in the records.
Shortcut For Flash Fill In Excel
To use the shortcut, you’ll need to populate B1. Once the “86” has been keyed in, highlight the range B1 to B5. Then use the keyboard shortcuts “Ctrl + E” or “Alt, A, F, F”.
Interesting read – How To Format Like An Excel Wizard
More Examples Of Flash Fill
Adding Dashes, Spaces, Or Any Separators
If you have a dataset with a bunch of numbers, you can add separators to help make them a bit more legible. Type your pattern into cell B1, highlight the range B1 to B9, Ctrl+E and let Excel work it’s magic.
Splitting Names And Adding Emails
There will be times when you have full names populated in a single cell. Flash fill can help split these and also create email addresses for you.
In the example below, if I wanted to populate all the first names, I would click on cell B2, then use the shortcut Ctrl+E. Repeat in columns B and C. Voila, data all split up!
Interesting read – How To Auto Resize Image In Excel Cell
Excel Flash Fill Not Recognizing Pattern
Flash fill is not meant to be a replacement for autofill or dragging formulas. It was designed for instances where you have a column that has data that you want to split across several columns (like the examples above).
If you think that your data set is suitable and it’s still not working, try a few of these tips:
- Add a couple more examples, Excel might not have been able to detect the pattern/sequence
- Make sure there are no hidden columns in between the dataset and the column you’re trying to populate
- Make sure there’s nothing populated already further down the column
As always, hope you found this helpful and that it saves you a ton of time!
Video Guide
You may also like to read: