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.
![Flash fill in excel: Everything You Need To Know About Flash Fill In Microsoft Excel [5 Examples] 1 What is Flash fill in excel?](https://datawitzz.com/wp-content/uploads/2021/10/screenrecording-chunk-3-1-1024x549.gif)
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
![Flash fill in excel: Everything You Need To Know About Flash Fill In Microsoft Excel [5 Examples] 2 How to Enable Flash Fill in Excel](https://datawitzz.com/wp-content/uploads/2021/10/Flashfill_2-1024x701.jpg)
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.
![Flash fill in excel: Everything You Need To Know About Flash Fill In Microsoft Excel [5 Examples] 3 flash fill option in excel](https://datawitzz.com/wp-content/uploads/2021/10/flash-fill3-1024x150.jpg)
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.
![Flash fill in excel: Everything You Need To Know About Flash Fill In Microsoft Excel [5 Examples] 4 Extract the First or Last Name from a Full Name](https://datawitzz.com/wp-content/uploads/2021/10/flashfill_5.jpg)
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.
![Flash fill in excel: Everything You Need To Know About Flash Fill In Microsoft Excel [5 Examples] 5 Concatenate First and Last Names to Create a Full Name](https://datawitzz.com/wp-content/uploads/2021/10/Flashfill_6.jpg)
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.
![Flash fill in excel: Everything You Need To Know About Flash Fill In Microsoft Excel [5 Examples] 6 Convert Names to Initials](https://datawitzz.com/wp-content/uploads/2021/10/FLASHFILL7.jpg)
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 .
![Flash fill in excel: Everything You Need To Know About Flash Fill In Microsoft Excel [5 Examples] 7 creating email id with flash fill in excel](https://datawitzz.com/wp-content/uploads/2021/10/fLASHFILL_8.jpg)
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.
![Flash fill in excel: Everything You Need To Know About Flash Fill In Microsoft Excel [5 Examples] 8 separating number and text by using flash fill](https://datawitzz.com/wp-content/uploads/2021/10/flashfill_9.jpg)
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