Working with the Excel Fill Handle

One of the cool things about working with Excel is the ability to click on the Fill Handle in the lower right corner of a cell, and then drag it to auto-fill values based on what’s in the original cell. However, it can be frustrating to figure out whether it’s going to copy the value or auto-fill based on the next value in the sequence. Here’s a brief overview on how you can get it to copy or fill based on what you need, regardless of what Excel wants to do for you automagically…

In this example, I have the year value in cell A1. If I left-click on the little dot in the lower right corner of the cell (called the Fill Handle) and drag the cursor down the column, it will copy the value into all the cells (as seen by the circled value at the bottom of the column):

The default for dragging down a single number value is to copy that value. But if I wanted it to auto-fill (increment by one) instead, I could click on the properties box that shows up when I stop dragging. From there, I can choose to fill based on a number series. When I select that option, the column values will change to show incrementing year values:

Now… if I have a text value in the field that has a number at the start, doing the left-click drag will default the action to increment the number value by one (as seen in the circled value at the bottom of the column):

Using the Fill Handle properties box after the values have been filled in, I could select Copy Cells instead, and everything would change to be a copy of cell A1:

While you’re dragging the Fill Handle down the column, you can also press the CTRL key to switch back and forth between Copy and Auto-Fill, too.

One other trick… instead of left-clicking to drag the Fill Handle, you can right-click and drag the handle. When you release the mouse button, you will be given a menu for either copying or filling the series of values:

Thanks to https://www.excelforum.com/excel-general/357473-set-default-in-auto-fill-options-i-always-want-to-copy-cell-som.html for these excellent tips on working with the Fill Handle in Excel.