Would you believe it if I tell you that Microsoft Excel is intelligent enough? Well, some of would agree with me straight away. The other set of people who do not agree with me would agree with me once they are done reading this article. Microsoft Excel has this amazing functionality called Flash Fill that can detect patterns in your data and fill out the rest of the worksheet for you! Well, sounds like something coming in the future? Well, you are in for a treat!
Flash Fill can be used to combine data, extract data, remove characters and a lot and a lot more. And it’s all right here, this is not something coming in the future! In this article, we explain in detail how you can make the best use of Flash Fill with the simplest of examples possible. Hope you enjoy reading!
How to Split Data Using Flash Fill
Let’s say you have a source column where you have data that needs to be split into multiple columns. For example, I have to split the data in the source column into First Name, Last Name, Age, and Favorite Color columns respectively.
Step 1: Firstly, make sure your source data is present and is consistent as shown below.
Step 2: Now, go to the First Name column and type in the first name as you want. Only thing is, it should match the spelling in the source string.
Step 3: Now click on the cell where you have entered the data and then press the keys CTRL + E. This will automatically fill in all the cells in the same column, extracting the first name from the source string.
Step 4: Just like previous steps, you can type in the Last Name, Age, and Favorite Color in the first cells of their respective columns.
Step 5: Once you have typed in the first row, simply press each of the first cells in each of the columns and then press CTRL and E keys to automatically generate the series using Flash Fill.
How to Combine Data Using Flash Fill
Let’s say you have a worksheet and column 1 and column 2 has data. You need to form column 3 by combining the data from column 1 and column 2. Here also, Flash Fill comes into the rescue.
Step 1: In the third column, type in how you want your columns to be combined.
For example, in the example below, I want the First Name and the Last Name to be combined to form the Full Name.
So, I have clicked on the first cell of the Full Name column and typed in the combined full name, which is, John Doe.
Step 2: Now click on the first cell of the Full Name column and simply press CTRL + E together.
Voila! Your series is now generated as simple as that using Flash Fill.
How to Change Case Using Flash Fill
Let’s say you need to combine data from 2 columns and form a third column just like in the previous scenario. But in this one, you want the case of your third column to be different from your source data. In that case, follow the steps below:
Step 1: If you want the First Name and Last Name to be combined, but you want the first letters of both the first name and last name to be of small letter case, then type in that way in the first cell of the Full Name column.
Step 2: Now select the cell where you have entered the data and then press the CTRL + E keys simultaneously. That’s it. Now you have the series generated as per the case of your choice.
How to Extract Only Numbers or Text Using Flash Fill
This is a very interesting trick using Flash Fill. Let’s say you have a source string that has both numbers and letters. You want the numbers in the string to be extracted to one column and the text to another column.
Step 1: Simply type in all the numbers present in the source string in the first row, in the same order, in the Numbers Only column.
Likewise, type in all the letters present in the source string column, in the same order, in the Text Only field.
Step 2: Now click the cells, where you entered the data in the previous step, one by one, and then press the keys CTRL + E to see the magical extraction taking place.
How to Remove Space Characters from Source String Using Flash Fill
Let’s say you have a source string and you want all the space characters to be removed from the source string. Oh yes, you can do even that with Flash Fill!
Step 1: Firstly, enter the string without space characters as shown in the screenshot below, in the destination column.
Step 2: Now press CTRL + E to auto-fill the second column, with strings that do not have space characters as shown below.
Step 3: But if you look at the above screenshot, you will be able to see that the letters in the strings also got removed along with the space characters. Now we need to tell Flash Fill that it shouldn’t remove the letters along with space characters.
For that, simply type in one more value in the second column as shown in the screenshot below. The string shown below has letters and space characters in it and thus it is a perfect example to teach excel of the pattern it should create.
Step 4: Once you are done, simply click on the cell where you just entered the data at and then press the keys CTRL + E. This would give you the refined results. Enjoy!
There are many more possible scenarios where you can use Flash Fill, however, we have tried to cover some of the major ones.
Please tell us in the comments section which Flash Fill trick amused you the most. Thank you for reading.