Excel Training- How To Use The AutoFill Tool

Eoin Lynch – Oct 11th, 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 again with some awesome tips tricks and shortcuts. So, today’s top tip, we’re going to talk about the autofill tool. So, you remember last week we talked about the flash fill tool and how brilliant that was? Well, in today’s one I’m going to take you through an in-depth look at the autofill tool, one of these tools that I know people use on a daily basis. What I found is most problems in a lot of cases is people don’t refer to it as the autofill till they refer to it as the little box down the bottom right-hand corner of the cell but the tool itself is called the autofill. So, let’s step out of powerpoint here for a second and what we’ll do is we’ll take a look at Excel and i’m just going to open up my excel file here and one thing I have noticed I don’t know if this is a bug in any of the latest versions of excel now as you can see from my screen here today you guys know I’m using Excel 365 or Office 365 or as it’s referred to now as Microsoft 365 but what I have discovered is in edge and this is just recently that I’ve discovered this that when you drag something down using that autofill tool. So, I’m just going to use January here as an example and I go to my autofill table which is that bottom right-hand corner of the cell you can see it there and I get that kind of thin crosshair and as soon as I get that, I drag down now.

What you should get at this moment in time is a tag, then the bottom right hand corner of that called the autofill options now what you’re also getting the latest versions is this it’s called a quick analysis option now I promise I will go through that in later videos but today we’re concentrating on the autofilter and you can see I don’t have it. The autofill tool option isn’t showing up now, this isn’t happening at all but what I have discovered is simply this. So, just get rid of that for a second, if you go to your file so if you find that the autofill options when you first try this isn’t working here’s what you need to do go to file you come down to options so we’re going into the settings of Excel here you go into the Excel options dialog box, you’re looking for the advanced section and if you scroll down to the area called cut, copy, paste you can see there’s an option here tick box that needs to be ticked called show paste options button.

“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.

So, I type in jam, there’s my jam I drag that down and you can see I’ve got my… or I’ll drag it down a bit further so you can see it’ll get the 12 months of the year and it’ll just keep repeating itself around the further you drag it down. The same way, look at this I’ve got January, the full version and again if you type that in and here’s the thing, to also remember if you type it in lowercase or all in uppercase, it’ll copy that same formatting all the way down absolutely brilliant so for those that didn’t know that.

At least now, you’re not typing out the days of the week or the month of the year you can get Excel to do it but here’s the brilliant part let’s say I’m working with the days of the week and I’ve got my Monday here and I dragged this list down for a couple of weeks here and you can see as I drag it down it’ll tell you what day the week you’re on so you can see, I’ve got a list of three weeks in there now. I’m looking at that and saying well here’s the problem I have, I don’t work Saturdays or Sundays I only work Monday to Friday. Okay, a standard week, well guess what? You go to your autofill options click on the little drop-down arrow you can see there’s an option there called look fill weekdays, and hey presto look what it does absolutely brilliant. Takes out the Saturday and Sunday and only gives you Monday to Friday now this is another question I do get asked on a regular basis when I’m given these classes and that is well what if I wanted Monday all the way down well one way you can do this is you can drag it down again.

Look, I’m just going to do this again watch this drag it down and I’m looking, I want Monday all the way down, I can go back to the autofill options and look use the option here called copy cells and it copies the Monday all the way down or if you like your little short cookie here’s a good one. I’m just going to type in the Monday again, there’s my Monday but if I hold down, so I go to my autofill tool thin little crosshair hold down the control key and then left click and drag down it repeats the same value all the way down absolutely brilliant two ways to do it get the same result. Now, let’s talk about days of the week all right or dates sorry so you can see I’ve got the first of the sixth 2020 European style date here and you can see look if I drag this down guess what it does it gives you the date series, the day series so the first the second the third the fourth and so on and so forth. Now, I’m going to show you a couple of things here but again let’s look at this and go okay well here’s the problem I have I don’t work Saturdays and Sundays guess what you can do your dates back down to your autofill options look at this look you can and there’s quite a number of options in here but I have the option to say fill weekdays and look at that it takes out the Saturday and Sunday and only gives me Monday to Friday now.

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 FacebookInstagram, 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).