How To Save Time In Excel By Using The Fill Handle

logo   How To Save Time In Excel By Using The Fill Handle I want to help you get a handle on the handle. Excel has, for quite a few versions now, had a number of shortcuts for using the mouse rather than the keyboard.

I want to clarify how some of them work, so you can make the most of the tools at your disposal.

Cell handles

First off, let me tell you about the various cell handles that Excel makes use of. You use a cell handle by hovering the mouse on various parts of the screen. Fire up Excel and try this for yourself. Nothing beats a little practice.

I’m going to show you this in Excel 2007, but only the most advanced options are version-specific. You’ll be fine. One thing though. If you don’t use the default Windows mouse cursors you might see different sorts of shapes. Change it back to the defaults until you get the hang of this.

Select

select   How To Save Time In Excel By Using The Fill Handle

You already use this one, if you use a mouse at all. Hold the mouse anywhere within the confines of a cell in a spreadsheet, and the mouse cursor becomes a small white cross. Hold the left button down and drag the mouse to select all the cells you slide over, in a rectangular shape. That’s it.

Move

move   How To Save Time In Excel By Using The Fill Handle

If you hold the mouse over the edge of a cell, then it behaves differently. It’s quite common for people to do this by mistake, but it’s easy to avoid if you take a look at the cursor. It should be a thin black cross with arrow-heads on it.

The move handle does exactly what you would think. It moves the current cell, or the current block of cells if you have more than one selected. It’s smart though. If the cell you are moving is referenced by formulae in other cells, each formula is automatically adjusted so everything still works.

One safety tip. Make sure the cursor isn’t over the small black square in the bottom right corner of the cell. You’ll be doing something completely different. I’ll explain that soon.

Copy

copy   How To Save Time In Excel By Using The Fill Handle

To copy cells instead of moving them, put the mouse in the same place, and hold down the Control (Ctrl) key. The cursor changes back to a white arrow, with a small black cross beside it.

The behaviour is a little different now. If you drag the mouse to another location, the cell or cells you have selected will be copied and pasted there. If the cell contains a formula, it will change its references just the same as it would if you have used copy and paste. The contents of the cell you are copying from will not be changed.

Fill

Now this is the interesting one – the Excel fill handle.

fill   How To Save Time In Excel By Using The Fill Handle

If you hold the mouse over the bottom right corner of the cell or cells selected, the cursor will change to a simple black cross. That’s the Excel fill handle, and it does some cool stuff.

First off, if you have a formula in the cell, and you want to copy it to some adjacent cells, you can do that.

Hold the left button”¦

fill1   How To Save Time In Excel By Using The Fill Handle

“¦and drag the mouse down (or to the right).

fill2   How To Save Time In Excel By Using The Fill Handle

Release the mouse.

What happens in this case is the same thing as you would have achieved with the copy example further up. However, if the cells don’t contain formulae then the behaviour is different.

If the cells involved have a discernable pattern, then Excel will attempt to extend that pattern as best it can.

So imagine the above example again, but pretend, just for fun, that it contains 500 rows, instead of the four shown. You’re suddenly required to apply numeric codes to the items”¦

extend1   How To Save Time In Excel By Using The Fill Handle

Create the first two, or however many are needed to establish a pattern. Hover over the fill handle”¦

extend2   How To Save Time In Excel By Using The Fill Handle

“¦ and drag the cursor down over the next two (or 500) cells.

extend3   How To Save Time In Excel By Using The Fill Handle

Note the indicator to the right of what the final number will be. Release the mouse.

extend4   How To Save Time In Excel By Using The Fill Handle

That’s it. The series of numbers is extended down over the other cells. Note that all the cells contain the actual values, not a formula.

You can use this for much more complex operations, none of which I have room for in this post, including complex lists, custom lists, and copying a formatting pattern along with the value pattern.

So tell me, do you use the handles? Did you already know what they do? Have you ever used the Excel fill handle by mistake? I certainly have. What else could I cover in Excel that might be useful?

Let me know in the comments.

The comments were closed because the article is more than 180 days old.

If you have any questions related to what's mentioned in the article or need help with any computer issue, ask it on MakeUseOf Answers—We and our community will be more than happy to help.

13 Comments -

trey

you can also double-click the handle and it will fill all the way down to the last cell with contents to the left.

this would be an alternative to dragging the handle for thousands of rows.

Jim Henderson

Absolutely. And the only problem with explaining that would be starting down another path I didn’t have room for in the post. Many thanks.

Jamie

Is there a keyboard shortcut for applying ‘fill’ to a range of cells?

Jim Henderson

Hi Jamie. Good question.

It would be difficult to make that work, though I’m sure you could set up a macro to do that. The complication is that it’s a two step process. Select the cell or cells on which the fill is based, and then select the cells to fill.

So in short, no.

Prateek Jain

It would be difficult to make that work, though I’m sure you could set up a macro to do that. The complication is that it’s a two step process. Select the cell or cells on which the fill is based, and then select the cells to fill.

Kate

Fantastic post, Jim! Thanks for the detail, that’s incredibly useful! Check out the Excel Page on Facebook for lots of tips, tricks and resources, and feel free to contribute to the conversation on the Wall as well! http://www.facebook.com/pages/Microsoft-Excel/89421301366#/pages/Microsoft-Excel/89421301366?v=wall&viewas=7300773

Cheers,
Kate
MSFT Office Outreach Team

Tony

I have used Fill to extend a column of dates, for example. The default increment is 1, but if you highlight the cells with the desired increment in them, Fill will use that instead. Handy for weekly dates. It will also carry on days of the week, in full or abbreviated, provided a week’s worth is in the highlighted range you choose to extend. Fill is a great function!

kathy

OK here is my problem… the Fill Handle does not work properly when using it across more than one file. So say I want to have a MASTER LIST of students names in one file… then to run reports with the corresponding names in another file.

When I use the fill handle.. it doesnt modify the formula but COPIES it over and over!! How can I change this without having to fill each formula individually?

kathy

NVM ~ I removed the $ from the formula and it filled correctly.

kathy