Office solution: Here is how you inhibit formats when using the fill handle

Read the solution to last week's Office challenge: how would you inhibit formats when using the fill handle?

Last week, we asked about the fill handle. Specifically, can you inhibit formats when using the fill handle to copy or create a series of values? Eljensen responded with one of the correct responses: use the Fill Without Formatting option. The fill handle is full of options, and many users ignore them because they just don't know they exist.

When you use the fill handle, Excel displays a small icon that offers quick access to several options, as you can see below. In this case, you'd choose Fill Without Formatting from this icon's dropdown list.

Adundon and Hnsk both mentioned the right mouse button, which is more efficient, but fewer users know about it. If you use the right mouse button to drag the fill handle, Excel displays a fuller list of options before copying the values.

Scifisdgo reminded us that a little planning can help you avoid the problem altogether. In the example, I used the Thick Box Border to apply borders all at once, rather than choosing individual borders. I did so because most people use this option rather than applying individual borders. In the challenge, I did refer to it as the top border, but not with any willful intent to misguide you! It's the kind of conversation you'll actually have with users! They don't always describe things the way you expect to hear them.

The fill handle does copy that bottom border when using the fill handle. However, if you apply a bottom border individually, the fill handle will not copy it. But wait! Excel's 2003's fill handle doesn't copy either format - the box or the bottom border. It looks like this challenge is still open - can you solve this final version mystery?

Thanks for another great challenge!

About Susan Harkins

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.

Editor's Picks