In this article you learn very interesting feature of Excel, that is Flash fill. Flash fill is a time saving feature which fills in data on the base of a pattern. It can also can extract and insert as well as format and concatenate.
Note: This feature is available only in Excel 2013 and above.
Flash fill option is available in Data tab. It also can be used by pressing Ctrl+E shortcut key.
Flash fill can be used for Dates, Text or numbers.
Date Example:
Lets say we have few dates in column A and we need to get Day on column B and Month on Column C. Although we can get it done by using the formula but we will achieve it by using Flash Fill.
To get the day from the date type 1 (Day of first date) on B2
Click on cell B3 and click on Flash Fill or press Ctrl+E.
To fill the month on column C input the first entry manually.
Click on cell c3 and press Ctrl+E. Data will be filled automatically.
We also can get day and month in a single column and format it as per our requirement. Enter the first one or two entry manually to get a pattern.
Press Ctrl+E to use flash fill.
Text Example:
Let’s take an example of text to use the Flash Fill. We have the below data of IPL team and we have to achieve the yellow columns data.
To get the first name of the team type Mumbai (first name of Mumbai Indians) in cell “I2“. Now if we will press Ctrl+E then it will fill Kolkata only in cell “I3” but we want to fill here Kolkata Knight here. We will fill it manually on cell “I3” so that we can give a pattern that whenever a team has 3 words in its name then first 2 words will be filled in First name.
- Select the Range “I4”
- Press Ctrl+E to flash fill.
To get the Last Name type one manual entry
Press Ctrl+E to flash fill.
- Now we have to get the short name of the team like- for Mumbai Indians = MI
- Input the one manual entry to get the patterns
- Press Ctrl+E to flash fill.
Now useful part of Flash fill is that we can create the comments or summary from multiple columns. Type one manual entry to get the pattern.
Press Ctrl+E to flash fill.
Number Example:
Let’s take an example of numbers to use the Flash Fill. Let’s we have mobile numbers in column A we have to change the format or get the first 4 or last 4 digit.
Input first manual entry and give the format whatever you want to give.
Press Ctrl+E to fill the same format.
To get the first four digit type one manual entry.
Press Ctrl+E to fill the same format.
To get the last four digit type one manual entry. It is not necessary to give one entry on row number 2, we can give it on another row also.
Press Ctrl+E to Flash fill.
