Flash Fill, a new feature in Excel 2013, recognises patterns in your data and repeats that pattern for you. A useful example where you might employ Flash Fill is where you need to separate First and Last names into separate columns.
In the screen-dump below you can see an example of Flash Fill in action. In B2 I entered all the characters that are in A2 but only up to the space (in other words the first name). When I the start entering data into B3, Flash Fill repeats that pattern for the rest of the column. Pressing ENTER on the keyboard will complete the action. Pressing ESC at this point cancels the Flash Fill.
Once the Flash Fill has done its thing, a Flash Fill button appears next to your data. Click on this button to either reject or accept the Flash Fill values.
I can now do the same for the last name. This time the pattern that Flash Fill recognises is that I am typing characters that appear after the space in cell A2.
Ways to Activate Flash Fill
There are a number of ways to activate Flash Fill. The method above involved typing the pattern in the first cell and then starting to type in the second cell. Flash Fill gives you a preview of the Flash Fill values: you can hit ENTER to accept or ESC to reject.
Another way would be to enter the pattern in the first cell then use CTRL ENTER to confirm the entry and CTRL E to Flash Fill. This method is demonstrated in the accompanying video and is probably the quickest method.A third method is to type the pattern in the first cell and then use the Flash Fill command on the Ribbon. The command is found on the Home tab within the Editing group, on the Fill menu.
More Flash Fill Examples
[dropcap type=”circle” color=”#ffffff” background=”#66a3bf”]1[/dropcap]In the example below I have used Flash Fill to reverse the order of the names, enter a comma between the names and only return the initial of the first name.
In this next example I have concatenated or joined the First and Last Names.
Flash Fill vs Formulas
Many of these tasks can be completed using text, date and concatenation functions. The advantage of Flash Fill is that is quick and easy to use; the disadvantage is that Flash Fill produces values and not formulae.
What’s the problem with not producing formulae? Well, if I had concatenated the first and last names using a formula such as =A2&“ ”&B2 then the concatenated version of the name is linked to the original entry; if I change the original, the formula automatically updates. This may not be a problem; it will just depend on your situation.