Solved

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

Posted on 2014-12-06
5
9,560 Views
Last Modified: 2014-12-09
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
0
Comment
Question by:agwalsh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 95

Assisted Solution

by:John Hurst
John Hurst earned 100 total points
ID: 40484611
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
 
LVL 5

Assisted Solution

by:Hakan Yılmaz
Hakan Yılmaz earned 100 total points
ID: 40484618
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
 
LVL 16

Accepted Solution

by:
Jerry Paladino earned 250 total points
ID: 40485410
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
 
LVL 33

Assisted Solution

by:Rob Henson
Rob Henson earned 50 total points
ID: 40486598
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
 

Author Closing Comment

by:agwalsh
ID: 40488359
Did exactly what I wanted...thank you :-)
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question