How to use excel's flash fill feature to auto-fill cells

excel-flash-fill

Why excel's flash fill feature?

Flash fill is an amazing feature in Excel which can be used to merge characters or extract characters, like, numbers, capital letters, small letters, and symbols.

Shortcut to use flash fill is Ctrl + e, or it can also be accessed from the Data tab flash fill option.

flash-fill

Points to be noted is that Excel will not recalculate values when you change your first value or second. In some case, you may have to reference two cells in order to work correctly. For example, in case if you are using numbers you need to select the first two cells and then click ctrl+e.

Let us see in detail,

Extracting numbers from string:

As mentioned above flash fill can be used to extract numbers from an alphanumeric word. For this, it is mandatory to mention the type of text to be extracted. Here we are going to extract only numbers from the string so check the whole string for numbers in the first row. It is clear that we have two numbers "7 & 3" so type it in the adjacent cell as 73.

For numbers it is necessary to add two cells as a reference, so we are going to add the second reference by checking the second row. As you can see we have two numbers in the second row which is "8 & 6". Type 86 it in the adjacent cell and press Ctrl+e, which is the shortcut for flash fill.

flash-fill

As you can see, it extracted all the numbers so quickly. Note that last row is empty as there are no numbers in that string.

Extracting capital letters:

Extracting capital letters is the same as extracting numbers but, instead of taking numbers as a reference now we will take capital letters and unlike referencing two cells in numbers here one cell is enough to flash fill.

flash-fill

Extracting last two letters:

It is all about patterns and how we want it to be. Here we want to generate the last two digits of the string. So we typed the last two didgit which should be the same as it is in the string and press Ctrl + e, which fills rest of the cells as we want.

flash-fill

Extracting first four digits:

Here the pattern is going to be first four digits, so we should type first four digits of the first string so that Excel knows the kind of pattern we want and press Ctrl + e.

flash-fill

As you can see, it generated the first four digits of all the string.

Extracting username from an email id:

In this, we are going to extract username inside an email id. For this to happen type the first row with only the user'name without an email domain name. Then it is all the same now, press Ctrl + e to generate all the user's name. The same is depicted in the below picture for clarity.

flash-fill

Hope you liked this article, if so please share it with your friends.

If you want more clarity or demo of this code, please watch the below youtube video.

For the detailed demo, please see this video, How to use excel's flash fill feature to auto-fill cells

Share on:
Most Popular