Rounding Numbers to Millions





Custom Number Format.

 

We can use Custom Number Format, to display a Number in Million or Display it in some Color.

Its a tool from, old version of excel when there was no Conditional Formatting option in Excel.

All conditional options are performed by using Custom Number Formal.

 

To open Custom Number Format Dialog box,

  • Select the cells, where you want to apply Format. Lets say B2 to B10.
  • Press Ctrl + 1, or you can click on Home > Font Group > Font Dialog Box launcher.
  • Font Dialog
  • Go to Number Tab.
  • In category Area, > Select Custom.

Custom

 

 

Custom Number format has part separated by semicolon (;). So for four part onlky three Semicolor Required.

Part1 ; part2 ; part3 ; part4

  • part1 is applied if cell value is positive,
  • part2 is applied if cell value is Negative,
  • part3 is applied if cell value is zero,
  • part4 is applied if cell value is Text,

 

In each part we can set condition (except 4th part / text part) and set color.

Let’s Learn Few Techniques.

 

 

 

  • use custom format as ;;;

to display cell as blank, as no format applied for any part

  • Use [Blue];[Green];[Red];[Yellow]

to display

  1. Positive Numbers as Blue
  2. Negative Number’s as Green
  3. Zero values to Red and
  4. Text Values to Yellow.
  • Use[>1000][Blue];[Green];[Red]
    1. Positive Numbers and greater 1000 then only format as Blue
    2. Else assume it as a part of 0 part, means apply formatting from Zero section
    3. If negative apply Negative part, which is Green.
  • Use [>1000][Blue] 0.00;[Green] ”-“ #0,.00;[Red]0
    • Positive Numbers and greater 1000 then format as Blue and apply Number format as two Decimal.
    • Else assume it as a part of 0 part, means apply formatting from Zero section
    • If negative apply Negative part, and apply color as Green, and Number Format as Thousand and for next two digit apply two Decimal.
      • So If actual number is -490, it will display it as – 0.49, and if Number is -67084530, will display it as -67084.53. “-“ was displayed,as we have specified it. Otherwise, it will omitted that (-) also.

Lets look below example with Pictorial Demostration..

[Blue][>1000000]#,##0,,” +ve Bi”;[Green](0.00,)” -Ve Th”;[Red]“-”;[Yellow]“T”

CustomNumbderFormat



Leave a Reply

Your email address will not be published. Required fields are marked *


9 + = eleven

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>