Flash fill is a great feature that has been available in Excel since 2013. Although it is not dynamic unlike a calculation it does provide a really quick way to produce a new column of data where there is a pattern related to other columns that already exist.
How does flash fill work?
Flash fill normally works automatically. If you enter the first entry Excel will look for a pattern in the existing data and once you start typing in the second record it comes up with suggestions for the other records. If this works then the other rows populate as soon a Enter is pressed. If this doesn’t work one of the methods below would need to be used.
Type in the first entry. Move to the next cell and press CTRL + E
Type in the first entry. Move to the next cell. Select Flash Fill from the Data tab
NOTE: If the flash fill misinterprets what it should be extracting try adding a second entry before forcing the flash fill. This gives Microsoft Excel more data to work on so it is more likely to make a correct assumption of the pattern it needs to follow. If Excel cannot see a pattern it will repeat the first entry.
Flash Fill Examples
Here are some classic examples where flash fill can be used. In each of these cases use one of the methods above to get the flash fill to work.
- Splitting Columns
If your data contains a column where you need to access part of that information this will work. Just type in the first record you need and let flash fill do the rest. A classic example of this is where you have a first name and surname in the same column but the data needs to be separated. Flash fill would be implemented twice, firstly to extract the first name and then to extract the surname.
- Combining Columns
If you require 2 columns to be combined into 1 such as having first name and surname in 2 separate columns and you need to view them together.
- Changing case
This can be used to change the data to upper, lower or title case if required.
- Creating email addresses
As long as there is consistency in the company email addresses columns containing first name and surname could be used in a huge variety of different ways to create email addresses. This could range from email@example.com to firstname.lastname@example.org or just email@example.com
- Removing extra spaces
Extra spaces at the start and end can be removed and if there is more than 1 space between words that can also be remedied
- Extract day, month or year component of a date
Just be careful if the day or month component is being extracted if the date is in dd/mm/yyyy format. With numbers the leading zeros will be removed. The solution is to add an apostrophe to the start of the number which forces it to be viewed as text.
This is not an exhaustive list but if you think there is a pattern in what you are trying to achieve it is definitely worth trying. Just remember that this method is not dynamic so if the data changes you will need to redo the flash fill. If you require a result that automatically updates when changes are made then a variety of different functions could be used to get the same results.
If you’ve enjoyed reading about how to use Flash Fill in Excel, there’s some other blogs below that you might find useful: