Since I heard about Flash Fill feature in MS Excel 2013 much earlier, immediately after opens the Excel preview I first tested Flash Fill feature. After experienced the Flash Fill option performance in Excel 2013, I can simply say that the trailer is bumper hit !!! One scene of Flash Fill Option in the trailer is sufficient to make the movie super hit.
You may not believe that I worked almost 10 hours on Flash Fill option and worked out almost 100 examples. I am going to share 20 examples through Flash Fill articles. In this article I cover almost 10 important examples.
Without wasting any further time, I am jumping into the topic Flash Fill feature in Excel 2013.
What is Flash Fill Feature?
Flash Fill is one of the option available in Excel 2013 to fill the data automatically in values, based on the example you want as output.
Flash Fill saves time in writing formulas to extract part of data from a single column or combine data from two/more columns. Flash Fill identifies the first result which you provide and based on the that result it fills the rest of Cells.
We can find Flash Fill option
- Data - Data Tools group - Flash Fill
- Home - Editing group - Fill drop down - Flash Fill
Short cut to apply Flash Fill
Ctrl + E
Exercise # 1 - Extract First Name without Formula
I will start with the simple example
Let say we have the below data from Cell A1 through Cell A11
If we want to extract the first name of the Excel MVP from Column A, then we should follow the below steps in Excel version 2010 or below.
1. Enter the below formula in Cell B2
=LEFT(A2,SEARCH(" ",A2))
2. Then drag the formula till Cell B11
3. Finally, we get the result of First Name in Column B in Cell B2:B11
To get the final outcome, we should know two Text Functions names Left and Search. Then only it is possible to extract the first name.
However, there is a alternative way to split the first name and last name by using text to columns. But to use Text to column option, we should understand the inside options of Text to column.
Now, see how Flash Fill do your work in seconds of time.
Simply give first result in Cell B2 (that is Mike)
Just enter the second result first character in Cell B3, Flash Fill option displays all the results in gray color.
Once we click Enter, Flash Fill just fills the First Name in all the Cells.
Wow, wonderful !!!
This is just sample, we are going to see most wonderful examples below.
Note: Instead enter the first character of result in Cell B3, we can directly click Flash Fill option from the menu bar (menu ribbon) or use Shortcut Ctrl+E, Excel fills the results.
Exercise # 2 - Extract Last Name
Let say we have the same data as above in Column A.
And our target is to extract the Last Name of the data
Then in other Excel versions below 2013, you can use the below formula to extract the Last Name.
=RIGHT(A2,LEN(A2)-SEARCH(" ",A2))
So, here we should have the knowledge on Text Functions names Right, Len and Search
But, in Flash Fill options.. simply fills the result.. hope no is explanation required here.
Just enter the first result in Cell B2 (Alexander)
Use Shortcut Ctrl+E to apply Flash Fill option to fill the result in the remaining Cells.
Or Type the first character of the second result in Cell B3, then Excel Flash Fill option displays all the results in gray color. Just click Enter.
Exercise # 3 - Extract First character of First Name and Last Name
Let say we have the same data as above in Column A
Our target is to extract the First character of First Name and Last Name (M Alexander)
Use the below formula in Excel version 2010 and below in Column B.
=LEFT(A2,1)&RIGHT(A2,LEN(A2)-SEARCH(" ",A2)+1)
Oh my god! we have to know four Text Formulas to get the result (Left, Right, Search and Concatenate/&)
See how our wonderful Flash Fill feature fills below
Flash Fill feature in Excel-2013 is really so clever. Based on the first result which we have entered in Cell B2, it analyzes and gives you the remaining results in fraction of seconds.
Steps to get the results :-
Just enter the first result in Cell B2 (M Alexander)
Use Shortcut Ctrl+E to apply Flash Fill option to fill the result in the remaining Cells.
Or Type the first character of the second result in Cell B3, then Excel Flash Fill option shows rest of results for us. Simply click Enter.
Exercise # 4 - Result in the format Last Name, First Name
Let say we have the same data as above in Column A
Now our target is bit difficult. We want the result should be in Last Name, First Name format.
Based on the difficulty, we should use some complex formulas.
Use the below formula in Excel version 2010 and below, in Column B to get the result.
=RIGHT(A2,LEN(A2)-SEARCH(" ",A2))&", "&LEFT(A2,SEARCH(" ",A2))
This formula is really crazy. How normal data entry operators has this much formula knowledge to apply. Here, we have to know four text formulas to get the result (Left, Right, Search and Concatenate/&)
In this way, Flash Fill saves thousands of hours of time for the guys who works with data entry/analysis/research.
Ok coming back to the topic, Lets see how Flash Fill gives us the result.
I hope, expectations has been changed, after seeing this result.
If we notice, Flash Fill not only extract a part from the data but also intelligently adds to the result, what we have added in the first result.
Since we added Comma (,) in between the first name and the last name, Excel Flash Fill option also added Comma (,) in the result and displayed before us.
Steps to get the result
Just enter the first result in Cell B2 (Alexander, Mike)
Use Shortcut Ctrl+E to apply Flash Fill option to fill the result in the remaining Cells.
Or Type the first character of the second result in Cell B3, then Excel Flash Fill option shows rest of results for us. Simply click Enter.
We will see, whether excel only adds special symbols like Comma (,) or adds any other value/text.
Simply Superb !!
I adds, MVP in between last name and first name and excel did the same thing.
I can simply say, Flash Fill is a logical Mirror of first result.
You have to tell how the Flash Fill option is?.. by giving comment below the post.
Don't thing that this is end. I am going to explain you further more examples. Ready to see brilliant examples than this.
Exercise # 5 - First letter of First Name and First letter of Last Name
Let say we have the same data as above in Column A
Now, we will test the Excel logically. We will ask Excel to fill First letter of First Name and First Letter of Last Name
From this example I not going to explain the formulas in Excel version 2010 and below, the reason that we may miss the focus in Flash Fill option. But note that the formulas should be so complex to solve from this Exercise.
See, how Flash Fill do our duty with our using any formula.
Excel did it !!!
No words to explain you more, except going to the another example.
As formality, find the steps below
Just enter the first result in Cell B2 (MA)
Use Shortcut Ctrl+E to apply Flash Fill option to fill the result in the remaining Cells.
Or Type the first character of the second result in Cell B3, then Excel Flash Fill option shows rest of results for you. Simply click Enter.
Exercise # 6 - First letter of Last Name / First letter of Fast Name
Now, we will ask Excel in a reverse way means First Letter of Last Name and First Letter of First name.. not Ok, we will ask to add Slash (/) Symbol in between.
The first result should be A/M
What not !
What not !!
What not !!!
What not !!!!
What not !!!!!!
Exercise # 7 - Extracting First 3 Characters of the First Name
If we want to extract the first 3 Characters of First Name.
See below, how Flash Fill performs these tasks.
Finally,
follow the below steps to apply Flash Fill feature in Excel - 2013.
Just enter the first result in Cell B2 (Alexander, Mike)
Use Shortcut Ctrl+E to apply Flash Fill option to fill the result in the remaining Cells.
Or Type the first character of the second result in Cell B3, then Excel Flash Fill option shows rest of results for you. Simply click Enter.
This is not the end. We will see some amazing examples in the Part - II of the Flash Fill article.
Next article on Flash Fill covers, Extracting data from dates, complex numbers, formatted numbers/text, combining of two or more data fields etc.,
How is Excel Flash Fill Option?
Do you like Flash Fill feature in MS Excel 2013, then share your experience by giving comment below.
1. Enter the below formula in Cell B2
=LEFT(A2,SEARCH(" ",A2))
2. Then drag the formula till Cell B11
3. Finally, we get the result of First Name in Column B in Cell B2:B11
To get the final outcome, we should know two Text Functions names Left and Search. Then only it is possible to extract the first name.
However, there is a alternative way to split the first name and last name by using text to columns. But to use Text to column option, we should understand the inside options of Text to column.
Now, see how Flash Fill do your work in seconds of time.
Just enter the second result first character in Cell B3, Flash Fill option displays all the results in gray color.
Once we click Enter, Flash Fill just fills the First Name in all the Cells.
Wow, wonderful !!!
This is just sample, we are going to see most wonderful examples below.
Note: Instead enter the first character of result in Cell B3, we can directly click Flash Fill option from the menu bar (menu ribbon) or use Shortcut Ctrl+E, Excel fills the results.
Exercise # 2 - Extract Last Name
Let say we have the same data as above in Column A.
And our target is to extract the Last Name of the data
Then in other Excel versions below 2013, you can use the below formula to extract the Last Name.
=RIGHT(A2,LEN(A2)-SEARCH(" ",A2))
So, here we should have the knowledge on Text Functions names Right, Len and Search
But, in Flash Fill options.. simply fills the result.. hope no is explanation required here.
Use Shortcut Ctrl+E to apply Flash Fill option to fill the result in the remaining Cells.
Or Type the first character of the second result in Cell B3, then Excel Flash Fill option displays all the results in gray color. Just click Enter.
Exercise # 3 - Extract First character of First Name and Last Name
Let say we have the same data as above in Column A
Our target is to extract the First character of First Name and Last Name (M Alexander)
Use the below formula in Excel version 2010 and below in Column B.
=LEFT(A2,1)&RIGHT(A2,LEN(A2)-SEARCH(" ",A2)+1)
Oh my god! we have to know four Text Formulas to get the result (Left, Right, Search and Concatenate/&)
See how our wonderful Flash Fill feature fills below
Steps to get the results :-
Just enter the first result in Cell B2 (M Alexander)
Use Shortcut Ctrl+E to apply Flash Fill option to fill the result in the remaining Cells.
Or Type the first character of the second result in Cell B3, then Excel Flash Fill option shows rest of results for us. Simply click Enter.
Exercise # 4 - Result in the format Last Name, First Name
Let say we have the same data as above in Column A
Now our target is bit difficult. We want the result should be in Last Name, First Name format.
Based on the difficulty, we should use some complex formulas.
Use the below formula in Excel version 2010 and below, in Column B to get the result.
=RIGHT(A2,LEN(A2)-SEARCH(" ",A2))&", "&LEFT(A2,SEARCH(" ",A2))
This formula is really crazy. How normal data entry operators has this much formula knowledge to apply. Here, we have to know four text formulas to get the result (Left, Right, Search and Concatenate/&)
In this way, Flash Fill saves thousands of hours of time for the guys who works with data entry/analysis/research.
Ok coming back to the topic, Lets see how Flash Fill gives us the result.
If we notice, Flash Fill not only extract a part from the data but also intelligently adds to the result, what we have added in the first result.
Since we added Comma (,) in between the first name and the last name, Excel Flash Fill option also added Comma (,) in the result and displayed before us.
Steps to get the result
Just enter the first result in Cell B2 (Alexander, Mike)
Use Shortcut Ctrl+E to apply Flash Fill option to fill the result in the remaining Cells.
Or Type the first character of the second result in Cell B3, then Excel Flash Fill option shows rest of results for us. Simply click Enter.
We will see, whether excel only adds special symbols like Comma (,) or adds any other value/text.
I adds, MVP in between last name and first name and excel did the same thing.
I can simply say, Flash Fill is a logical Mirror of first result.
You have to tell how the Flash Fill option is?.. by giving comment below the post.
Don't thing that this is end. I am going to explain you further more examples. Ready to see brilliant examples than this.
Exercise # 5 - First letter of First Name and First letter of Last Name
Let say we have the same data as above in Column A
Now, we will test the Excel logically. We will ask Excel to fill First letter of First Name and First Letter of Last Name
From this example I not going to explain the formulas in Excel version 2010 and below, the reason that we may miss the focus in Flash Fill option. But note that the formulas should be so complex to solve from this Exercise.
See, how Flash Fill do our duty with our using any formula.
No words to explain you more, except going to the another example.
As formality, find the steps below
Just enter the first result in Cell B2 (MA)
Use Shortcut Ctrl+E to apply Flash Fill option to fill the result in the remaining Cells.
Or Type the first character of the second result in Cell B3, then Excel Flash Fill option shows rest of results for you. Simply click Enter.
Exercise # 6 - First letter of Last Name / First letter of Fast Name
Now, we will ask Excel in a reverse way means First Letter of Last Name and First Letter of First name.. not Ok, we will ask to add Slash (/) Symbol in between.
The first result should be A/M
What not !!
What not !!!
What not !!!!
What not !!!!!!
Exercise # 7 - Extracting First 3 Characters of the First Name
If we want to extract the first 3 Characters of First Name.
See below, how Flash Fill performs these tasks.
follow the below steps to apply Flash Fill feature in Excel - 2013.
Just enter the first result in Cell B2 (Alexander, Mike)
Use Shortcut Ctrl+E to apply Flash Fill option to fill the result in the remaining Cells.
Or Type the first character of the second result in Cell B3, then Excel Flash Fill option shows rest of results for you. Simply click Enter.
This is not the end. We will see some amazing examples in the Part - II of the Flash Fill article.
Next article on Flash Fill covers, Extracting data from dates, complex numbers, formatted numbers/text, combining of two or more data fields etc.,
How is Excel Flash Fill Option?
Do you like Flash Fill feature in MS Excel 2013, then share your experience by giving comment below.
Good one. What happens if the data in the first column changes? I suppose the column B won't be changed at all. This gives advantage to the formulas. Of course, the Flash Fill will be a often usefull as you do not need to change the data source.
ReplyDeleteYes Struzak.. Flash fill never replace the formula. However, it reduces the hours of works for the guys, who works with large data.
ReplyDeletePlease check the second part of examples of Flash Fill, which will be published shortly.
Excel Everest l like this blog along with both blogs are really helpfull for me to learn Excel.
ReplyDeleteEverest offers Microsoft Excel Tutorials: Our administrations are Microsoft Excel Training, Online Excel Training,Excel Courses.
ReplyDeleteIt is built right inside Excel so you'll learn by doing. Microsoft Excel Training