The fill handle is one of my all-time favorite and most used Excel functions.The fill handle is quite powerful, capable of filling thousands of rows in the blink of any eye – you just need to know a few secrets to maximize its value.
For those of you asking “What in the heck is a fill handle?”, it’s the little black square in the bottom-right corner of the selected cell or range.
Click & Drag
If you know anything about the fill handle, it’s probably that by simply clicking and dragging it, you can copy the contents of that cell across the row or column for as far as you drag the mouse.
There are two special cases when you are working with a series of data: numbers and special series like months, days of the week, etc.
Numbers: For a repeating series of numbers you need to have at least two of the series values selected before you use the fill handle. If you only have one the fill handle will simply copy that value and not generate a series…..because it doesn’t know what the increment should be. For example, do you want 0, 1, 2, 3… or 0,10,20,20….etc
Special Series: Excel automatically recognizes a few ‘special’ series like months and days of the week. These series will repeat when they reach the end of the series. (i.e. January will follow December, etc). You can even create your very own custom fill series, which we’ve talked about before.
These alone can save you a fair amount of time, but keep reading for even more fill handle tricks you may not know.
Double-Click
My personal favorite, double-clicking the fill handle can instantly copy your cell contents to thousands of rows. This only works when there is data in the row or column next to the one you are filling. Excel will fill the contents all the way to the end the adjacent data.
Here’s how the double-click works:
» Excel uses the values in the column to the left to determine how far to copy. Excel will stop when it encounters a blank cell.
» If there are no values to the left, Excel will use the values in the column to the right.
{CTRL} + Click & Drag
If you don’t want a series hold the CTRL key while you click and drag to make a copy instead.
{SHIFT} + Click & Drag
If you’d prefer to insert blank cells, you can hold the {SHIFT} key while you drag.
Right-Click & Drag
Right-clicking and dragging will give you a nice context menu with several handy options. The options available depend on the data you are working with. One great option for this one is when working with dates you can choose how to fill the series; days, weekdays, months and years.
Awesome article, Mike! In fact, I’d like to share it with my audience. How did you create the animated images? There so cool!
LikeLike
Thanks John. They are animated gifs. I used Camtasia to make these.
LikeLike