Join Curt Frye for an in-depth discussion in this video Entering a data series using the fill handle, part of Excel 2013 Power Shortcuts.
One of the most common, and also one of the most boring, tasks that you'll do in Microsoft Excel is entering sequences of values. For example, you might need to enter a series of order numbers or a series of dates. They could be in sequence. They can be copies. Regardless, you need to get the data in. It takes time if you don't know the proper shortcuts, but I'll show you those in this movie. I am going to work in the FillSeries workbook, which you can find in the Chapter 1 folder of your Exercise Files archive.
See here I have data that I'm going to use to build a series. I have just a basic sequence of numbers-- currently represented by the number 1-- some sales projections, and also dates. If you want to extend a series of numbers, you can do so by grabbing the fill handle, which is at the bottom right- corner of the active cell or the active cell range with more than one cell selected. So for example, at the bottom-right corner of cell A2, you can see the fill handle next to the number 1.
When I position the mouse pointer over the fill handle, the mouse pointer changes into a black cross, and then, if I hold down the left mouse button and drag, then Excel extends the series. And in this case it repeats the value, the number 1. So if were to drag down the cover cell A5 and release the left mouse button, then I get the number 1 in each of those cells. And I'll press Ctrl+Z to undo that action. Now I'll show you what happens when you hold down the Ctrl key when you drag the fill handle. It changes the default behavior.
So before, because I only had a single number, dragging the fill handle repeated the number. If I move the mouse pointer over the fill handle, so now it's a black cross, and hold down the Ctrl key, and you can see that the black cross changed, so there's a little black cross to the top- right of it, almost like an exponent. Now if I left click and drag down, you see that the data series extends: 2, 3, 4, and so on. So if you want to repeat a value, you can left-click and drag--and again, this for a single number. And if you want to change this behavior to extend the series, then hold down the Ctrl key and drag.
You should experiment to see how the fill handle operates and also how holding down the Ctrl key changes it for different selections of data. You will also notice that after you're done dragging the fill handle, the Auto Fill Options button appears, and if you click it, you get a list of options that you can apply to the series that you just created. The option that you just implemented, in this case FillSeries, is highlighted, but you can also select Copy Cells, Fill Formatting Only, Fill Without Formatting, and Flash Fill, which is new in Excel 2013.
You can also use the fill handle to extend a data series doing linear projection for forecasting. So for example, let's say that you have a series of data that gives you annual sales for your company and those were in cells C2 through C5. Say our previous sales were 150,000, 250, 450, and 675,000. If the trend continues, what would next year's sales be? Well, you can find that out by selecting all the cells in the data series, then dragging the fill handle--again, moving the mouse pointer, so it's a black cross and dragging down--and you'll see that if the trend holds--in other words, the growth pattern continues--then the next year's sales will be 825. Drag it again.
For the next year it's 1,002,500 and so on. So you can see that it is an easy way to do a little bit of forecasting by dragging the fill handle. One last thing I'll show you is how to extend a date series. So in cell E2 I have August 2, 2013. If I grab the fill handle and drag down, you'll to see that it goes up by a day each time I move down the cell: August 3, August 4, August 5, and so on. The Auto Fill Options button also appears and when I click it for a date, then I get a lot of different options.
I have the other options I had before-- Copy Cells, Fill Series, Fill Formatting, and so on--but I can also fill days, fill weekdays, fill months, and fill years. So for example, if I click Fill Years, then I get the same date, August 2, for 2013, 14, 15, and 2016. So you can see the fill handle is an extremely versatile tool. If you learn how to work with it well, you'll save yourself a lot of time.
- Entering a data series using the fill handle
- Creating hyperlinks
- Setting a print area
- Applying a table style
- Creating substitute data sets using scenarios
- Wrapping and shrinking text
- Creating a data entry form
- Removing duplicate values
- Inserting the current date or time
- Generating a list of unique random numbers
- Calculating running totals
- Analyzing a formula for errors
- Summarizing data with charts and PivotTables