Flash Fill in Excel is a new feature that was introduced in Excel 2013.  One of the cool uses of Flash Fill is to fix incorrect formatting in your text automatically.

Ever had the scenario where your data is formatted differently?

Example: First names starting with lower case, last names all in upper case, middle initials in either cases…arghhhhhh!

Luckily we have Flash Fill which can automatically convert the entire data set into one consistent format.  See how below:



To demonstrate the power of Excel’s Flash Fill, we will start off with this table of data where we need to fix the inconsistent formatting:

Flash Fill - Fix Incorrect Formatting 01


STEP 1: Type Homer A Simpson as the first entry in the Full Name column.  

Flash Fill - Fix Incorrect Formatting 02


STEP 2: We want the rest of the Text to be formatted this way, so in the second entry, type Ian B Wright.

Notice that Excel did not auto-suggest to Flash Fill.  There are times that this happens.

Flash Fill - Fix Incorrect Formatting 03


Since Flash Fill did not start automatically when you are expecting for it to match your pattern, you can start it manually by highlighting the entire column you want it to fill.

Then click Data > Flash Fill (Another alternative is to press the Ctrl+E keyboard shortcut).

Flash Fill - Fix Incorrect Formatting 04

Flash Fill - troubleshoot


STEP 3: You now have your data auto populated using Flash Fill.  

What is very impressive is Excel was able to apply the same format pattern to the rest of the table without the use of a single formula! 

Adios…I mean, goodbye inconsistent formatting 🙂

Flash Fill - Fix Incorrect Formatting 05

Flash Fill - Fix Incorrect Formatting





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

How To Make Negative Red Numbers In Excel When you are working with lots of different numbers in Excel, you sometimes want your numbers to stand out by showing them in a negative red number with a minus sign in the front.To do this you need to select your numbers and press CTRL+1 to bring up the Format dialogue box. ...
Extract Numbers Using Flash Fill In Excel  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 extracting the numbers from your text au...
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....
Create a Named Range in Excel Whenever I work with spreadsheets, there's no escaping the fact that I have to work with a lot of ranges.Some of these ranges I have to reuse time and time again to create different calculations!Good thing there are Named Ranges in Excel!By using a Named Range, you ca...