Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Rather than typing a lot of repetitive data, why not let Excel do it for you? If you take a look here at this sheet, we have months and we did this in the previous movie. Now let's just select all those cells and delete. Let's get back to this first one here in B5 and type the word January and instead of pressing Enter, which would move us down to the next row, let's press Ctrl+Enter so we stay there. Now, if you take the close look at the cell, you'll notice in the lower right corner of the cell is a little square dot.
Now your mouse pointer is usually this big plus sign, but if you put the mouse pointer on that square dot, the mouse pointer turns to a crosshair. That's what's called the Autofill mouse pointer or the Autofill handle. When you get that and it has to be that, it can't be another mouse pointer, drag to the right and look what happens. It fills in the months for you. Well that's pretty cool and there's a few other things we could do. Let's go down here to Sheet 2. And this Sheet 2 is expecting to see weekly average. So in that same cell B5, type Monday and again press Ctrl+Enter, put your mouse over that dot so you get the crosshair and then drag out here until you get to Friday.
Well that's pretty cool, but there's a few other things we can do. Let's go to Sheet 3 and just show you a few things. Let's say we wanted January, but we wanted to abbreviate it and you can just type this here in the first cell. So I'll just type January again, press Ctrl+Enter, and this Autofill works going down a column, not just across a row. When you get that crosshair, you can drag down and see what happens is it repeats. Once it gets down to the end of the series, the series just starts again. And the same for the days. Now you can type in the day, type in Monday and Autofill down to the bottom and it repeats.
It'll just make the columns be a little wider. If you abbreviate it, press Ctrl+ Enter, it will repeat abbreviated. Well, that's pretty handy. What do you do for numbers? Numbers are a little different. Let's go up to the top here and maybe just type the number one, Ctrl+Enter, and when you Autofill it, notice it just gives you a whole lot of ones, and the reason is that there is no built-in series. Let's go over here and type in a 1. Ctrl+Enter. This time if you hold the Ctrl key down, when you get that Autofill handle, now when you drag down and I'll just let go off the mouse first, now it will increment.
Well what if you want to create your own series? Let's go over here in column H, maybe you type in 10, press Enter and maybe you type in 20. Remember to press Enter. Now select both of these cells together. When you have multiple cells selected, they share a common Autofill handle. Now put your mouse pointer on it, get the crosshair, now when you drag down, you notice that it keeps that series. So that's all pretty cool. Well this is all fine because months and days are built-in series, but wouldn't it be nice if you could create your own sort of pre-made series? It could be a series of your clients, or vendors, or people you work with. It could be anything.
Well, let's say we wanted a list of regions where our company operates. We can create a custom list. So here's what we do. Go up to the File menu, so in Backstage view, go down here to Options and then in the left side here, go to Advanced and let's scroll down. Down towards the bottom, we find this button Edit custom lists. Click that and here it shows you the days and the months, so it's great. Now over here it says List Entries. Click in this empty box and maybe let's type a few things. Let's say we operate in New England and the Mid-Atlantic, Southeast, Midwest, Southwest, and maybe also in Pacific.
Now click Add and you see that adds the list here. Now that's great. Click OK, click OK again. Anywhere you want you can type in New England. I'll press Ctrl+Enter and I'll make that count little wider. Now when we Autofill, you'll notice if gives you that list. What's also really great is you don't have to start at the beginning of the list. Maybe if you started with let's say Pacific, it would still give you that list. Or maybe if you started on Thursday, and Autofill and this will work if you fill across as well as down.
It'll give you that list. So this is a really great feature. Let Excel do a lot of the typing for you.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 87391 Viewers
80 Video lessons · 136396 Viewers
59 Video lessons · 55044 Viewers
52 Video lessons · 68912 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.