Fixing Excel Data Using Flash Fill

March 21, 2018 - by Bill Jelen

Every Wednesday, I am feating a favorite Excel tip from Excel Project Manager Ash Sharma. This week, Flash Fill. Introduced in Excel 2013, Flash Fill will allow you to extract characters from a column of data or concatenate data from columns without writing a formula.

Consider the example shown below. A product code has three segments separated by a dash. You want to extract the middle segment. The first segment could be 3 or 4 characters long. The second segment is always 2 characters. The formula to extract the middle segment isn't rocket science, but it is not something a beginner would come up with: =MID(A2,FIND("-",A2)+1,2).

But with Flash Fill, you don't have to come up with a formula. Follow these simple steps:

1. Make sure there is a heading above A2.
2. Type a heading in B1.
3. Type ME in B2.
4. You have a choice here. You could select B3 and press Ctrl + E to invoke Flash Fill. Or, you could go to B3 and type the first letter of the correct answer: E. If you start to type in EU, Flash Fill will jump into action and show you a greyed-in column of results. Press Enter to get the results.

Personally, I guess I am a bit of a control freak. I want to tell Flash Fill when to jump into action.

The greying in effect shown in the previous figure is awesome to allow an Excel rookie to discover that Flash Fill exists. It will detect that someone is about to type thousands of cells and prevent that from happening. Flash Fill has probably saved millions of hours of white-collar productivity.

But there are subtleties to Flash Fill - I call it Advanced Flash Fill - and if you understand those subtleties, you will want to make Flash Fill wait until the right time.

If you want to take control and only have Flash Fill happen when you press Ctrl + E, go to File, Options, Advanced and uncheck Automatically Flash Fill.

Here is a more complex example. The product code in column I contains a mix of digits and upper case characters. You would like to get just the digits or just the characters. There is a formula for this, but I can't remember it. Feel free to watch Dueling Excel Video 186 for the array formula or VBA User Defined Function. I am not going to watch the video, because I can solve this with flash fill.

This is one of those cases where Flash Fill won't be able to figure the pattern out from one example. If you type '0252 in J3 and then Ctrl + E from J4, Excel can't figure out the answer any time the part number starts with a digit.

Instead, follow these steps:

1. Make sure there is a heading above I2. Add a heading in J1 and K1. If there aren't headings, Flash Fill will not work.
2. You absolutely need the leading zero to appear in 0252 in cell J2. If you just type 0252, Excel will change it to 252. So, type an apostrophe (') and then 0252 in J2.
3. In J3, type '619816
4. In J4, type '0115
5. From J5, press Ctrl + E. Flash Fill will correctly get just the digits

1. Select columns J & K. Home, Insert, Insert Sheet Rows to move the Digits column out of the way.
2. In J1, type a heading.
3. In J2, type BDNQGX

4. In J3, press Ctrl + E. Flash Fill will work correctly.

   

Flash Fill can be used on multiple columns. In the example below, you want the initials from column Z, each followed by a period. Then a space and the last name from column AA. You want the whole thing to be proper case. If it weren't for people with double-barreled first names, you could have used =PROPER(LEFT(Z2,1)&". "&AA2). But dealing with M. E. Walton would make that formula dramatically more difficult. Flash Fill to the rescue.

1. Make sure there are headings in Z1, AA1, and AB1.
2. Type J. Doe in AB2

3. Select cell AB3 and press Ctrl + E. Flash fill will jump in to action, but it will not use both initials in Rows 6, 10, or 18.

   

4. Select cell AB6 and type a new pattern: M. E. Walton. Press Enter. Miraculously, Flash Fill will look for others with this pattern and will correct B. B. Miller and M. J. White.

   

Thanks to Ash Sharma for suggesting the awesome Flash Fill feature as one of his favorites.

I love to ask the Excel team for their favorite features. Each Wednesday, I will share one of their answers.

Title Photo: fotoblend / pixaba