Have you ever felt like it was going to take you forever to clean up your data so you could analyze it or extract what you need? Fortunately, the Flash Fill feature makes these tasks easier with brilliant automatic filling.
Most Excel users need to manipulate a column of data—whether it’s simply changing the text case, trimming data out of a cell, rearranging data within a cell, or adding content to a cell. With Flash Fill you can do all of these things in mere seconds. Not only is Excel’s Flash Fill a big time-saver that fills in data for you when a pattern is evident, it can also extract and insert as well as format and concatenate. This feature can even reverse text, numbers, dates and much more.
Let’s take a look at some examples of this new, powerful tool:
Change Formatting with Ease
Column A contains names in UPPERCASE. In Column B we will convert the text to Title Case. Type the name with the format you want and press Enter. Now Begin typing the second name and Excel will follow your example and fill the column for you!
Rearrange Text and Add Characters
What if we need to rearrange the text and put the Last Name before First Name separated by a comma and space? Again show Excel what you want by example and Flash Fill with do the rest!
We want to create a column of initials in column B. Notice Flash Fill thought we wanted the 1st and the 5th character in this example because Charles contains an “L”.
Show the Pattern and Choose Flash Fill
So we need to show the pattern by typing the first 2 examples “CL” and “KW” and in the next cell below choose Flash Fill on the Data Ribbon (keyboard shortcut [Ctrl] [E])
All of this is pretty amazing but Flash Fill can do more. We’ll generate a list of the company’s email addresses using the name column and trimming and adding the necessary text.
Split Inconsistent Text and/or Numbers
Here we can see a list of model numbers where we can easily separate the letters and numbers into separate columns.
Additional Flash Fill Examples
Using the Flash Fill feature in Excel can save you time and unwanted stress.