How to Auto Fill Data in Microsoft Excel 2016
During this Microsoft Excel 2016 training tutorial video, we will demonstrate the different ways you can use the Fill feature in Excel. You will learn how to work with the fill handle, fill dropdown and series dialog.
Video Transcript
We’ve already looked at filling a series of cells a couple times already in the course. We looked particularly at filling a series with the months of the year. But in fact, many times when you’re working with Excel you’ll find that the data follows one or more distinct patterns.
Now let me just quickly do again something we’ve done a couple of times already. I’m just going to type January in that cell and this little block, this little icon, the bottom right hand corner of the board here it’s called the Fill Handle. And if I pull the Fill Handle across, let’s just go to there say. You’ve already seen that you get the months of the year. Let me just Undo that, go back to just having January. But this time instead of pulling across with that Fill Handle what I’m going to do is to select the cells up to R4. So I’m going to hold the Shift key down, press R4 and all the cells between G4 and R4 are selected. Now what I’m going to do is I’m going to go the Fill dropdown in the Editing Group on the Home Tab and I’m going to select Fill Right. And what happens this time instead of getting the months of the year is that I get the same cell content, in this case the word January, in each of those cells. So that isn’t a Series Fill that we refer to. It’s really just a Fill, a Fill Right. Now let me Undo that yet again.
There is a Keyboard Shortcut for Fill Right that you’ll probably find yourself using quite a bit. It’s Control-R. Let me do Control-R here and that gives you a straightforward way of doing a right fill. But in that case what you’re doing is effectively copying the contents of one cell into a number of other cells.
Let me just go back. Let me Undo that yet again and I’m going to type February in H4. Again I’m going to select G4, put the Shift key down. Now I’m going to do my right fill again, Control-R. What do you think is going to happen? That’s right. You still get January in every cell because basically with my left most cell selected I select the range of cells I want to fill and basically it goes left to right filling those cells.
Now similarly if I were to hold the Shift key down and click in G14 I could do a Fill Down which is Control-D or let me just Undo that, click in G4 again, use the Fill Handle to drag down and I get my sequence of months again.
Now it’s important to realize that you can’t only fill right and down. You can also do up and left. Let’s suppose I put the word Hello here. And if I wanted to basically have Hello going up the sheet. Say I’m starting here in U23. If I select a cell above that, go to the Fill dropdown and do a Fill Up that works the same. And in fact if I click in M10 with the Shift key held down, so I’ve got a rectangular selection, I could do a Fill Left and so on. So you can basically work in any direction on the sheet. And of course you can do Series in any of those directions as well. So if instead of Hello in this first cell here I’d put January I could use the Fill Handle to go left and then I could even use the Fill Handle to go up. So as you can see you can build some pretty sophisticated patterns.
Now the examples that I’ve used here have been relatively straightforward. And now I want to look at something a little bit more complex and we’re going to use a very important Series Dialogue for this example.
Want More? Get Started With a Free Excel 2016 Course! Click Here
So in cell H30 I’m going to type a date. I’m going to type a date of the 3rd of January 2015. Let’s suppose that I want to do a Series, a sequence of cells in row 30 staring on the 3rd of January 2015 but I want to have the step as 17 days, a very strange amount. Now to work out what those individual cell values will be, well 17+3, the next one would be the 20th of January and then another 17. That sounds like the 6th of February and so on. But it becomes a little bit complicated to keep going up in steps of 17 days. Well one way that you can do this is to use the Series Dialogue. And if I click on that Fill dropdown again there’s an option, Series and then it’s got a dot-dot-dot. Now whenever you get a dot-dot-dot on an option on one of these menus from the Ribbon it means you’re going to see a dialogue if you click on that. And in this case you have the Series Dialogue which lets you build very sophisticated series on your worksheets.
So, first of all do we want to go in rows or columns? Well I’m going to go in rows. I’m going to go across the row. What type of series is it going to be? Is it going to be a linear series? Is it going to be a growth series, something that’s getting bigger? Is it going to be a date series? Or is it some sort of AutoFill? Now what I’m going to do is to make this a date series and the date units I measure are going to be days. But the step value here is going to be 17 days. So I’m going to build a series that goes up 17 days at a time.
Now the step value basically says, “Where do I want this series to stop?” Now I’m going to randomly choose a date. I’m going to say I want it to stop on the 31st of October 2015 and there it is. Notice as I pointed out earlier in the course that where the columns aren’t wide enough to accommodate those dates I get the hashes. Let’s select all of those cells. Let’s AutoFit the column width so you can see them all properly. And there you are. Starting at the 3rd of January, 20th of January, 6th of February, 23rd of February, and so on.
Now it is true that pretty much any series that you can do with a Series Dialogue you could actually setup to do using the Fill Handle type approach. But it can get quite complicated and you’ll quite often find the Series Dialogue is a more convenient way of doing things. You can very simply, for example, just do a series of numbers. It doesn’t have to be dates or months or something. If I had a two in this cell and a five in this cell, let me now extend those with the Fill Handle, then I still get a series of numbers increasing three at a time. So I can do that without using the Series Dialogue but the Series Dialogue often makes it much more straightforward thing to setup.
Want More? Get Started With a Free Excel 2016 Course! Click Here
Another point to make here is that, let’s go back to that left hand one. Let’s put 100 in there. If you’re using Series Dialogue, let’s say that you’re doing in this case rows linear and your step value instead of being three as it was just now you made minus six and said stop when you get to minus-100 that’s fine as well. And you can of course count down dates as well.
So that’s it on Fill and Series Fill. In the next section we’re going to take a look at Flash Fill. So please join me for that.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!