Flash Fill is like a copy cat, which can do the things in the same pattern in which you are doing. You just have to do that operation once & Flash Fill do it for the rest. It can help you to make your data entry much easier & make you less dependent on complex formula for little things.

Let’s read this post & find out more that how can you improve you data entry tasks by using Flash Fill.

How To Use Flash Fill

If you look at above example, I have entered the name without using the prefix in first cell & Flash Fill following the same pattern to get the names from rest of the cells. You can apply it by using following simple methods. Let’s have a look.

• Using Ribbon

Go to Home Tab -> Editing -> Fill -> Flash Fill. Or,  You can add flash fill option to the quick access toolbar for your convenience.

• Enabling Automatic Flash Fill
  

You can activate the automatic flash fill option to fill entries automatically once you entered your first entry. It will give you suggestions to fill rest of your entries based on the pattern of the first entry.

To activate automatic flash fill option. Go to File -> Options -> Advance -> Editing Options -> Tick Mark automatically flash fills.

• Using Shortcut Key
  

You can also use the shortcut key Ctrl + E to apply flash fill.

How Flash Fill Works

Flash Fill follows the pattern applied by the user for an initial data entry & created the rest of the entries based on that pattern. For Example, While performing some data entries you have to write the name of the person as mentioned in his email id & you have to do it for 1000 email mail ids. Now, What you have to do is to just write the name in the first cell as mentioned in email id & apply Flash Fill. Rest of the 999 cells will automatically fill with the name as per its corresponding email ID, using the same pattern you have used in your first cell.

Let’s follow some useful examples mentioned ahead to understand this magic wand.

10 Useful Examples To Understand Flash Fill

I have just listed some useful examples which can help you to learn about flash fill in the best way. I have also tried to make clear the logic behind the working of flash fill. So, go get it.

• Extract Name

In below example, I have type full name in the cell & flash fill entered the rest of the names automatically. Now if you check the pattern, the first name “bill” is exactly starting from the 13th character in the cell & the last name “Jelen” is starting from the 12th character. Using this pattern flash fill extract the text starting from the 13th character from the first name & from the 12th character from last name cell. And, put them jointly into full name cell for the rest of the entries.

• Extract Text From Date

In this example, I have used a suffix with day number, entered the month in short form & year in full number. The Same pattern is followed by the flash fill to get the rest of the data. Here, you will say that this option is quite smart to work with data but there is a need to take care while using flash fill with dates. As, it is just following the pattern you have used in your first entry.

• Change Text Case

Now look at this example, just write your text in capital letters & flash fill will do the same with rest of the entries following the same pattern entered in the first entry.

• Extract Number From Text

Now, Here what we are doing is, extracting the numbers from a cell which have the both text & numbers. Once I have entered the number in my first entry & used flash fill. It gives me the same results like I have done in my first entry.

But, here we have one point to understand that it is not extracting numbers. It is just extracting the part of the cell from where the number are starting.

• Remove Unwanted Characters

And here, you can remove unwanted characters from a mobile number. I have entered exact 10 digits mobile number in my first entry & same is followed by flash fill to remove the unwanted characters from rest of the cells.

You also use flash fill get convert a simple number to a mobile number format or to a social security number.

• Extract Decimals from Number
  

Yes, you can use it to get decimal numbers. What I have done in this is, I have just entered the numbers which are after the decimal point & for rest of the number flash fill done with the same pattern and get all the numbers which are after the decimal point.

• Concatenate

Now its time to do some concatenation. Flash Fill will also allow you to concatenate text or numbers is a simple way. You just have to enter your entry & flash fill will do for the rest of the entries.

• Extract Text

You can also extract some of your text from a cell. Just do it for your first entry & then use flash fill.

• Extract Name From E-mail IDs

This one is my favorite. You can extract the name of a person from his email id. What you to do is, just enter the text which is before the dot(.) & the which is after the dot & before the @. And, Flash fill will go for same in rest of the cells.

• Add Quick Number Formatting

You can also make some number formatting with flash fill. Now if you look at here I have added a dollar sign with the number in my first entry, which is the per defined format in excel. When flash fill enters the rest of the numbers in cells with the dollar sign, excel assumes it as a currency format.

Things To Remember

There are some points which we need to take care while using flash fill.

Download Sample File

Conclusion

This tool is really a magic wand for those who do want to use formulas while doing some simple data entry. If your data has a same type of pattern, you can go with Flash Fill.