excelexcel-formula

Convert number in excel to M or B

How can to convert numbers in Excel to hundred normal and (K) to thousand and (M) to Milion and (B) to Billion

like the below

enter image description here

and i used this format but give all hundred number with (K)

\[\<100000\]0.00,"K";\[\<100000000\]0.00,,"M";0.00,,,"B"

enter image description here

i need formating like the below image

enter image description here

Solution

  • Just as Ron suggested, using conditional formatting would keep the value as number.

    1. Reset formatting: select the range where you have applied your custom formatting and change it to General. This may be why nothing changed when you applied the conditional formatting.

    2. Apply conditional formatting (Menu > Format > Conditional Formatting) as here (using your excellent format code :)

      1. Add new rule (press +)
      2. Choose Classic > Format only cells that contain > Cell Value > Greater than or equal to
      3. Enter 1000
      4. Format with > Custom Format - enter your format code
      5. Applies to: Enter the range containing the numbers.

    Formatting dialog