Flash fill in excel
What is Flash fill in excel?
Flash Fill is a data tool in Microsoft Excel that allows us to combine, extract or transform data based on a few examples. We only need to provide a couple of examples of the results we want, excel will guess the pattern and fill in the rest of the data for us.
In the above gif file, we can see that we only provided one row of input, and after using Flash fill it automatically separates values.
How to Enable Flash Fill in Excel?
To enable flash fill in excel go to File then Options. After that select Advanced and check on Enable AutoComplete for cell values. This will enable flash fill
Where is flash fill in excel
We can check flash fill in excel by going to Data tab in ribbon and then can see Flash Fill in Data Tool column.
We can also check flash fill alternatively by going Home tab then Fill. In the drop down we can see Flash Fill option.
There is also a keyboard shortcut available for Flash Fill. The process for using the keyboard shortcut is similar to using the command in the ribbon.
- Type out the example data you want returned.
- Select both the cells you want to fill and the cells with the examples.
- Press Ctrl + E on your keyboard.
5 Examples of How to do flash fill in Excel
Example-1: Extract the First or Last Name from a Full Name
If we have a list of full names then we can use Flash Fill to extract part of the name. In this example, we used Flash Fill to get the First name from a list of names that are all in the First Last format.
Example-2: Concatenate First and Last Names to Create a Full Name
If we are starting out with a list of first and last names, then we can use Flash Fill to combine the data into a single cell to make it a full name.
This example uses Flash Fill to combine the first and last names into a First Last format.
Example-3: Convert Names to Initials
If we want to use initials of names either it is in separate columns or in a column. In the below example we used First & Last names in two columns.
Example-4: Create email ids from first name and last name
We can create email ids in bulk by using first and last name. Flash fill helps in creating such email ids we need to just use first and last name along with email domain name. After that just use CTRL + E .
Example-5: Separating Number and Text from an alphanumeric
We usually encounter problems wherein we need to separate text and numbers from an alphanumeric value. Tough doing it by formula or manually is a tedious task. Though using flash fill makes this task very easy. See the example illustrated in the below example.
In the above example, we got data as alphanumeric and we need to separate numbers and text. we will first write the number in the first cell even there is an alphabet in between. We will ignore the alphabet and write all numbers at a place then will use flash fill. It will separate all numeric in the column. We will treat text value in the same way as we did for numbers.
There are multiple uses of flash fill in regular excel uses that save our time and make handling easier. I will encourage you to explore more on this.
That is all for now for Flash fill in excel. Will meet again with some new topic.
You can watch the video also on this topic for more clarity.
You can read more about flash fill from the below articles.
https://www.excel-easy.com/examples/flash-fill.html
ahttps://www.journalofaccountancy.com/newsletters/extra-credit/microsoft-excel-flash-fill.html