Pinterest Pixel

Fix Incorrect Formatting Using Flash Fill In Excel

  Flash Fill in Excel is a new feature that was introduced in Excel 2013.  One of the... read more

Download Excel Workbook
Bryan
Posted on

Overview

Fix Incorrect Formatting Using Flash Fill In Excel | MyExcelOnline Fix Incorrect Formatting Using Flash Fill In Excel | MyExcelOnline

 

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:

 

Fix Incorrect Formatting Using Flash Fill In Excel | MyExcelOnline

download excel workbook Flash-Fill-Fix-Incorrect-Formatting.xlsx

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:

Fix Incorrect Formatting Using Flash Fill In Excel

 

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

Fix Incorrect Formatting Using Flash Fill In Excel

 

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.

Fix Incorrect Formatting Using Flash Fill In Excel

 

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).

Fix Incorrect Formatting Using Flash Fill In Excel

How to Use Flash Fill In Excel

 

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 🙂

Fix Incorrect Formatting Using Flash Fill In Excel

 

 

Add Comma in Excel between Names with SUBSTITUTE Formula

 

If you like this Excel tip, please share it
Fix Incorrect Formatting Using Flash Fill In Excel | MyExcelOnline Fix Incorrect Formatting Using Flash Fill In Excel | MyExcelOnline
Fix Incorrect Formatting Using Flash Fill In Excel | MyExcelOnline
Bryan

Bryan Hong is an IT Software Developer for more than 10 years and has the following certifications: Microsoft Certified Professional Developer (MCPD): Web Developer, Microsoft Certified Technology Specialist (MCTS): Windows Applications, Microsoft Certified Systems Engineer (MCSE) and Microsoft Certified Systems Administrator (MCSA).

He is also an Amazon #1 bestselling author of 4 Microsoft Excel books and a teacher of Microsoft Excel & Office at the MyExecelOnline Academy Online Course.

See also  How to Create a Histogram in Excel: A Step-by-Step Guide with Examples

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...