Software

A quick fill handle trick for Microsoft Excel

If you think you know every fill handle trick in the book, there just might be one more you don't know about. Susan Harkins explains.
Excel's fill handle is a big favorite with most users. You enter a single value or formula and the fill handle does all the work. Once you get use to the tool, the tool handle becomes invaluable. As much as users appreciate it, there's still a trick or two not everybody knows. For example, you might not know that double-clicking the handle will automatically complete the task if you're filling a column or row that's based on an adjacent column or row. Fortunately, the actual trick is simpler than its description! Let's work through a quick example so you can see just how easy this trick is.

Column A in the following sheet contains a list of consecutive values. I've also entered the following formula in B1, which returns the letter A:

=CHAR(A1)

At this point, I could drag the fill handle down to create an alphabetic list (based on the values in column A), but I don't have to drag it at all. A simple double-click to the fill handle will automatically fill in the rest of the series. Excel knows where to stop, so don't worry about that - just double-click! It's an easy trick, but one still not well known.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

9 comments
KeithAu001
KeithAu001

I just tried this "trick" with different numbers lik 1 to 9, then 10 to 19 and all I got were odd characters therefore, the numeric value given in the left column wont always return an A, B C D E etc. May I suggest you take a closer look at something like this before you post it

Roger Spencelayh
Roger Spencelayh

When dragging, use the Right mouse button. Gives a list similar to Smart Tag options.

agency
agency

Yes, I use that one a lot. I also like using CTRL with fill handle to copy a repeating block of values (this also prevents Excel completing using a series when you want to copy instead e.g. A,B,C,A,B,C)

rasilon
rasilon

Very cool tip. This will save a lot of time. Hank Arnold (MVP)

pksun24
pksun24

Not sure why you started the series with 65, but I see nothing else works. What is CHAR in the formula in B1 based on? I'm assuming some type of character code, but not sure. Thanks

Helen Phelan
Helen Phelan

Press the control key while you're dragging to switch from copy to series.

Mark W. Kaelin
Mark W. Kaelin

Do you have a fill handle trick that you'd like to share?

skrks2
skrks2

Love this also! I kept loosing my formatting when I used the fill handle before. Now that doesn't happen. Thanks for posting this.