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:

 

DOWNLOAD EXCEL WORKBOOK

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

 

HELPFUL RESOURCE:

728x90

 

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

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

Find Errors with Go to Special Constants Say you have a data set and want to make sure that each column contains what it is supposed to.For example, say you have a column which contains Dates and you want to check that there are no cells which contain Text.You can easily check this by highlighting that column an...
Excel´s Fill Justify Tool An interesting tool within Excel is the Fill Justify.  It allows you to select text from several rows and merge them in to one cell.So if you have data that gets downloaded in to separate rows and want to join them up in to one sentence, then the Excel´s Fill Justify option i...
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 ...
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...