Flash Fill in Excel (Table of Contents)
Flash Fill in Excel
Flash Fill in Excel is used to automatically fil the data with the help of available cells pattern in such a way that if we combine 2 cells value in 3rd cell, then we would be automatically combined rest of the cells value using Flash Fill which is available in Data menu tab under Data Tools. For example, we have 10 cells in a column containing the text lines along with numbers. Now, if we want to extract numbers from each cell and fill them in respective cells, then write a number from a cell in the parallel cell then click on Flash Fill or press Ctrl + E, we will see it will fetch the number from those cells and fill in respective side cell.
Shortcut Key for Flash Fill in Excel
In Excel, we are aware of shortcut keys where we can use it for various function same like other functions, Excel Flash Fill has a keyboard shortcut. The shortcut key for Flash Fill in excel is “CTRL+E”. For example, if we have two separate names in two different columns. First type both the name in the single column and by holding on CTRL key Press E i.e. CTRL+E so the beauty is excel will automatically fill all the names by the use of Flash Fill.
Let’s see how to add Flash fill in excel by following the below steps.
- First Go to File menu.
- Click on Options.
- Once we click on the option we will get the dialogue box as shown below.
- In the excel option dialogue box, we can see the option as Advanced.
- Click on the advanced option so that we will get advanced option dialogue box as shown below. Once we click on the advanced option go to Editing option where we can see “Automatically Flash Fill” option checkbox. Checkmark that, so the Flash Fill option will be enabled.
- Now we can check in the Home menu, Flash Fill is enabled which is shown in the below screenshot.
Examples of Flash Fill in Excel
Example #1: Applying Flash Fill in Excel
In this example, we will see how we will apply Flash fill in excel by following the below steps.
Consider the below example which has First name and Last name in two separate columns. Assume if we have a huge list of names that we need to combine in one single column. If we go on for typing it will consume more time and there is a chance of getting name mismatched. In these scenarios, we can use Flash Fill option to fill the names automatically.
- First click on the C2 cell column named called Flash Fill
- Type the First name and Last name in C2 as shown below
- Now go to the Home menu and at the top right corner after Auto Sum, we can the see the Fill option as shown below
- Click on the Fill option so that we will get the list of Fill List as shown below. Choose the last option which is Flash Fill.
- Once we click on Flash Fill we can see the beauty that all the names will be filled automatically by using them and we will get the result which is shown below.
Example # 2: Combining Multiple Names Using Flash Fill
In this example, we are going to see how to use flash fill with multiple names and email id. Consider the below example which has First name, Last name, Email id.
Assume that we have the same list with other names with their email id and we need to combine the first name along with email-id. We can type and complete the process but it will take a very long time if we have a list of 100 names at a time. In these scenarios, Flash fill tool will be very useful where it actually saves more time by filling the data automatically. Let us see the procedure of doing it by following the below steps.
- First Create a new column & Type the first name with the last name, email id as Jameswinfred@hotmail.com which is shown below.
- Now apply the Flash Fill shortcut key by holding the CTRL KEY and press E .So that all the names will get filled automatically which saves a lot of time in typing, Once the flash fill has applied the result will be displayed which is shown in the below screenshot.
Example #3: Extracting Data Using Flash Fill
In this example, we are going to see how to extract the specific data using Flash Fill. Flash fill tool can combine multiple names and it will extract the data what we are exactly looking for. Let us see how to extract the specific data in this example by following the below steps. Consider the below example which has First name, Last name, and Social Security Number of Individual employees.
Now in this example, we need to extract the first three digits and the last two digits of the social security number.
- Create a new column named Flash Fill.
- Now simply type the number within double quotes in cell D2 as – 114/81
- Apply the flash fill shortcut key or we can use the Flash fill option in the menu. But here we will use the short cut key for applying Flash Fill. Press CTRL key plus E. Now we can see that all the data will be filled automatically which is shown below
Example #4: Extracting data from sentence using Flash Fill
In this example, we will see how to extract the specific data from a sentence using Flash Fill. Consider the below example which shows First name, Last name, and Location combined in one cell.
Assume that we need to extract only the location. In this scenario, we can use the Flash fill to extract only the location from the data. Let’s see how we can extract using Flash Fill by following the below steps.
- First Create a new column called Flash Fill and input the location as Germany as shown below.
- Now apply the flash fill by using the short cut key “CTRL +E, Once we hit CTRL+E shortcut key, Flash fill senses the data what we have given, Flash fill will automatically fill the data without typing and the output is shown below.
Things to Remember about Flash Fill in Excel
- Flash fill is a new feature which is available only in Excel 2013 and the latest version.
- Flash fill is one of the best tools in excel which fills the data automatically and it is also called a time saver tool.
- Flash fill will sense the data whatever we give and fills the data automatically.
Recommended Articles
This has been a guide to Flash Fill in Excel. Here we discuss Flash Fill in Excel and how to use the Flash Fill in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles-
