Join Dennis Taylor for an in-depth discussion in this video Copying a formula into adjacent cells, part of Excel Essential Training (Office 365).
- [Instructor] Many times in Excel, when you see a formula you also want a similar formula in adjacent cells, either across a row or down a column. And we're looking at a worksheet here called Copy Formulas. It's in the workbook 03 Creating Formulas and Functions. Here's a form in cell B four. I click on it. You can see it in the formula bar or I can double click it here, and you can see it larger. We want that same kind of a formula here. We certainly don't want the exact formula because we'd get the answer 20. When you copy a formula in Excel, the important concept is, you're copying a formula relatively.
Now, I'm going to double click this again, so we can see it larger. This formula refers to content in column B. If we drag it one column into the right, the column references will get adjusted to be column C, and then going to the right, D,E,F,G. When you copy formulas, they get adjusted relatively, otherwise, you'd simply be repeating the exact same formula. And of course the payoff is here, we don't want to type a formula here, then here, then here. What if we were doing this for 12 months? That'd be kind of ridiculous, really time consuming.
And the way we copy a formula is simply by dragging and entering from that lower right hand corner. It's called the fill handle. So as I drag to the right from the fill handle into June, right away, you can probably see those value are correct. What's this one right here? You can see it in the formula bar or I'll double click. You can see it larger. So one by one, we have correct formulas, and if we had 12 months, we'd be all the more impressive. And similarly, in column H here, there's a formula. What is it doing? It's adding up the six cells to its left.
Do we want to do that here? Yes, we do. And here? Yes we do. So just as in the examples over here, we were copying, you could say, the relationship. What is this formula over here? What did it do? It subtracts the two cells above it. We copy it to the right. What does this one do? Same thing, same thing, same thing, and so on. Same general idea here, but now I'm moving down a column. So I can simply drag that lower right hand corner, and we have correct answers there. What does this one say? You can see it in the formula bar, or as I double click, see it better.
And similarly, in column I, the function here for average is the cell to its left divided by six. We want to do that two more times. We'll drag that lower right hand corner downward. So many, many times when you see a formula, you're likely to want to copy it, as we've seen here, down a column, or across a row, and this technique of copying a formula by dragging the fill handle is absolutely essential when you work with formulas and lists the way we're seeing them here. We started off seeing only three formulas in this worksheet, and we created the other formulas simply by dragging the fill handle.
In this list of numbers that we're seeing here on the screen, just at a glance, it looks like a bunch of numbers. These are numbers, all the rest are formulas. So as you work with Excel, they'll be many times when you need to copy formulas like we've seen in the examples in this movie.
- Working with the Excel interface
- Entering data
- Creating formulas and functions
- Formatting your data
- Adjusting rows and columns
- Finding and replacing data
- Inserting and deleting sheets
- Sorting and filtering data
- Creating charts and PivotTables
- Printing and sharing worksheets
- Protecting worksheets and workbooks
Skill Level Beginner
Q: This course was updated on 1/7/2019. What changed?
A: A new video was added that covers working with Excel Ideas.