How to use Flash Fill in Excel 2013?
One of the most useful and timesaving tools Microsoft has introduced in Office 2013 is Flash Fill in Excel. In my opinion, this tool itself is a reason to upgrade to Office 2013! Flash Fill takes tedious tasks, often performed manually, and executes them automatically. You don’t have to insert a single formula – Excel learns and recognizes patterns of what you are trying to do and fills in the data for you in a flash! In this week’s blog post I wanted to share with you some background on Flash Fill and show ways in which you can use it.
How it all got started
In December 2009, Sumit Gulwani, a senior researcher at Microsoft Research Redmond, was sitting next to a businesswoman on a flight to Redmond. The woman, who was thrilled to meet a Microsoft employee and Computer Science Engineer, opened up her laptop and asked Sumit if there was any way to merge two column with first names and last names into a single column containing the full names in Excel. This got Sumit thinking that there should be a way to help end users perform various common tasks in Excel automatically, without them having to know any formulas. So he and some other researchers started collaborating with the Office 2013 team, and a few years later they introduced Flash Fill.
How it works
Take the business woman’s example, you have two columns of names and you want to merge them into one single column with the full name. To use Flash Fill just start filling in the column with full names the way you want it to appear.
If you continue to fill in more names Flash Fill will automatically kick in and fill the full column with names for you. If you want to activate Flash Fill yourself, fill in the first cell, hit enter and then press the keyboard shortcut CRTL + E. What happens in the background is that Excel tries to understand what you are trying to do by combining millions of small programs that might accomplish the task. Excel then sorts these code snippets to try to find the one that is best suited for what you are trying to do. Flash Fill learns by demonstration, so if it doesn’t understand what you want, just keep filling in cells and it will try a different pattern.
Say that you for instance only want first names – not middle names or initials. Continue to fill in cells manually demonstrating what you want (in this case I’ll type only Karl and leave out the middle name Johan in the third cell) and Flash Fill will again try a different pattern.
You can use Flash Fill for a number of different tasks.
You can use Flash Fill to merge columns like in the example with first name and last name into full names.
(If you don’t have Excel 2013 with Flash Fill and you want to merge the full contents of column A and B, you can achieve the same result by entering the formula =A1&B2 or =CONCATENATE(A1,B2) in cell C1. )
You can use Flash Fill if you want to split contents from one cell into multiple cells. Perhaps you have a column with full names but you want columns with first name and last name, or you have a column with prices where you want to split numbers and currencies into two different columns.
(If you don’t have Excel 2013 with Flash Fill you would achieve the same result by using the “Text to Columns” tool).
Flash Fill can also be used if you want to clean or modify data. Perhaps you have a column with city names where some of them begin with a space? Just type in the city name the way it is supposed to be written and Flash Fill will remove all spaces.
(If you don’t have Excel 2013 with Flash Fill you can achieve the same result by entering the formula =TRIM(A1) in cell B1. )
Perhaps you have a full text string and you only want to pick out a certain word, for instance, city from the full address? Just write the name of the city you want to select and Flash Fill will pick out the rest for you.
Formatting text and numbers
Quite often you have data in your spreadsheet that you want to format in some way. Perhaps you have a column with names but they are all in uppercase. Again, just start typing the names the way you want them to appear and Flash Fill will format the names the way you want them.
(If you don’t have Excel 2013 with Flash Fill you can achieve the same result by entering the formula =PROPER(A1) in cell B1. )
You can also use Flash Fill to censor data. Perhaps your data contains some sensitive information that you want to censor. In this example, I have a column with ID numbers where I want the last 4 digits to be censored so that you can’t make out the full ID number. Again, start typing the data the way you want it to appear, press enter and then CTRL + E and Flash Fill will fill out the rest for you.
(If you don’t have Excel 2013 with Flash Fill you can achieve the same result by entering the formula =REPLACE(A1,8,4,”XXXX”) in cell B1. )
In order to accomplish rather straight forward, data re-arrangements requires quite advanced formulas in Excel. Take the following example, you have a column with first and last names in upper case that you want to convert so that it is displayed last name comma first name in proper case.
The formula to be used to achieve that result looks like this:
=PROPER(RIGHT(A1, LEN(A1)-SEARCH(” “, A1))&”, “&LEFT(A1, SEARCH(” “,A1)))
With Flash Fill you don’t have to know how to write complex formulas like this, just start filling out the column the way you want it to appear and Flash Fill will do it for you!
As you can see, using Flash Fill, data re-arrangements like this one becomes a lot easier and saves you a lot of time.
Benefits of formulas versus Flash Fill
There is one caveat to using Flash Fill. Flash Fill doesn’t take into account changes in data. Say for example that you have used Flash Fill to combine the first name and last name into a single full name column. If you make any changes to the names in the original columns, they will not be reflected in the combined column.
As long as you are aware of this limitation, you can gladly continue to use Flash Fill – no need to bother with tricky formulas anymore!
(If you have difficulties reading this article, you can access the full article in pdf here).