Fill handle without breaking formatting 3

[EcoWill]

Hi all - when you drag down the fill handle to auto-fill a formula down a column, it also drags out the formatting of the cells in those columns and you have to go back and re-do them which can be a pain. Is there a way to fill only NUMBERS and not cell formatting too?
Thanks mucho.

 

[SkipVought]

hi,

Try Copy -- Edit > Paste Special FORMULAS.

You do this by COPY a formula and then SELECT the paste range, prior to the Paste Special process.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xlhelp]

When you use [red]Fill Handle[/red] to copy and once you release the mouse button, there should be an "Auto Fill Options" box that should pop-up; with the following Options:

Copy Cells
Fill Series
Fill Formatting Only and
Fill Without Formatting

Choose the last option.

Canadian eh! Check out the new social forum Tek-Tips in Canada.
I thought I had retired...I was mistaken. I used to work for a large corporation; now I work for my wife.

 

[SkipVought]

xlhelp,

I see no such box in 2007???

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xlhelp]

I am using 2007 and I get it all the time.

If it doesn't show on your version, do the following:

Click the Microsoft Office Button, go to Excel Options; Advanced; under Cut, copy, and paste: click to select the Show Paste Options buttons check box.

Canadian eh! Check out the new social forum Tek-Tips in Canada.
I thought I had retired...I was mistaken. I used to work for a large corporation; now I work for my wife.

 

[SkipVought]

xlhelp,

Great TIP! ==> *

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[EcoWill]

Hey folks- of course Skip's 'paste formula' option is the way to go, and I knew that. Forgot. Duh. I don't get the option box that xlhelp suggested either, but will try that when I get home from my vacation or maybe sooner when I get to where I am going. (The Rockies). Thanks again!

 

[GlennUK]

You also get the option box that xlhelp suggested when you pull the fill handle using the right-mouse button ( without having to change the options ).

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[N1GHTEYES]

Very cute tip Glenn. Have a star! I can't believe I was not aware of that one.

Tony

 

[xlhelp]

One from me too, Glenn


I don't need Google, my family knows everything

 

[GlennUK]

thanks for the stars

I'm glad that my know-how is useful for others.

Cheers, Glenn.

Beauty is in the eye of the beerholder.