One of the beautiful things about Excel is its ability to handle plenty of complex formulas. In this lesson, we learn how to make using those formulas a little bit easier by using the fill handle to avoid having to type them over and over again.
So Many Formulas
Let's say that you've stayed up all night writing a complex set of formulas in Excel to describe the development of a set of data across various parameters. In your model of this company's business for a particular item, you've ran literally every example that you think that it is practical to include. Finally, after many cups of coffee, hours without sleep, and wondering about the value of leaving the corporate world to go sail around the Caribbean (hey, we've all been there!), you finally have completed the forecast. There's just one little thing. Your boss just walked in and apparently forgot to mention that those formulas need to be written for the entire line of products sold by this company. Do you break down? Do you throw a stapler at the wall? Fortunately, you get to look like the employee of the month because you're one mouse movement away from fulfilling that request. In this lesson, we'll learn how to use the fill handle to copy all of those formulas for new numbers.
Copying Using the Fill Handle
Perhaps copying isn't the word I should use, because if you were to use a keyboard shortcut to apply these formulas to a new line of formulas, you'd have to go through and individually change each of the values to correspond with the cells that your data should be drawn from. That is a pain. Instead, by using the fill handle and a little logic, it will largely take care of itself.
But wait, where's the fill handle? If you highlight a cell, you'll notice a little square on the lower right hand corner. If you drag it with your mouse, it will either select rows moving horizontally or vertically away from the original cell. That square is the fill handle. When you drag the handle, it will copy the formulas for cells in whatever relative direction you move.
Keeping the Formulas Straight
So wait, what do I mean by relative direction? I mean, that if your original data to be manipulated by a formula is in one cell, and you use the fill handle on a different cell, it will draw data from the adjacent cell. Still confused?
Let's try an example. Let's say that you had a sheet that added the contents of A1 and B1 and put them in C1. The thing is that you've got a lot of data to be added in A2-A99, and B2-B99, but you still want to use the formula of A1+B1. Now you could very well go ahead and write =A2+B2, =A3+B3, and so on until you've copied the formula the whole way through. However, by just writing the original formula in C1 and dragging down on the fill handle, you copy that formula but update it for the relevant terms. In other words, C58 will have the sum of A58 and B58. Note that you have to work logically in order for all this to work. If you place formulas with no regard to sequence, these tricks will not work. In other words, don't use your sheet like scratch paper, but instead keep everything neat and in order.
The fill handle has uses beyond formulas, however. Many of these relate to Excel's internal formulas, which are built into the logic of the program. Most notably, you could automatically number a set of data by inserting the first few numbers and then using the fill handle to fill in the rest. It doesn't just have to be 1, 2, 3. Excel also recognizes arithmetic sequences, so you can count by twos, threes, or even sevens. And the fill handle doesn't only do numbers; it can also do dates. So if you want to create a line for every day of the year, you can type 1/1, 1/2, 1/3, and then use the fill handle down to 365. The dates will fill in automatically. Finally, speaking of dates, if you type Monday, Tuesday, Wednesday, then use the fill handle, the days of the week will repeat themselves. The same will happen for months as well. In short, the fill handle can help save you a lot of time on repetitive tasks.
In this lesson, we learned how to use the fill handle of Excel to quickly repeat repetitive information. Using the example of having to repeat a series of formulas for a work assignment, we saw that the fill handle can use the same information to draw from cells in the same relative position. We also used the fill handle to prevent the typing of repetitive data, ranging from sequences of numbers to months and days of the week.