I often have data that is for numbers in the billions. For various reasons (table size, comparability between the numbers, etc...) I'd like to be able to change the display for the cell to be $b, or $m (and have the ability to quickly switch the formatting back and forth between the two) while having the whole value of the cell remain as the full number. So for example, the number 1,000,000,000 could be displayed as either 1 (if formatted as billions) or 1,000 (if formatted as millions) but the value would always be 1,000,000,000.
It seems something like this should be available via custom number formatting, but I haven't come across it there or anywhere else. Have I been missing something obvious?
Last edited by dylanemcgregor; 08-31-2009 at 03:37 PM.
Trailing commas in custom number formats divide by 1000; so 0,,;-0,,;0 displays in millions.
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
Try these custom formats...
$0,,"m"
and
$0,,,"b"
Re: Changing units in cell?
Thank you both. This is perfect, I've never done much with the custom formatting and had no idea that is how this would work. I didn't even know that you could put the "m" in the formatting and not screw up the number.
I tell you, just when I think I'm starting to know Excel fairly OK, something basic like this comes along and I realize I've been making my job much harder than it needs to be for the last 5 years.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks