Millions & Thousands Custom Number Formatting

**Kudos** · Feb 24th, 2009, 11:02 AM

To display 1,100,000 as $1.1M & 110,000 as $110.0K I've used the below Custom Number Formatting.

[>=1000000] $#,##0.0,,"M";[<1000000] $#,##0.0,"K";General

This works great... unless the number goes negative. For instance a negative million will show as a negative thousand K. I've tried many different variations to attempt the negatives to appear correctly however the initial order Excel views them (positive; negative; zero; text) makes them all invalid.

I suppose ideally what I'm asking is if you can combine Custom Number Format much like a AND OR function? Something to the effect of...

positive millions "M" or thousands "K"; negative millions "M" or thousands "K"; general

... because I would want to format for either/ or millions or thousands at the same time??

I hope this makes sense. Does anyone know of a way to do this? Or better yet... an alternative to my insanity that Excel will accept?

Many thanks,
Kudos

**nbrcrunch** · Feb 24th, 2009, 11:06 AM

the positive part of the format is formatted for Millions. The negative part for Thousands. You can't have both mixed like that.

also, note what you are doing here... two commas before "M" and one comma before K. That's correct for for thousands but you have to remain consistent.
[>=1000000] $#,##0.0,,"M";[<1000000] $#,##0.0,"K";General

There is no magic to "M" & "K" They are merely textual references unrelated to real values.

**lenze** · Feb 24th, 2009, 11:08 AM

Hi and welcome to the board!!
Try changing your 2nd condition.

Code:

[>=1000000] $#,##0.0,,"M";[>0] $#,##0.0,"K";General

lenze
Edit: you might want to color your negatives Red

Code:

[>=1000000] $#,##0.0,,"M";[>0] $#,##0.0,"K";[Red]General

Edit2: How are the numbers obtained? By formula, a Query or user entry?

**lenze** · Feb 24th, 2009, 11:30 AM

After rereading your post, another question comes to mind. How do you want your negatives formatted? If you break them at > [-1000000] and < [1000000], that would be four conditions and you will need to use code.

lenze

**Kudos** · Feb 24th, 2009, 11:36 AM

Right, I realize the millions are in positive and thousands in negative. This was a formula that a contractor obtained for us and we've been using since. I'd like to correct the coding that they had done and in turn make the formatting useful for positives and negatives if possible.

Thanks Lenze! When I try that however it wouldn't format the thousand with a K at the end. The numbers are obtained by manual entry generally. Edit- Reply to Lenze: As for the current coding I'm good for scraping the whole thing and starting over if it would be easier.

Here, I'll try to reiterate. The Original columns below are merely there to demonstrate the number that is entered into the cell. In Column D and G I'd like to format the cells... The positive to look like Column D and the negative to look like Column G (or negative in parenthesis even it doesn't matter). The kicker is... I want to be able to use the same Custom Number format for both.

This will come in handy for larger spreadsheets that have mixed + & - data as well as for a charts source data to save time from editing the chart specifics.

I hope that makes more sense. Would this be possible?

Thanks to all for reading and any advice or help provided!
Many thanks,
Kudos

**lenze** · Feb 24th, 2009, 11:50 AM

Thanks Lenze! When I try that however it wouldn't format the thousand with a K at the end. The numbers are obtained by manual entry generally.

Are you sure?

Code:

[>=1000000] $#,##0.0,,"M";[>0] $#,##0.0,"K";General

works for me. That said, however, to get your 4 formats, you will need code.

lenze

**Kudos** · Feb 24th, 2009, 11:54 AM

Yeah, if I put in -150000 the formatting doesn't change it, it still looks like -150000. Could it be that it's because I'm using Excel 2007?

And when you say I'll need code what does that mean exactly? What would that entail?

Thanks again!
Kudos

**lenze** · Feb 24th, 2009, 12:00 PM

Originally Posted by Kudos

Yeah, if I put in -150000 the formatting doesn't change it, it still looks like -150000. Could it be that it's because I'm using Excel 2007?

And when you say I'll need code what does that mean exactly? What would that entail?

Thanks again!
Kudos

Right -150000 would show up as entered. The custom format for numbers less than 0 is General.

By code, I mean a macro, probably a Change Event.
Let me think about that.

lenze

**Kudos** · Feb 24th, 2009, 12:11 PM

Thanks for all your help Lenze!

**lenze** · Feb 24th, 2009, 12:27 PM

OK. Here goes
Firstt, create 2 Custom Styles. Format>Styles. I used Style1 and Style2. Name then whatever you want. For Style1 Custom Format using

Code:

[>=1000000] $#,##0.0,,"M";[>0] $#,##0.0,"K";General

For Style2, use

Code:

[<-1000000]-$#,##0.0,,"M";General

Now, in the WorkSheet module, copy this code

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target
    Case Is > -1000000
        Target.Style = "Style1"
    Case Else: Target.Style = "Style2"
End Select
End Sub

You can, if needed, specify the exact Cells, columns or ranges to apply this to. Basically, when a number is entered in a cell, the correct Style(formatting) will be applied.

Good luck

lenze