Rather than write a formula and copy it multiple times, use the fill handle to copy a formula into adjacent cells down a column or across a row.
- [Instructor] In Excel, it's not uncommon that after you've written a formula, you need to copy it either rightward into adjacent cells or downward into adjacent cells. And as we're about to see, it's a huge time saver to be able to do this. In this particular worksheet, called CopyFormulas, and we're in the Workbook 03, Creating Formulas and Functions. In cell B4 is a formula. I wanted to double-click it so we can see it better. It subtracts the two cells above it. When you copy a formula, you copy that kind of a relationship. If we were to copy and have the exact same formula over in C4 that we have in B4, it's not gonna make any sense.
We're gonna get the answer 20 and we want the answer, of course, to be the subtraction of these two cells. When we copy a formula, we copy the relationship. And we do it with the fill handle in the lower right-hand corner. So, pointing to the fill handle, notice how it changes its shape to be a thin plus. Just gonna drag this over into Column G. And as I release the left mouse button, there it is. And one-by-one, you can see that the answers are correct. If you have any doubt, you can click here, look at the formula, or double-click to see it larger, and of course, the same kind of thing is happening.
We did not copy the formula exactly. We wouldn't have wanted to do that anyway. And so, copying the formula here makes this a lot simpler and easier. And imagine, of course, if we were doing 12 months, how much time we would save that way as well, too. And there are many, many times when we're copying down a column and sometimes, hundreds, thousands of rows, even. In cell H2 is the use of the sum function. Once again, I'll double-click it to make it larger. We see what that's doing. And what is that doing? It's adding up the six cells to its immediate left.
So here, too, we want the same thing to happen in cell H3 as well as in cell H4. We go to cell H2 and drag that fill handle down into the next two cells. And just to check it out, what's this formula say? You see it in the formula bar or you double-click to see it larger. Same general idea. And the same thing we need to do with Average. What is the average formula in cell I2 doing right here? It's taking the cell to its left and dividing it by six. So here, too, using that fill handle.
We'll drag downward and we've got the same kinds of formulas here. And ultimately, we will reformat that, too. So copying formulas, either across a row, or even more commonly, down a column, is something that will save us an enormous amount of time as we work with Excel. It's one of the basic features of working with formulas in Excel that you use all the time.
- Navigating Excel tabs and menus
- Entering data
- Creating formulas and functions
- Formatting rows, columns, cells, and data
- Working with alignment and text wrap
- Adjusting rows and columns
- Finding and replacing data
- Printing and sharing worksheets
- Creating charts and PivotTables
- Inserting and deleting sheets
- Using power functions such as IF and VLOOKUP
- Password-protecting worksheets and workbooks
- Sorting data
- Analyzing data with Goal Seek and Solver
- Creating and running macros