Excel Training- How To Use The FlashFill Tool

Eoin Lynch – Oct 17th, 2022

Here’s a coin to redeem 1 free awesome blog read:

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

Hi guys, it’s Eoin here from InvestinU with some more amazing tips tricks and shortcuts. So, again today I’m going to be using my favourite Microsoft office application as you probably know at this stage, Microsoft Excel or Excel 365. So, let me just step into this presentation very, very quickly and get stuck in. So, today I’m going to talk about an absolutely super new feature. Well, I say new, it came out in Excel 2013, it’s now in 16 and 2019 and office 365. The bad news is if you’re using anything before that it doesn’t exist but we have flash fill now, this is a super tool. So, if this is something you haven’t seen I can guarantee you by the end of this short session you will be finding uses for the flash filter and print mr flashfield you may have come across a tool called text-to-column. Okay, so this is the tool that many people would have used which you can probably relate to for separating data out.

 

“Investinu offers fully customisable software training, using your company’s data – something other companies just won’t do.

When content is pasted so I’m going to tick that box click ok. Now, you’ll see that if I drag down January for a second there’s my autofill options. So, if you find when you first do that that autofill options is not showing up it’s just a quick analysis that’s what you need to do okay so if anyone has any problems like that that’s there’s a quick and easy solution to it.

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

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 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!

Amy Lynch
Amy is a best-selling Harper Collins author and creates content daily for Irish and US audiences. In addition, she publishes regularly with Image magazine, Easy Parenting magazine, and The Irish Independent newspaper, as well as The Sunday Times, TV Choice magazine, and Woman’s Way magazine. (P.S. She’s Also our in house Content Creator).

Enjoy Your Read!

Amy Lynch
Amy is a best-selling Harper Collins author and creates content daily for Irish and US audiences. In addition, she publishes regularly with Image magazine, Easy Parenting magazine, and The Irish Independent newspaper, as well as The Sunday Times, TV Choice magazine, and Woman’s Way magazine. (P.S. She’s Also our in house Content Creator).