Excel Macros For Formatting

Save time formatting new Excel models by creating format-macros that are available on every Excel workbook you open. Below is the best way I have to get you there (if you have a more efficient path, please tell me so I can update it).

In the text below, you will see many shortcuts. I STRONGLY encourage you to take the time to commit these shortcuts into muscle-memory for additional time-savings (plus, people watching will think you are an Excel-Master, which of course you are!)

First, I would add the Developer Tab to your Excel ribbon if it’s not there already. The Developer Tab gives you quick access to Macros, VB and add-ins.

File / Options / Customize Ribbon
Customize the Ribbon: Main Tabs
Check “Developer”

Create your first personal macro

Open Excel and record a macro [ALT+LR]
Store the macro in: Personal Macro Workbook
Perform your formatting
Stop recording the macro [ALT+LR]
Repeat the steps for any additional macros

Drop-in more macros and set the shortcut keys

Unhide the Personal Macro Workbook so you can edit it [View/Unhide/OK]
Open the Macros list [ALT+F8]
Click [ALT+E] Edit
Copy/Paste new macros, like the ones I have below (ModelCFO Format Macros)
Close the editor [ALT+F11]
Open the Macros list again [ALT+F8]
Select a newly added macro and click [ALT+O] Options
Enter the new shortcut key and [OK]. Remember to hold down the SHIFT key!
- Blue = Ctrl + Shift + B
- Highlighter = Ctrl + Shift + H
- Note-to-Text = Ctrl + Shift + X
- Number = Ctrl + Shift + N
- Percent = Ctrl + Shift + P
- Remove Blanks = Ctrl + Shift + R
- Text-to-Note = Ctrl + Shift + Q
Repeat for other newly added macros
Re-hide the Personal Macro Workbook [View/Hide]

Make macros available every time you open Excel

Close Excel and reopen it.
Open the Macros list again [ALT+F8]
You should see all the macros from your PERSONAL workbook
Again, to Edit the PERSONAL macros, unhide the workbook

Where is the PERSONAL.XLSB file located?

To find it, when you open Excel, click [ALT+FA] Save As to see the location. From Save As, click on the file location to open up a dialog box to quickly access the file. On my laptop, the file is at:

C:\Users\[username]\AppData\Roaming\Microsoft\Excel\XLSTART

ModelCFO Format Macros

I have created 7 macros initially. Feel free to copy and paste whichever one you like. Just make sure to copy the beginning ("Sub....") all the way to "End Sub" for each one. Once in place, make sure to test them to see how they work.

Blue font (for use as a standard input)
Highlighter. Blue+Yellow Shade+Border for use as a standard highlighted input
Number format set to 0.0
Percent format set to 0.0% in black and (0.0%) in red
Text-to-Note. Copy text and put into the cell on the left as a note
Note-to-Text. Reverse of above, put the note into text on the cell to the right
Remove Blanks. Remove blank cells (not a format, but a nice-to-have)

MACROS TO COPY ARE IN A TEXT FILE AND LISTED BELOW.

EACH MACRO'S NAME IS LISTED AFTER "Sub".

Sub Blue()

' Blue Macro

' Keyboard Shortcut: Ctrl+Shift+B

With Selection.Font

.Color = -65536

.TintAndShade = 0

End With

End Sub

Sub Highllighter()

' Highllighter Macro

' Keyboard Shortcut: Ctrl+Shift+H

Selection.Font.Bold = True

With Selection.Font

.Color = -65536

.TintAndShade = 0

End With

With Selection

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlBottom

.WrapText = False

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = False

End With

Selection.Borders(xlDiagonalDown).LineStyle = xlNone

Selection.Borders(xlDiagonalUp).LineStyle = xlNone

With Selection.Borders(xlEdgeLeft)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

With Selection.Borders(xlEdgeTop)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

With Selection.Borders(xlEdgeBottom)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

With Selection.Borders(xlEdgeRight)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

Selection.Borders(xlInsideVertical).LineStyle = xlNone

Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

With Selection.Interior

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

.Color = 65535

.TintAndShade = 0

.PatternTintAndShade = 0

End With

End Sub

Sub Number()

' Number Macro

' Keyboard Shortcut: Ctrl+Shift+N

Selection.Style = "Comma"

Selection.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"

End Sub

Sub Percent()

' Percent Macro

' Keyboard Shortcut: Ctrl+Shift+P

Selection.NumberFormat = "0.0%;[Red](0.0%)"

End Sub

Sub Text_to_Note()

' Text_to_Note Macro

' Keyboard Shortcut: Ctrl+Shift+Q

Dim cellcomment

cellcomment = ActiveCell.Offset(0, 1)

ActiveCell.AddComment

ActiveCell.Comment.Visible = False

ActiveCell.Comment.Text Text:=cellcomment

ActiveCell.Offset(0, 1).Delete

End Sub

Sub Note_to_Text()

' Note_to_Text Macro

' Keyboard Shortcut: Ctrl+Shift+T

Dim cellcomment

cellcomment = ActiveCell.Comment.Text

ActiveCell.Offset(0, 1).Value = cellcomment

ActiveCell.WrapText = False

ActiveCell.Comment.Delete

ActiveCell.Offset(0, 1).WrapText = False

End Sub

Sub Remove_Blanks()

' Remove_Blanks Macro

' Keyboard Shortcut: Ctrl+Shift+R

Selection.SpecialCells(xlCellTypeBlanks).Select

Selection.Delete Shift:=xlUp

End Sub

Related shortcuts to learn

View Macros	ALT + F8
Start/Stop Macro	ALT + LR
Open/Close Editor	ALT + F11
Square Border	ALT + H + B + S
Format %	CTRL + SHIFT + 5
Add a Decimal	ALT + H + 0
Remove a Decimal	ALT + H + 9

That's it. I hope you have now increased your efficiency!

If you have questions, suggestions, or fixes to anything on this page, please tell me about it!

ModelCFO.com

Comments