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

Excel Paste Special: A Values Multiplier Paste Special has a few different Paste features and operations that many users do not know about. One of them is the Paste Special > Values > Multiply combination. What that does is multiply a range of selected cells by a value, called the multiplier.  So if you want...
Lock & Protect Formula Cells If you have a workbook with lots of formulas and you want to protect those formulas from being amended by other people who share your workbook, then you can! You need to follow these steps: 1. Press the Go To Special shortcut CTRL+G 2. Select the Constants box and press ...
Replace Excel Formatting with Another Formatting Imagine this, you have a table full of bold text.  The bold text could also be all over your worksheet in random cells. Then you decide that the bold text does not suit your expected design and prefer red colored text instead. What would you do? Changing all of the forma...
Show The Difference From Previous Years With Excel... Excel Pivot Tables have heaps of calculations under the SHOW VALUES AS option and one that gets the most use is the DIFFERENCE FROM calculation. You can show the values as the Difference From previous months, years, day etc.  This is just great when your boss asks you how you ...