Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
In this worksheet CopyFormulas, in the workbook 03-Creating Formulas and Functions, we've got a formula in cell B4 and we need to have that same kind of formula in cell C4, D4, all the way over into G4. Now, if we talk about copying a formula, you might say, "well, we don't want this exact formula to be copied into column C, because we would get the same answer, we get a 20". In column C we want to subtract these two cells. Many, many times when you've written a formula in Excel, you need to copy it across a row into adjacent cells or in some cases down a column.
What we would like to see here of course is the answer 30 and the answer 50 over here and so on. We need to copy a formula. Fortunately, the way that Excel copies formulas is that it really copies the relationship and that's an unusual way of saying it, but in this formula right here, a different way of phrasing it is, this formula subtracts the two cells above it--top cell minus the cell below it. Do we want to do same thing here? Of course we do, this cell minus this one. When we copy a formula, we're gong to be repeating the same kind of relationship.
Now, the are various methods for copying data including formulas, but surely the best way to copy a formula into adjacent cells is to use the so-called Fill Handle in the lower right-hand corner. This cell that contains a formula that subtracts the two cells above it can be copied rightward, simply by clicking and dragging this fill handle to the right. As we let go, you certainly see correct answers and of course we want to check these out just to make sure in C4, what does our formula say, double-clicking, I can certainly see that's doing the right thing.
How about column E over here? Double click, that's exactly what we want it to say and so on, every one of these. Copying formulas really means copying the relationships between cells. In cell H2, we've got a total using a function, the SUM function. It tabulates the six cells to its left and we want to do the same thing in cell H2 for the "Overhead Expenses" and in cell H4 for the "Profits". Here too, we can use the Fill Handle, drag the formula--the function in H2-- downward into these two cells to get those answers.
Our Average is a calculation in cell I2 and double-clicking and looking at it we see that it's dividing the cell to its left by six and we want to do the same thing in the two cells below this. Here too, we'll drag from the lower right-hand corner. There are many, many situations in Excel where you write a single formula and then copy it into adjacent cells by using this Fill technique. We've seen it initially with a row and then two examples used with a column.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 64627 Viewers
80 Video lessons · 124266 Viewers
52 Video lessons · 60223 Viewers
59 Video lessons · 46053 Viewers