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

Top Excel Data Cleansing Techniques Data cleansing is an important activity within Excel and one that we find ourselves doing day in day out, sometimes without even knowing it.So what is data cleansing?In the words of Oz du Soleil, Excel MVP, it is like "Hercules being sent out to capture the three-he...
Show Pivot Table Values As a Running Total In If you want to track your sales on a monthly basis and see their Year to Date totals, then you can easily do this with the Running Totals In calculation.You can activate this by Right Clicking on your Pivot Table values and choosing Show Values As and Running Total In.  You c...
Turn Text To Values With Paste Special Values Many times you would have received data from your IT system which is formatted wrong!  Well a gazillion times if you work in a mid sized firm 🙂When you try and sum the values you get a count rather than a sum, that is because Excel reads the data as text rather than a value....
Custom Date Formats in Excel Custom date formats in Excel allow you to display only certain parts of the date.Say you had a date of 18/02/1979, which coincides to be my birthday 🙂You can use the Format Cells dialogue box to show only the number 18, the day that corresponds to that date (Sunday), the...