Flash Fill

Learn Excel from MrExcel podcast, episode 2041 - Flash Fill!

I'll be podcasting all of my tips from the book, click the “i” on the top-right hand corner to get to the playlist!

Alright, just a quick ProgramNote. The original intent here was we had 40 tips in the book, and it took me about 61 podcasts to get through those 40 tips, because there were some tips that took 2 or 3 or 4 episodes to get through those. And now, all of a sudden we jump from episode 40 to episode 62, what's going on there? Well, after the first section of the book “Top 40 Excel tips”, there are 40 keyboard shortcuts, and then Section 3, another 30 tips for Excel’s 30th birthday! And most of these tips were not written by me, they're written by my co-author’s Szilvia. So I'm not going to be podcasting Szilvia's content, but I am picking up where I picked up, which was tip #22 in Section 3, which is really tip #62. And so here we are, I will get you tip 62-70 over the next week or so, alright.

Today, we're talking about a great new feature that came along and Excel 2013 called Flash Fill. Let's say that we needed something crazy, like first-initial-last name. Could I do that with a formula? Sure, =LEFT of this, ,1 , that's easy, but then I have to do a FIND to find the space, and do a MID starting from that FIND, out to the length of the whole thing. But watch how easy this is with flash fill, like I just give it a sample, so type the 1st one in the format that I want, so here I'm using “firstinitial. lastname”, but only the first letter is capitalized. As soon as I press Enter and type the G here, they will detect what I'm doing, and they will fill it in correctly, alright.

Now, at this point, we have one chance to make a correction. 2 people have a middle initial, do I want the middle initial to appear? If I do, I go to one of those, and I edit, F2, P. See, it kind of has a little shadow back here, saying that this is the whole flash fill area, and they're going to reevaluate that whole area when I press Enter. And sure enough, flash fill, Changed Cells: 1. Martha K. Wendel now shows up as M. K. Wendell, so you can make one correction after the fact, cool huh?

This is also great for doing things that would be a lot harder to do, breaking text apart. If I wanted to grab the word “consulting” from the end, I just type the first one, and then here I'm going to press Ctrl+E. Ctrl+E is the shortcut for flash fill, it's also back here on the Data tab, that icon there, but Ctrl+E will allow you to invoke that. Now hey, one thing to be careful about is with flash fill, flash fill is programmed to never automatically fill numbers. So if you wanted to fill numbers, see when I start to type the 11 here, they're going to grade in, and then they're likely to take it away. So type a couple to give it a pattern, and then go to the blank cell and press Ctrl+E, and they will correctly get that. Alright, not just 40 tips in the book, but bonus tips, buy the whole book, save some time, it's dripping with spicy tips, click the “i” on the top-right hand corner.

Alright, Flash Fill was introduced in Excel 2013, is great for parsing or concatenating text. The new column, the Flash Fill column, has to have a heading, otherwise they're not going to detect that you're trying to use flash fill. Type the entry for the 1st row, as soon as you start to type the answer for the 2nd row, the results will grey in, press Enter to accept those results. Once the results are there, correct another entry, and flash fill will go through looking for things that match the new pattern. You can invoke flash fill with Ctrl+E, so type the first one, go to the next blank cell, Ctrl+E, it tends not to work automatically with numbers, but you can still use it for numbers by using Ctrl+E.

Well hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!