One benefit of using Microsoft Excel is that there are multiple ways to achieve the desired results. For example, we could use various formulas if we want to manipulate text from one cell to use in another. But, what if there was a way to do these repetitive tasks without knowing any programming language? I’ll introduce you to Excel’s Flash Fill feature and some input-output examples.
Simple Patterns VS. Complex Formulas
You might be confused by my mentioning patterns at this stage, but that’s an easy way to think of this feature. We’ll be looking at a data column for commonalities so Excel’s machine-learning techniques can take hold. Let’s look at a couple of examples.
Maybe, someone put people’s full names in one column, but you prefer to have them split into distinct fields. You could split the first and last names using formulas, text to columns wizard, or even extract text from an Excel cell. But, that’s probably overkill if you need First and Last names.
Another example might be reformatting phone numbers or social security numbers in a specific format. This feature shines when people have inputted multiple phone formats. For example, some people wrap the area code with parentheses and other people string all the numbers together.
Start With The End in Mind
In the example below, I have a series of US presidents. Column A has the Last name, comma, and First name. And sometimes, we have a Middle name. I prefer having a separate column for the first name and another for the last name.
I’ve provided Microsoft Excel with a pattern for John Adams in cells B2 and C2. Notice how I’m not worrying about the comma or middle names. If you need all those fields, I’d suggest the tutorial on how to separate names in Excel.
Flash Fill has auto-filled cells based on my providing a simple pattern, the yellow highlighted cells. In other words, Excel is making an educated guess on what I want in the red box.
What Makes a Pattern?
You might be thinking this was too easy for Excel. It just had to look at the cell in column A and grab the first word after the comma, and put it in the adjacent column. True, but it also works well, if I alter the president’s names by adding leading spaces, semicolons, removing punctuation, or adding double spaces. See the yellow entries below.
And if I don’t like the results, I have a control where I can Undo Flash Fill.
How to Use Flash Fill in Excel
For these instructions, I’m using Microsoft Office 365. The feature works in any version of Excel 2013 and newer.
In this example, I want the flash fill to use the 3 letter code in the middle of the Program Name and copy it to the Country column. In addition, I want the country code to be uppercase.
- Open up your spreadsheet.
- In the adjacent column, I want to enter my pattern in cell B2.
- I’ll enter CAN since I want Excel to change the case.
- Press Ctrl + e.
Excel will take an educated guess and fill in the remaining entries in the red boxed area. If you didn’t like the results, you can use the control in C3 to make changes.
Another way you can use flash fill is to substitute values. This can sometimes be easier than using search and replace or learning how to use Excel’s IF function.
Let’s take an example where someone has inputted prices, and they all end in .00. You prefer to have Excel make the ending zeros be a dash.
Again, pressing Ctrl + e achieves our results.
Multiple Patterns & Rearranging Data
Flash fill can do more than extract text or change the case. Many years ago, I had to do software testing that required using credit card numbers. You couldn’t just make them up with random numbers because a checksum was involved.
I could’ve found a starting set and then used Excel flash fill to create a new set by rearranging the numbers. In the example below, I want to create new test numbers by shifting the original last group of 4 numbers to the second group of the New Test CC.
However, I don’t get my desired results when I use the feature. I can see flash fill correctly moved the last 4 digits into the second spot on the adjacent cell. However, they added more numbers. I wouldn’t even be able to use these new numbers as they are too long for my purposes.
The above incident is an example where you need to provide multiple pattern examples in the adjacent cells. I’ll add another pattern in cell B3 and erase the remaining errors.
Now, if I use flash fill, I get my desired results.
Troubleshooting & Tips
Flash fill is a great feature, but it may not work for you straight away. It needs to see enough data patterns for the machine learning to kick in.
- You can also use Flash Fill from the Data tab.
- Sometimes you need to provide multiple patterns for it to work.
- Your adjacent cells should not be blank cells. Don’t have blank columns in between, or you’ll encounter an error message that reads:
“We looked at all the data next to your selection and didn’t see a pattern for filling in values for you. To use Flash Fill, enter a couple of examples of the output you’d like to see, keep the active cell in the column you want filled in, and click the Flash Fill button again.”
- Flash fill isn’t dynamic. If I change the underlying data, such as the data in Column A above, my cell contents in Column B will not automatically update. I need to invoke Flash Fill again.
Hopefully, these Excel flash fill examples have shown the power and ease of the feature. It can be used in countless ways to manipulate text without resorting to more complicated Excel formulas and substrings.
And sometimes, other Excel functions are better for specific tasks. This was the case when I needed to convert month text values to a number. The key is defining your pattern and adding a couple of examples. Sometimes you may have to tweak things, but it can make a tedious task more fun.