Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
If your worksheet contains a data series that you would like to extend into the future, you can select the cells containing the existing values and use the Fill Handle to extend the series. To extend the series using the Fill Handle, select the cells containing the data series you want to extend, grab the Fill Handle at the bottom right of the selection, and drag. So, I'll show you how to do that here with the data in cells B8 through B12. So we have the black selection outline, and here at the bottom right corner, we have what's called the Fill Handle.
If you move the mouse pointer over the top of it, it turns into a black cross and you can drag it down. And Excel extends the data series for you. I'll click Undo. I'll come back to this in a moment. I would like to note that you can go forward or backward. So for example, if the sales revenue cell were blank, I can select here and move up, and you'll see that Excel will extend the series backwards in time if you want. I do want to emphasis that this technique is only accurate when you have a data set with no missing values.
As compared to the Forecast function, which interpolates a y value for any x values that are skipped in the data set, the Fill Handle does not. So I'd like to show you the error in this data table, and that is that we have the years 2005 to 2008, but we skipped 2009. That means that dragging the Fill Handle just works with the existing data and it does not attempt to do any sort of calculation to figure out what the year 2009 would or should have been. I will show you over here, how the Forecast function handles the same data set.
So we have =FORECAST, x is the year 2011 which is in cell D13, the known_y's are in E8 through E12 and the known_x's are in D8 through D12. Close the parenthesis and there you have the value of 43,660. Now when you compare that with the Fill Handle value, 46,265, you see that they are two different values even though the inputs are exactly the same, and that is because the two functions, FORECAST on the right, the Fill Handle drag-select on the left, use different techniques.
So now let's see what happens if I change the years to 2009 and 2010 in both cells. I'll just do 2010 and 2009, and I'll delete the Fill Handle value there. So we have our forecast value of 46,265, and if I drag the selection over here, you will just see that we get the same value. And again, the difference is just in the way that the Fill Handle extension and the Forecast formula treat missing values. Extending a data series using the Fill Handle is a quick way to predict the future value, but you must be certain that you are working with an uninterrupted data set for the results to be accurate.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 64752 Viewers
80 Video lessons · 124339 Viewers
52 Video lessons · 60277 Viewers
59 Video lessons · 46104 Viewers