#244 5 Things You Should Know About the Excel Fill Handle

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.

Advertisements

3 thoughts on “#244 5 Things You Should Know About the Excel Fill Handle

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s