3. Fill Handle

What is the fill handle?

The fill handle is a tool that helps you quickly copy or extend data and formulas across multiple cells. You can use it to drag and fill adjacent cells with a pattern, such as numbers, dates, or formuals. It’s that little green square in the lower right corner of your cell selection:

Let’s look at a quick example. Let’s say we have a spreadsheet where we need a row for each month of the year, starting with January. We could type January into cell A1 and then type February into cell A2 and continue typing for all 12 months. Or we could use the fill handle to do this work for us:

The fill handle can fill lots of things automatically:

• Days of the week
• Months of the year
• Number series (e.g., 1 to 100)
• Date series (e.g. January 1, 2025 through May 8, 2025)

These are all really handy uses, but the fill handle really shines in filling formulas.

Filling formulas with the fill handle

We’ve already seen that formulas are a powerful tool in Excel. When combined with the fill tool, they become incredible timesavers. In the example below, we’ve got a sheet of simple campaign results with columns for Total Revenue and Total Cost. We are using the formula Total Revenue - Total Cost to calculate Net Revenue for each campaign. But we’ve got 9 campaigns and we don’t want to have to write out 9 formulas. Fill handle to the rescue:

Excel automatically identified that our formula in cell D2, =B2-C2, contained cell references that fit a pattern. As we filled down, it followed that pattern for each new row creating formulas like =B3-C3, =B4-C4, etc.

Absolute references

So far, we have talked only about relative cell references where we refer to a single cell and allow the fill handle to adjust that cell reference as we move down rows or across columns. However, sometimes we have the need to fill a formula that consistently refers to a single cell. For this, we will need to make use of absolute cell references in which the cell being referenced doesn’t change as we fill.

As an example, let’s say we have a list of campaigns and their revenue, and we want to know what percentage of the total each campaign makes up:

In this example, the total of all campaign revenue is in cell B11 so we need to divide each campaign’s total revenue by the value in cell B11 to get the % of total revenue. We have entered our formula, =B2/B11 into cell C2. But if we fill down, look what happens:

Excel increments our B2 reference correctly with each row we move down, but it also increments our B11 reference. So the formula in cell C3 becomes =B3/B12. Since there’s no data in B12, we get a division by 0 error. To correct this, we need to use an absolute reference by locking the reference to B11 so that Excel doesn’t increment it. We do this using the $ operator. Our new formula is =B2/B$11, with the dollar sign before 11 telling Excel “hey, when we fill down, don’t change this row number, leave it as 11.” Now, our fill works:

You can use the $ operator to lock either row references as we did in the example above, or column references (e.g., $B1 will lock the reference to colum B as you fill but allow the row number to change). You can even lock both references if you always want to refer to one specific cell (e.g., $B$1 will always refer to cell B1 no matter which way you fill).

Key takeaways

• The fill handle helps you repeat patterns down rows, across columns, or in ranges
• The fill handle has built-in functionality (days of the week, months of the year)
• The fill handle can also fill patterns of cell references in formulas like sum() or average()
• You can lock references to columns, rows, or specific cells using the $ operator so that Excel does not increment the column/row reference when filling