# Millions & Thousands Custom Number Formatting

This is a discussion on Millions & Thousands Custom Number Formatting within the Excel Questions forums, part of the Question Forums category; To display 1,100,000 as \$1.1M &amp; 110,000 as \$110.0K I've used the below Custom Number Formatting. [>=1000000] \$#,##0.0,,"M";[ This works ...

1. ## Millions & Thousands Custom Number Formatting

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

2. ## Re: Millions & Thousands Custom Number Formatting

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.

3. ## Re: Millions & Thousands Custom Number Formatting

Hi and welcome to the board!!
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?

4. ## Re: Millions & Thousands Custom Number Formatting

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

5. ## Re: Millions & Thousands Custom Number Formatting

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?

Many thanks,
Kudos

6. ## Re: Millions & Thousands Custom Number Formatting

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

7. ## Re: Millions & Thousands Custom Number Formatting

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

8. ## Re: Millions & Thousands Custom Number Formatting

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.

lenze

9. ## Re: Millions & Thousands Custom Number Formatting

Thanks for all your help Lenze!

10. ## Re: Millions & Thousands Custom Number Formatting

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

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•