How to Use The Fill Handle in Excel

One of the key features in Microsoft Excel is its ability to fill data based on existing values.  Excel is smart enough to extrapolate and auto-fill values based on your existing ones.

Click on the black box in the corner of any cell and drag.  Excel will fill the cells automatically.  The fill handler works both horizontally and vertically.

Here are some tips on how to use the fill handle for values:

• Type in a month in either full form (e.g. January) or abbreviated form  (e.g. Jan) and drag and Excel will fill in the subsequent months.
• Type in a day of the week in either full name (e.g. Monday) or abbreviated form (e.g. Mon) and fill.  The filled cells will contain the correct days of the week in sequence.
• Type in any value into a cell (e.g. 1, 2, etc.) and when you fill in with the same value.
• Type in a sequence of values in cells, e.g. 1 in B2, 2 in B3, etc.  and select multiple cells and then drag the fill handler.  The cells will be filled in with the sequence.  This works just as well with intervals such as 2, 4, 6 etc. as well as 1,2,3.
• If you hold the control key when dragging the fill handle, you will see a plus (+) sign and the value selected will increment by 1 in each cell.
• If you right click on the fill handle, it pops up a dialogue where you can explicitly instruct excel how to fill the data.

The Fill Handle can also be used to fill formulas.  Here is an example:

The product in c2 is a formula =A2*b2 and the sum is a formula A2+b2.  Now let’s assume that you have a series of values and you want to have the same formula repeated:

If you select the cell with the existing formula you can use the fill handle to automatically fill down the right formula for the other rows.  It automatically figures out that as you move down rows it should adjust the formula so that A2 * b2 becomes A3 * B3, C4 * B4 and so on.

There is also another way to fill in the cells.  If you select the cell you want to copy and then select a region and paste, it will fill the values in the same way.  It again automatically adjusts the formula so that it makes sense based on the new row that it now occupies.  This works both horizontally and vertically.

One last tip on filling – there are cases where you will want the value in your formula to not change as it moves from row to row.  Take this example:

In this case, if you simply do a standard fill in Excel this is the result:

Why did this not work properly?  The reason is that Excel assumed that B1 * B3 should be B2 * B4 in the second row and B3 * B5 in the third row.  However, what we wanted is for the first number to stay constant and only the second row to change. 

The solution to fixing constants is to name them.  Named values in Excel do not change when filling.  To name a cell, click on the cell and then in the left box type in a name for the cell. 

Then when you create the formula, use the name instead of the cell address, e.g. instead of B1 * B3 we will now set the formula to InterestRate * B3.  By using a named cell, Excel assumes these values are constants in formulas and doesn’t adjust them automatically.