How To Use Flash Fill In Excel For Automatic Data Entry

Table of Contents[Show]
How To Use Flash Fill In Excel For Automatic Data Entry


Excel Flash Fill Feature - If you use Microsoft Excel 2013 or above you can fill cells or input data in Excel automatically based on certain patterns according to the sample data you provide. The way this is meant is to use the FLASH FILL function in Excel.

This Flash Fill function is almost similar and indeed complements the Auto Fill Data function in Excel.

The difference is that Auto Fill is more intended for data sequence formation patterns on one row or one column only, while Flash fill is more intended for the formation of new data based on certain patterns that can be more complex or more complicated.

This function is also similar to the Text to Columns feature which is used to extract or separate data. It's just that, in addition to separating data or splitting data into sections based on a certain pattern, Flash Fill can also be used to combine data or unify certain data in Microsoft Excel.

Wow ... But still confused me?

For example, you have the full name data in column A. Then you want to separate the first name and last name into 2 columns. Column B contains the first name and Column C contains the last name.

Or vice versa, you already have data for the first name and last name in different columns, then you want to combine them into the full name in the next column. Without using Excel formulas which are relatively complicated, you can easily and quickly do these things using the Flash Fill feature in Excel.

The new Flash Fill feature was introduced in the Ms. Excel 2013 so to be able to use this feature you must be using Office Excel 2013 or later. If you are still using Excel 2010 and below then you will not be able to enjoy this cool feature.

Okay, then how do you use the Flash Fill function in Excel? Let's continue on the tutorial discussion.

How to Use Flash Fill in Excel

By using Flash Fill, Excel will guess what data should be entered in other cells after studying and analyzing the data you write manually. Therefore Flash Fill in Excel will work best if your data has a clear pattern.

There are at least 4 ways to be able to access or use Excel's Flash Fill feature:

1) Tab Home--Group Editing--Fill--Flash Fill.

Use Flash Fill in Excel


2) Tab Data--Group Data Tools--Flash Fill.

Use Flash Fill in Excel

3) Using Fill Handle. The Fill Handle appears when you use Auto fill Data.

Use Flash Fill in Excel

4)  Using Shortcut .Ctrl + E

 

Excel Flash Fill Shortcut

To be clearer, I will demonstrate how to use flash fill in Excel.

Steps to Use Excel Flash Fill

Steps to Use Excel Flash Fill


For example, if you want to get the first name and last name from the data above, the steps you need are as follows:

  1. Write the pattern for the first name of one of the corresponding cells. Generally in the top data cell (Type "Pavan" in cell B2).
  2. Move the active cell to other cells in one column. For convenience, just press enter so that the active cell moves below the pattern reference cell at number 1 (Activate Cell B3).
  3. Next select the Flash Fill command which you can find on the HOME Tab - Group Editing - Fill - Flash Fill or DATA Tab - Group Data Tools - Flash Fill. If you are familiar with using shortcuts, you can activate the Flash Fill command by pressing a button .Ctrl + E
  4. Do the same from 1-3 to fill in the data for the last name.

The way to combine first and last names is also the same, the most important thing is to build the correct pattern so that Excel can predict the new data patterns you want to generate with this Flash Fill.

With flash fill you can also take the initials pattern of item names for example, instead of using the LEFT, MID or RIGHT functions which in certain cases might even make it difficult for you.

Examples of Using Flash Fill in Excel

Here I will give 8 examples of using Flash Fill, Excel's newest feature that is sophisticated enough to automatically input data on Excel worksheets.

1) Splitting Text from Multiple Columns

In this case, you can use Flash Fill as an alternative to using the Text to Column feature.

Flash Fill - Separates First and Last Name
Ctrl+E

2) Combining Text from Multiple Columns

Excel Flash Fill as an alternative way to combine text with the Concatenate or ampersand (&) function .


Combining Text from Multiple Columns
Ctrl+E

 

3) Changing Excel Data Format (Numbers and Text)

Besides being able to be used to format text, you can also use Flash Fill in Excel to format numbers.

Flash Fill - Change Data Format
Ctrl+E
 

4) Extract Numbers / Extract Numbers from Text

Apart from using complex Excel formulas to separate numbers and text, try other alternatives using the Flash Fill function provided by Office Excel.

Flash Fill - Take numbers (Extract Number from String Excel)
Ctrl+E

 

5) Extract Text / Extract Character

Flash Fill - Splitting the Date Month and Year Excel
Ctrl+E

6) Extract Date Month Year

Apart from using the DATE, MONTH or YEAR functions to get the date, month and year values, you can also use Flash Fill for the same purpose.

Ctrl+E

 

7) Making Abbreviations (Acronyms)

Don't bother using the LEFT, MID or RIGHT functions if your need is only for abbreviations or acronyms. Try this powerful Flash Fill.

Flash Fill - Create Abbreviations / Acronyms
Ctrl+E

8) Changing Text Capitalization (Proper Lower Upper)

The Flash Fill feature in Excel can also replace the UPPER, LOWER or PROPER functions which are commonly used to change text capitalization in Excel.

Flash Fill - Change Excel's Capitalization
Ctrl+E

How to Enable Flash Fill in Excel

I already use Excel 2013, but why is the Excel flash fill feature that I am using not active?

If you can't use the flash fill feature, take a look at your excel settings in the section. Then make sure the parts are checked. Whether you are successful or not in data entry using the Flash Fill feature, of course, will depend on the data patterns you have. Please explore further for other usage examples. File--Options--Advanced Automatically Flash Fill

How to Enable Flash Fill in Excel


If you find this excel tutorial useful, please share it so that your friends can also get the benefits. Greetings Excel Class.

Leave a Comment