Excel Training- How To Use The FlashFill Tool

Heres a taste of some of the amazing top tips, tricks and shortcuts included in our training sessions.

Anyway, let’s have a look at the awful options a lot of these I know guys you will already know but let’s kick into this straight away. The first one days of the week the abbreviated version so you can see i’ve got Monday here or Mon, I’m going to grab my autofill, then little crosshair drag it down and look what it does it gives you Tuesday, Wednesday, Thursday. I’ll do a brief abbreviated version you can see it also works with the full version so there’s monday I drag it down and hey presto you can see it gives you seven days a week well guess what if it works for the days of the week, it’s also going to work with the months of the year.

Well, if you look right beside that button see them in the data tab here right beside that button is this flash fill you know what makes it even better it’s got its own shortcut key control e. As you can see from the screen there. Anyway, enough for talking about the button, let me show you how this one works. So, as I’m using this one just to give you an example, I’m going to use the shortcut key but if you prefer to use your mouse and click on the data tab and click on the button you know, anyway let’s get started. How does it work? You can see I’ve got a few examples here. Example one to five temple number one.

What if I need to separate the database? How would you have done this in the past you? I would have more than likely used your text to come or some of, you out there, probably use the formula. You probably come across formulas like left right mid and if you’ve ever done something like that you know it’s difficult enough. Watch how simple and easy this is so how does it work? Well, all flash fill needs is a simple little example of what you’re looking for so you can see here I want to take the first name. I’ll put in the first name column. So, take Andren here, okay and you can see I’ve got a quite a number of names because then it’s about row 40 here not that big but again it could work off millions. I give it one simple example and guess what I do, I hit ctrl e and look at that I mean how brilliant is that? Don’t work onto the text column wizard. Now, we’re trying to use the left function with the mid the right whatever. There’s the first name so now what we want, what do we want now? We want the last name, so we give it one simple example watch this. Look, Conway, control e and look at that okay in seconds I‘ve pulled the first name in one column and a last name in the second column, guess what it gets even better that’s just example number one.

Let’s look at example number two what if we’re going the other way, okay what if we want to join data together well again if you’ve been using excelt enough, you’ve more than likely used formulas like concatenate right or the new one that’s come with 365 called concat or you’ve probably used the ampersand to join these things together but here it is, look again, a bit like what we just did. I‘m going to put in Adrian. Here, I give up one simple example guess what flash fill does all the rest I mean how good is that well let’s look at this one you might say well actually the way we work it is we use the last name comma and then the first name so again let’s do it. So, I’m going to put in colony comma, Adrian, here I give it one simple example and look at that let’s tweak it a bit further. So, now I’m thinking well actually you know what all I need really is the first initial of the first name and the last name so let’s put in a Conway, I mean how good is that as I said to you if you’ve not used flashfill before you’re probably looking at that and thinking the hours you’ve wasted I mean I’ve come across people in in the past and the number of years of using this and they’ve been manually doing this.

Okay, let’s look at example number three. So, now we’re going to take it one step further again. So, not only am I looking to join this data but I’m looking to put our company domain on the end of it, turn into an email address. So, look we’ve got Adrian dot Conway at investinu. Guess what I hit flash fill or my shortcut key and it does the rest now at this point by the way just to mention I do get people saying to me well what if there’s different domains put a third column in put those different domains in there and use flashfill.

Okay, second last example. Example number four. So, now what I’m looking to do is I’ve got all these account numbers, okay but really they don’t work for me that well I’d like to have these dashes and break them up. As you can see here, look there’s the example 775-576-644 okay. Every third one I want to dash in here I give the example. Guess what you already know the answer flash fill, I’ll do the rest just brilliant and the last one for today guys is this one what if you want to just extract a certain piece out of it. So, for example I want to take up all these account numbers but I’m looking to take out the numerical part out of august. So, I put in my example two, three, five, six and guess what you already know flashfill does the rest. I mean how good is that? What I love about this one is it’s giving you an insight into the new stuff that Microsoft are bringing out all the time it’s one of the reasons I’ve been using this application for 20 years and it still keeps me interested still love working with excel look that’s it for today guys.

Microsoft Excel is an extremely critical tool in the world of business, education and home offices, but are you using it as efficiently as possible? Even if you are familiar with Microsoft Excel, the number and variety of keyboard shortcuts you can use to speed up your work may shock you.

If you find yourself constantly reaching for your computer mouse or your laptop’s trackpad, you are slowing yourself down and hobbling your typing speed, costing yourself time and energy in the long run. Learning how to correctly use keyboard shortcuts can save you a lot of this time and energy, while boosting your overall productivity. You can find out more about these shortcuts and much more about business, websites and Microsoft Office in our helpful Blog section.

If you found this tip on learning about Microsoft Excel helpful, InvestinU offers a range of training supports, including Webinar Excel Training, for Microsoft 365 which can be found here.

You can check out our Facebook, Instagram, or Linkedin pages for more updates and to get in touch today!