Flash Fill: Merge data in different cells - Microsoft Excel Tutorial

(upbeat music) - So I got a beautiful thing to whoop on you right now. This is new in Excel just within the past few years, and it deals with a situation where when we get data that we can't use, how do we clean it up? In this case, flash fill is magical. Let's check it out. Look at this. We've got, in this example, the cities and the state abbreviation. We want just the city part. Challenge is we have is New York is two words. Notice Chicago is all caps. We need to get that proper case. Rolling Meadows, we've got an upper case and a lower case. We're going to clean this up, flash fill, Jack. Here we go. New York, we just have to type it. And then watch, type the B for Baltimore. Check out that ghosting. This is flash fill asking, is this what you want? So look, it's not fixing the cases. So let's escape out and train the flash fill. Let's go to Miami. Capital M-I-A-M-I, and then let's highlight this whole range, go to data, flash fill. There it is. There it is. We are done with this, look at that. Rolling Meadows is right, Cambridge is right, everything is right. We are done. We didn't have to use a formula and we surely did not type all of these one at a time. Flash fill is magic. (funky music)