How to format 0.25 as 25.0¢ without changing the cell value?

[JenniferMurphy]

Is there any way to format a cell containing a US dollar value as cents without changing the value in the cell?

I want the value to remain in dollars, but some columns contain very small values (<$0.02). It would be much easier to read 1.375¢ than $0.01375.

 

[BQardi]

Yes, you could use Conditional formatting:

Create a rule where cell value is between 0 and 1 for example.
Format -> Number tab -> Custom -> "#,##0.00¢" (or whatever you want)

EDIT:
Just realized that you wanted it multiplied by 100, which my solution can't handle. Sorry...

 

[Chihiro]

To my knowledge, you won't be able to do exactly what your looking for in custom format alone.

Something similar would be...
[<1].00¢;$0.00_¢

Based on this you can do following. Where <Ctrl+J> is not typed, but key press Ctrl and j.
[<0.02].00¢<Ctrl+J>%;$0.00_¢

Ex: 0.0023
Will display as 0.23¢%

But if you apply Wrap Text, you can hide the % since <Ctrl+J> breaks it out to new line.

 

[Chihiro]

Woops. "Ctrl+J" disappeard. It goes between .00¢ and %.

 

[JenniferMurphy]

To my knowledge, you won't be able to do exactly what your looking for in custom format alone.

Something similar would be...
[<1].00¢;$0.00_¢

Based on this you can do following. Where <ctrl+j> is not typed, but key press Ctrl and j.
[<0.02].00¢<ctrl+j>%;$0.00_¢

Ex: 0.0023
Will display as 0.23¢%

But if you apply Wrap Text, you can hide the % since <ctrl+j> breaks it out to new line.

Now that's creative. I am constantly amazed at the ingenious workarounds people come up with to make up for the holes in Excel.

I think I'll just multiply by 100 in the cell and remember that it's in cents, rather than dollars.

Thanks
</ctrl+j></ctrl+j></ctrl+j>