Flash Fill in Excel is a new feature that was introduced in Excel 2013.

Flash Fill allows you to combine, extract, move & transform data that belongs in one column, into a new column.

One of the cool uses of Flash Fill is formatting numbers.  In our example below, we are going to add hyphens within our serial numbers!



To demonstrate the power of Excel’s Flash Fill, we will start off with this table of data we need to apply our formatting on:

Flash Fill Formatting 01


STEP 1: Type 123-45-6789 as the first entry in the Formatted SSN column.  We also want the rest of the SSNs to be formatted this way.

Flash Fill Formatting 02


STEP 2: In the second entry, once you type the first number 4 of 478923744Excel will auto-suggest to Flash Fill the rest of the Formatted SSN column.

Excel is smart enough to infer that you are trying to apply this formatting.

If the Flash Fill looks good, press Enter. 

Flash Fill Formatting 03


*** IMPORTANT: If Flash Fill doesnt’ start automatically in your selected cell when you type in data that matches a pattern, you will need to start Flash Fill manually.

This is done by clicking on Data > Flash Fill or by pressing the Flash Fill keyboard shortcut CTRL+E

flash fill excel 2013


STEP 3: What is very impressive is Excel was able to apply the same formatting to the rest of the table without the use of a single formula!

Flash Fill Formatting 04






If you like this Excel tip, please share itEmail this to someone


Pin on Pinterest


Share on Facebook


Tweet about this on Twitter


Share on LinkedIn


Share on Google+


Related Posts

Custom Number Formats in Excel A custom number format in Excel can have up to four different sections in the following order: Positive format; Negative format; Zero format; Text format   You can specify different format codes for each section as long as they are separated by a semicolon. So you ca...
Turn Text Dates To Excel Dates With Text To Column... Whenever you import data from your company's server, ERP system or any other source for that matter, the Dates usually come in a TEXT format. I will show you a cool trick where you can turn the TEXT Date to a values Date that Excel can read and work with. DOWNLOAD EXCEL WOR...
Excel Table Headers Show in Columns Before Excel Tables were introduced in Excel 2007, there were Excel Lists which had limited functionality. For example, when you had a list of data with hundreds of rows and you had to scroll all the way down to the bottom of your list, the Headers row was not visible and you ...
Add Custom Symbols With Your Numbers in Excel A cool feature within Excel is the ability to format a cell's value by pressing CTRL + 1 on any cell. This brings up the Format Cells dialogue box and under the Custom category you can customize the Type to whatever you like and even insert symbols! There are four formattin...