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

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,475
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

BQardi

Active Member
Joined
Aug 30, 2016
Messages
483
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...
 
Last edited:

Chihiro

New Member
Joined
Dec 2, 2016
Messages
32
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.
 

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,475
Office Version
  1. 365
Platform
  1. Windows
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,133,531
Messages
5,659,360
Members
418,499
Latest member
mbcmel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top