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.

Flash Fill Extract Name

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!

Flash Fill Combine Cell Data

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.

Flash Fill Format Numbers

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.

Advertisements

2 thoughts on “Why Write Excel Formulas When You Can Flash Fill?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s