Once one formula works correctly, you rarely want to type it out again for every row below it. By the end of this page you will know how to copy a formula down a whole column in seconds, using the small square called the fill handle.
Imagine you have written a formula in cell C2 that adds A2 and B2. If you have fifty more rows below it that need the same calculation, retyping the formula fifty times would be slow and error-prone. Instead, Excel lets you copy a formula down instantly, adjusting the cell references as it goes so each row calculates its own figures correctly.
- The working formula
- Drag the fill handle down to here
Click the cell containing your working formula, then hover your mouse right over that bottom right corner until the pointer changes to a thin black cross. Click and drag downward over as many rows as you need, then release. Excel fills every row with its own version of the formula. If your list already has data running all the way down the neighbouring column, you can skip dragging altogether: just double-click the fill handle, and Excel automatically fills down to match the length of that data.
Try it yourself
In A2:A6 type five numbers, and in B2:B6 type five more numbers alongside them.
This creates two columns of data for a formula to combine, and gives the fill handle something to match its length to.
In C2, type =A2+B2 and press Enter.
This creates the first working formula, which will become the pattern to copy down.
Click C2 again, hover over its bottom right corner until you see a thin black cross, then drag down to C6.
This copies the formula down, and Excel automatically adjusts A2 and B2 to A3 and B3, A4 and B4, and so on.
Click C2 once more and this time double-click the fill handle instead of dragging.
This shows the shortcut: Excel senses the data in columns A and B and fills C down to match it automatically.
Click on any of the filled cells, such as C4, and look at the formula bar.
This lets you see for yourself that the formula reads =A4+B4, not =A2+B2, proving the reference has shifted to match its own row.
Words people use
Copying formulas with the fill handle is one of the biggest everyday time-savers in Excel. Once you trust that the references adjust correctly on their own, you can build large calculations from a single well-checked formula.
Check yourself
If you can say yes to each of these, you are ready for the next lesson.
- I can find and use the fill handle at the corner of a selected cell.
- I can drag a formula down to copy it into several rows.
- I can double-click the fill handle to fill down automatically to match nearby data.
- I understand why the cell references change as a formula is copied down.