Create your own custom formatting shortcut in excel

Like with most things in life, the longer you do something for the more you get set in your ways. Excel is no exception and if I'm a sucker for anything it's number formatting.

Maybe it's the accountant in me, but I think negative numbers are easier to read and look far more stylish wiht a bracket around them. Then if you are going to use a bracket you must sort the alignment out.

Next is the zeros, it's gotta be just a dash, trust me. If you are looking at a model and you've go a sheet full of zeros you need to know if they are really a zero or just a small enough number to be rounded as zero.

Here's it in it's custom formating code along with the percentage style also:

#,##0_);(#,##0);-_)
0.00%_);(0.00%);-_)

All we need is the following bit of code hooked up to assigning a shortcut (in this case Ctrl-Shift-M), using the shortcut cycles through the formats, done!

Paste this code into a macro module in you personal macro workbook:

Sub mcFormattoggle()
'
' MC_Format Macro
'
  numformat = Cells(Selection.Row, Selection.Column).NumberFormat

  Select Case numformat
    Case "General"
      Selection.NumberFormat = "#,##0_);(#,##0);-_)"
    Case "#,##0_);(#,##0);-_)"
      Selection.NumberFormat = "0.00%_);(0.00%);-_)"
    Case Else
      Selection.NumberFormat = "General"
  End Select
End Sub

Then in the ThisWorkbook object paste this code which will assign you shortcut. In this instance it is Ctrl-Shift-M

Option Explicit

Private Sub Workbook_Open()
       
    Application.OnKey "^+M", "mcToggleFormat"
End Sub

Save then restart Excel and you are away!