Knowing how to use Flash Fill in excel can be a big time saver when working on your analysis or model. In this post, we would be touching on ways to utilize the full power of Flash Fill to save a lot of time when working in excel. Some of the cases we would be reviewing include:
- Using Flash Fill to extract numbers
- Combining text with Flash Fill
- Using Flash Fill to change cases
- Using Flash Fill to extract and add
- Using Flash Fill to reformat numbers
Before we begin, let’s first establish how Flash Fill in excel works
How does Flash Fill in Excel Work?
Flash Fill is a very useful tool in excel and it is quite easy to use once you understand how it works. Firstly, you need to let excel know what you want to achieve. You can do this by creating an initial sample and then using Flash Fill to complete the remaining. I am sure you are wondering how to create a sample right? Easy, just see the general steps below.
Step 1: The first step is to have your source data, this is the dataset you intend to finetune
Step 2: To the right of your source data, create a sample on the first row by entering the desired output
Step 3: Navigate to Flash Fill through the ribbon or press Ctrl + E to complete the list.
Now that we are all caught up with how Flashfill works, let’ proceed with the different cases we can use Flashfill.
Using Flash Fill to Extract Numbers
In this section, we would be extracting numbers from cells containing both numbers and letters. All you need to do to replicate this is to follow the steps below.
Step 1: Have your source data ready and create a column to the right or left for excel to input the results
Step 2: In the column you created, show excel what to do by inputting the result of the first row in the dataset. In our example, that is on row 2. We would input 5678 in B2 to show excel that we need only the numbers.
Step 3: Navigate to the data tab on the ribbon, then in the data tools group, click on flash fill or simply press CTRL + E and skip going through the ribbon.
Result:
Combining Text with Flash Fill
In this section, we would be touching on how we can combine 2 cells quickly. Yes there are other methods of joining two or more cells, like Concatenate, but using flashfill is much faster than having to write formulas.
In our example, we have the first name and last name of some employees and we want to create an email address for them using the format firstname.lastname@domain.com. In our example we have listed just 6 names but imagine that you are in the HR department of a large organization and you need to do this for over 1,000 staff. It would be time wasting to be manually inputting the email addresses when you can easily use flash fill in excel. How do you do this? Well, just follow the simple steps below.
Step 1: Get your source data ready and create a third column to the right or left for your results.
Step 2: Show excel what you want your output to be by entering the first email address. In our example, this would be the email address of Jake Griffin.
Step 3: Press CTRL + E to fill the list or you can go through the Data Tab > Data Group Tools and click on flash fill. After executing, you should get the result below.
Using Flash Fill to Change Cases
Flash Fills can also be used to change cases for a dataset. Imagine that we have some of the Surnames in in our previous example in CAPS, and we need only the first letter of the surname in CAPS, how can we correct this? Just follow the steps below
Step 1: Input how you want the output to appear in the first row of where you have the name in caps
Step 2: Press CTRL + E or go through the ribbon as discussed in previous sections and you should have the result below.
Using Flash Fill to Reformat Numbers
Flash Fill can also be used to reformat numbers with similar patterns. All you need to do is to give excel a sample and then fill the remaining list with Flash Fill. Let’s take an example below.
Step 1: Type in a sample of the format you want excel to replicate
Step 2: Press CTRL + E to fill the list and you should have the result below
Limitations of Flash Fill
Although Flash Fill is a very useful tool, it has a few shortcomings. Some of these shortcomings are:
- Results are not dynamic. This means that the results are not automatically updated. Sometimes information might change in the source data. If this happens, you would have to rerun flash fill because it won’t automatically update with the source data.
- Flash Fill might wrongly identify pattern and leave undesired results which may be very difficult to spot especially if you are working on a large data set.
- It only works when you need to fill data vertically, it doesn’t work for horizontal data filling
