Software

Office challenge: How would you inhibit formats when using the fill handle?

In this week's Office Challenge, test your Excel skills with an simple fill handle trick for inhibiting formats.

Do your users sometimes suffer with hitchhiking formats? Take a look at the following sheet. When you use the fill handle to create a series, Word also copies the top border. That may or may not be what you want. If not, what quick fix would you share with users? (Your clue's in the sheet!)

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.

10 comments
scifisdgo
scifisdgo

Unfortunately, choosing the "fill without formatting" option will remove any individual formatting that you may wish to have, e.g. bold text, colors, etc. Another way would be to format the rows correctly from the beginning. The current scenario occurs because row 2 is set with a top border. If this is removed and row 1 is set as a bottom border their is no issue.

iDvl
iDvl

Pasting unformatted text in Word and Excel from Paste > Paste special and selecting the unformated option or just the option text. My preferred way is the tool PureText from http://www.stevemiller.net/puretext/. It is a blessing. It past tables from a web-page into the corresponding cells in Excel without clutter.

hnsk
hnsk

While dragging the Fill handle, use the right button on the mouse and not the left one. When you release the button, a context menu will appear and you can choose the correct option. This is no cheating!

dogknees
dogknees

Wouldn't removing them after the fact be cheating? After all the challenge is to fill without formatting, not fill then remove formatting.

adundon
adundon

You will also have the option to choose "Fill Without Formatting" when you let go of the mouse button, if you use the right mouse button to auto-fill,

eljensen
eljensen

After you fill the cells using the handle, click on the small box (shown in the screenshot by "2016"). Select the option to "Fill Without Formatting," and the borders in the filled cells will disappear.

ssharkins
ssharkins

That's partially correct -- when formatting the top row, I used the Border control and chose Thick Box Border to enclose the three cells all at once. I didn't use the top border for row 2. I think a lot of people would default to that choice rather than setting the borders individually as you suggest. But you're right -- if you choose the bottom border for the first row of headers, Excel doesn't copy the format.

JaneHawkins
JaneHawkins

Oh yes there is! AutoFill will take ALL formatting. It doesn't matter whether the border is at the top of row 2 or the bottom of row 1.

Fastflier
Fastflier

I've used Pure Text's hotkey paste function many times a day for years, in all applications. It's a fantastic time saver, especially for web-based work.