copy a formula from a cell with one format to another cell the results display the destination format

[boxarox]

Column B and C are formatted as percentage: Column D is formatted as currency.

In cell B10, I autosum B1 thru B9.

Ctlr +c to D10 then Ctlr+V.

I want the results in currency ( the format in the destination cell), not percentage ( the originating cell)

Is there a setting I need to change to get the copied formula to always display results in the destination cells format?

I've tried paste special> formulas, but that doesn't seem to work either

I am using 2010

 

[Pup Denab]

Hi boxarox
Welcome to the forum, try something like this, it will make the format US dollars, however you can change to make any currency you wish

=TEXT(SUM(B1:B8),"[$$-409]#,##0.00")

 

[boxarox]

Pup;
Thank you for the response,but that approach actually seems a bit more cumbersome than just highlighting the cell and clicking currency on the ribbon. What I was hoping for was some hidden setting somewhere, that I could tweek to change the overall manner that the worksheet operates in. Sort of like the"set precision as displayed"radio box under options/advanced/when calculating this workbook...that would direct Excel to always keep the destination format, rather than the "copied from" format. it's not just percent- dollars that aggravates me. All I want to do is copy the formula, and leave all of the formatting from the original cell, in the original cell. In other words I don't want to carry the cells format with the copied data or formula. I am oriented towards the keyboard,(Ctrl+c,Ctrl+v) for copy paste functions,and really don't like to use the mouse- paste/paste special/Pick the way you want it. Possibly a keyboard shortcut? Macro? Voodoo? Fairy Dust?

<tbody>
</tbody>

 

[Pup Denab]

HI boxarox
Sorry it took so long, format you destination column then copy your data, select cell/ cells you have already formatted, then press alt e s v then press enter, hopefully this is enough fairy dust for you ( or use macro recorder, to create a simple marco that can do this for you)
Regards
Pup

 

[mikerickson]

There is a PasteSpecial/Value button that you can use rather than Paste (All)