Hi,
Flash fill option has been introduced into the Ms-office 2013 version and would be available after 2013 version. It is very powerful and useful option developed by Microsoft community.
What flash fill can do?
Flash fill will extract the information from any congested data in a simple way.in the further example, you will see and understand the use of it.
Excited! Here we go
Instead of only reading the article, you can download the Dummy file and together we can do the practice.
Example 1- Get the First and Last Name –
Suppose we have a database as given below-
We need to extract Student’s first name into column B and the last Name into column C. In the previous version we were using option “Text to columns” however it takes a lot of time and sometimes it gives an incorrect answer. Let’s see How flash fill will help us?
Step1: Put your cursor on the top of column B (CellB2)
Step2: Type first Name Manually Ex. Manshi-> Enter
Step3: Data Tab-> Flash Fil or Ctrl+E
When we click on option Flash fill, Student’s First name will fill automatically.
Follow the same steps as explained above for the last Name.
Flash AutoFill-
This is another magical feature of Flash Fill When we type the first name in our column and after that when you type second name’s First alphabet, excel shows rest name’s list. Ex. we can see into the image, at this moment you must press only “Enter”.
If Auto fill option is not working.
Go to File->Option-> Advance -> check mark on Automatically Flash Fill option.
Flash fill understand your inputs fillings-
If we put the first alphabet it will give you first Letter of the all the Name, If we type all the Letters in capitals then it will give all the name in Capital Letters Ex. (MANSHI) vice versa.
Example: -Concatenate Email Id with names and company-
In this sheet, we have names in different columns, company name in another column and we need to create an email id through concatenating name along with the company (See in the example). In the previous version we were using function “Concatenate” however we must provide every cell address and other information.
To get an instant result to follow the steps-
Step 1: Type complete name in cell E2 ( manshi.sharma@lenskart.com)
Step 2: Go to Data tab-> Flash fill or press Ctrl+E
Example 2- Get alphabet and Number
As we can see in above example, we need to extract 4-digit data from the starting in column B and 3 digits data from the last in Column C.
Just follow the steps-
1- Type first four-digit into the cell B2 ex. A001
2- Data ->Flash Fill or you can use its shortcut key Ctrl+E
Similarly, follow the above steps for the column C.
See the steps below-
Example 3- Get the state and pin code from the Address-:
If we have a customer Address database and we only want to know the state name from the customer is belonging and maintain it into the different columns.
How will we do that? in the
previous version, we were using Option “Text to columns” however it takes a lot of time and sometimes it has given the inaccurate result.
However, Flash fill can give you a better result in a short time period.
Step 1: Type the state name into Cell B2 Ex. (Allahabad) then press Enter
Step 2: Go to Data Tab-> Flash Fill
If we want to extract some other information Ex. Pincode
Follow the same steps as explained above, you will get the desired result.
Example- Get Alphabet and Number from the Miscellaneous data-
As we can see above image, we need to copy Text in text column and Number in Number column. If we follow the same steps as we are applying in the previous example like Type the first name then enter then Go to Data tab-> click on Flash fill -See what happened-
Every functions/ option have merit and demerits.
Flash fill could not understand the sequence, as we can see the text is coming into the unsystematic manner.
What to do next?
Need not to worry about it.
Step 1: Just type the first Name into first cell (B2), leave the second cell and type another name into the third cell (B4).
Step 2:- Go to Data tab-> Flash Fill or Press Ctrl+E
To get Number to follow the same steps as explained above.
Note: Some time Flash fill will not understand the pattern and gives you a weird data, In such case, you can fill a couple of more cells so that Flash Fill can recognize a pattern, or dump the idea of using Flash Fill and use Excel functions instead.
Limitations of Flash Fill in Excel-
Flash Fill is an amazing tool; however, it has some limitations before we use it on the large and important database we must to know –
The result of flash fill is fixed. This means that if you change the original data set. The flash fill result would not change.
Flash Fill ignores cell with a space character in it. If you’re using Flash Fill, make sure the cells where the result would come don’t have any space character in it. If it does, Excel Flash Fill would simply ignore it.
In some cases, Flash Fill may falter in identifying the pattern giving incorrect results. Excel shows an error.
Hope, this article will help you a lot. If you have any query feel free to comment below or reach www.nurturetechacademy.in
Happy Learning 😊