# Format Numbers As Thousands And Millions

When you want to just show numbers as 9.1 M or 830 K

``````'==================================================================================================
' ## Format Millions with 1 decimal. focuses on constants and formulas
'==================================================================================================
Sub FormatMillions()
Dim rng As Range

Set rng = Application.Selection
If rng.Count = 1 Then
rng.Select
rng.NumberFormat = "[>=100000]#,##0.0,,"" M"";[<=-100000]-#,##0.0,,"" M"";0"
Exit Sub
End If

On Error GoTo checkConstantsFormulas
Set rng = Union(rng.Cells.SpecialCells(xlCellTypeConstants), _
rng.Cells.SpecialCells(xlCellTypeFormulas))
rng.NumberFormat = "[>=100000]#,##0.0,,"" M"";[<=-100000]-#,##0.0,,"" M"";0"
rng.Select
Exit Sub

ConstantType:
On Error GoTo checkFormulas
Set rng = rng.Cells.SpecialCells(xlCellTypeConstants)
rng.NumberFormat = "[>=100000]#,##0.0,,"" M"";[<=-100000]-#,##0.0,,"" M"";0"
rng.Select
Exit Sub

FormulasType:
Set rng = rng.SpecialCells(xlCellTypeFormulas)
rng.NumberFormat = "[>=100000]#,##0.0,,"" M"";[<=-100000]-#,##0.0,,"" M"";0"
rng.Select
' Exit Sub
Exit Sub

checkConstantsFormulas:
Resume ConstantType

checkFormulas:
Resume FormulasType
End Sub

'==================================================================================================
' ## Format Thousands with 1 decimal. focuses on constants and formulas
'==================================================================================================
Sub FormatThousands()
Dim rng As Range

Set rng = Application.Selection
If rng.Count = 1 Then
rng.Select
rng.NumberFormat = "[>=1000]#,##0.0,"" K"";[<=-1000]-#,##0.0,"" K"";0"
Exit Sub
End If

On Error GoTo checkConstantsFormulas
Set rng = Union(rng.Cells.SpecialCells(xlCellTypeConstants), _
rng.Cells.SpecialCells(xlCellTypeFormulas))
rng.NumberFormat = "[>=1000]#,##0.0,"" K"";[<=-1000]-#,##0.0,"" K"";0"
rng.Select
Exit Sub

ConstantType:
On Error GoTo checkFormulas
Set rng = rng.Cells.SpecialCells(xlCellTypeConstants)
rng.NumberFormat = "[>=1000]#,##0.0,"" K"";[<=-1000]-#,##0.0,"" K"";0"
rng.Select
Exit Sub

FormulasType:
Set rng = rng.SpecialCells(xlCellTypeFormulas)
rng.NumberFormat = "[>=1000]#,##0.0,"" K"";[<=-1000]-#,##0.0,"" K"";0"
rng.Select
' Exit Sub
Exit Sub

checkConstantsFormulas:
Resume ConstantType

checkFormulas:
Resume FormulasType
End Sub
``````

Tags:

Categories:

Updated: