Custom format to show numbers in thousands, brackets and red for negative numbers

hi Folks
What custom format do I need to use to get the above i.e.
I want
100,000 to appear as 100
-25,000 to appear as (25) (in red)
Thank you
agwalshAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Jerry PaladinoConnect With a Mentor Commented:
Several examples of displaying as Thousands and Millions.  More examples in the attached file

Thousands
#,##0,;[Red](#,##0,)            
       100,000       =      100
        (25,000)      =      (25)

Thousands      
#,##0.00,;[Red](#,##0.00,)            
       100,000       =      100.00
        (25,000)      =      (25.00)

Thousands      
#,##0,k;[Red](#,##0,k)            
       100,000       =      100k

Millions      
#,##0,,;[Red](#,##0,,)            
       100,000,000       =      100

Millions      
#,##0.00,,;[Red](#,##0.00,,)            
       100,000,000       =      100.00

Millions      
#,##0.00,,"M";[Red](#,##0.00,,"M")            
       100,000       =      0.10M
Q-28575563.xlsx
0
 
John HurstConnect With a Mentor Business Consultant (Owner)Commented:
You can use Format Cells, Number and select brackets, red, separators, and rounding 0, but that will not change the number itself.

To show 25,000 as 25 you need to divide by 1000 and then round to 0.
0
 
Hakan YılmazConnect With a Mentor Technical Office MEP EngineerCommented:
You can look here for cell format examples.

This is your case:
# ;[Red](# )
or this;
0 ;[Red](0 )

It may depend on your local decimal or thousand seperator settings. My thousand sepetator is "space" character.
If your thousand seperator is comma "," character, this formulas will be;

#,;[Red](#,)
and
0,;[Red](0,)
0
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
Thinking beyond your initial question, where are you wanting to use this Custom Format?

If it is on a Summary report where the source data is in single units but the Summary is reported in thousands, why not just divide the formula pulling source data by 1000. Or set a cell to show 1, 100, 1000 or 1000000 and then you can use that as the Factor value and have all cells divided by it.

If its for showing on a graph, you can set the Axis label values to show as factored by thousands or millions etc.

Thanks
Rob H
0
 
agwalshAuthor Commented:
Did exactly what I wanted...thank you :-)
0
All Courses

From novice to tech pro — start learning today.