How to Use Flash Fill in Excel 2013

Excel 2013’s new Flash Fill feature anticipates your formatting and data requirements and accommodates them on the fly. Excel does so by learning and recognizing patterns and then uses its auto-complete functionality to finish the work you started. Flash Fill encounters, interprets, and responds – you don’t have to do a thing but understand what you’re seeing and accept or reject the free offer.

“Flash Fill brings the power of text manipulation to the hands of common, non-technical, everyday users,” says Chad Rothschiller, a program manager with the Excel 2013 team.

Flash Fill formatting

Formats determine what you see, but they don’t change the stored value. Formats make data more readable and meaningful. Using Flash Fill, you can often skip formatting tasks because the feature will watch what you do, learn from it, and add the formatting characters for you! To illustrate, we’ll format a column of phone numbers (shown below) using Flash Fill:

1. To get things started, enter (222) 555-1234 in cell E1.
2. Select E2 and enter ( to engage Flash Fill, which will interpret the repetitive first character as a series and try to interpret your needs.
3. To complete the remaining list with Flash Fill’s suggested values, press [Enter]. Pressing [Esc] removes the list so you can continue inputting values yourself.

At this point, you can decide whether you want to delete the unformatted values, or not.

Earlier, I used the term format, but we didn’t actually apply a format to the data. I’m using the term generically; I’m not referring to the pre-defined formats you can apply to data. In this case, Excel stores the additional characters with your phone numbers. You won’t always want to take this route, but when you can, it’s a great shortcut.

You’ll notice that the feature isn’t flawless – it didn’t accommodate the phone number in D3, which doesn’t have an area code. Consistency matters and Flash Fill isn’t that smart – at least not yet.

Flash Fill lists

Earlier versions let you create custom lists for reuse. It isn’t difficult, but there’s a bit of setup involved. Now, you can use Flash Fill. Of course, you must enter the list the first time, but after that, Flash Fill takes over. To illustrate, let’s recreate the list of last names in our example sheet:

1. Enter Harkins into F1.
2. Enter S into F2 – the first letter is all you need. Flash Fill will automatically fill in the remaining cells based on the pattern you created.
3. To enter the assumed list, press [Enter].

Flash Fill concatenation

Combining lists use to require a column of CONCATENATION() functions. Flash Fill needs only an example to learn from to combine values for you. Let’s combine the example names into a single column using Flash Fill, but a word of warning first: I’ve intentionally entered values to confuse Flash Fill. Knowing its limitations is as important as knowing how to use it. Now, let’s work through an example that won’t respond well:

1. In cell G1, enter Susan S. Harkins. (Notice that I added a period character to the middle initial value.)
2. In cell G2, enter A and wait. Nothing happens.
3. You’ll have to enter values manually until you reach row 4, John A. Phillips. Unfortunately, Flash Fill is confused and enters the value from the row above.

Sometimes, Flash Fill just won’t be able to interpret the pattern from your data and your input. If the list were longer, Flash Fill might eventually work it all out.

After repairing the original data a bit, Flash Fill responds better. Add a middle initial for Alexis Stanley in row 2 and try again. This time, Flash Fill kicks in on the third record. Consistency in your original data will be the key to the best Flash Fill experiences.

Flash Fill parsing

Data doesn’t always come in neat normalized fields – sometimes you have to parse what you need. Just as Flash Fill concatenates autonomous values, it parses multiple values stored together. Let’s use Flash Fill to parse the names you combined in the last example:

1. First, delete the original data. That way, you can see that Flash Fill is using your concatenated values and not the original data.
2. In cell H1, enter Harkins.
3. In cell H2, enter A and wait for Flash Fill to kick in. When it does, press [Enter] to complete the list.

One last trick

I have one last Flash Fill trick to share. In cell I1, enter Harkins, Susan (last name, first name format). In cell I2, enter S – the first initial of Alexis’ last name – to continue the pattern. Flash Fill will recognize the pattern and complete the transposed list accordingly! Press [Enter] to complete the list.

---

Courtesy: techrepublic.com