Some of the most time consuming and irritating aspects of Excel are working with repetitive information. This included writing formulas, formatting, separating dates, and entering names and email addresses manually. Fortunately, the Flash Fill feature introduced with Microsoft Excel’s 2013 edition makes these tasks easier with brilliant automatic filling.
Excel’s Flash Fill is a time-saving feature which fills in data for you when a pattern is evident. It can also can extract and insert as well as format and concatenate. This feature can even reverse text, numbers, dates and much more.
Let us walk through how to use Flash Fill with some examples.
1. Separating First Name and Last Name Using Flash Fill
We have a list of full names in one column, and we will separate the data in this column into two individual columns using Flash Fill.
2. Concatenating Last Name and First Name with Comma Separation
We have a lengthy list of full names in one column, and we wish to have the pattern: last name, comma, first name.
3. Extracting Initials from Full Names
We have the same list of full names. We will extract initials from the full names using Flash Fill.
4. Formatting Phone Numbers Using Flash Fill
Using Flash fill to format phone numbers, you can concatenate with brackets to make it easily readable.
5. Extracting Text before the Symbol @ from Email Addresses
With the Flash fill feature, extracting text before the @ symbol from email addresses is simple.
Note: Flash Fill cannot recognize source data modification.
If you apply Flash Fill on source data and then try to modify the source data, Flash Fill cannot recognize the changes and make modifications in available results as Excel formulas do. Therefore, it is best to use Flash Fill with data which is not going to be modified in the future.