How To Use Flash Fill In Excel

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.

Flash Fill Example
  1. Type an example of what you want in cell B1. In this example “86”
  2. Select cell B2 and type “67” to activate the Flash Fill function
Flash fill activated
  1. Press Enter to fill the entire column with the same pattern. Alternatively, press Escape to manually fill in the records.
Flash fill complete

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 shortcutsCtrl + E” or “Alt, A, F, F”.

Flash fill shortcut

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.

Flash fill dashed example

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!

Flash fill names example

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:

Leave a Comment