Why Write Excel Formulas When You Can Flash Fill?

A lot of times the data you have to work with in Excel isn’t quite the way you need it to be, and going through a giant set of data to manually correct it is monotonous and time-consuming. Sure you can do a lot of things with a few crafty formulas but once you see what the Flash Fill function can do you’ll be totally amazed.

Excel 2013’s Flash Fill feature allows you to take a part of the data entered into one column of a worksheet table and enter just that data in a new table column using only a few keystrokes.

As soon as Excel detects a pattern in your initial data entry it ‘smartly’ figures out the data you want to copy offers a way to type the rest of the data for you. Accept it and the rest of the cells are automagically transformed!

And the cool thing about it is that you never need fuss with building any kind of formula. It just works!

Flash fill is great for so many things beyond basic number and date series – especially, when it comes to cleaning data.

Extracting Cell Data

Sometimes your data is combined in ways you don’t want. Flash fill can easily handle pulling your data apart just the way you want it.

Combining Cell Data

Of course, there are also times when you want just the opposite and you need to combine multiple cells into one. No problem!

Converting Data Formats

Often you get data like phone numbers, dates, social security numbers, etc that are not formatted the way you want them. Flash fill makes the job of fixing them a snap.

By default, Flash Fill will be always listening and offers suggestions whenever it can. If you want to turn it off go to File » Options » Advanced and uncheck “Automatically Flash Fill”.

To give flash fill a nudge and trigger it into action press CTRL+E or to ignore it press ESC.