Formatting Notes In Excel 365

Published by Stephen Milward in Science And Technology · Sunday 28 Dec 2025 · 2 minutes
Tags: Excel, format, notes

I have an Excel spreadsheet with many thousands of notes in cells. However, the formatting isn't consistent and there isn't a command in Excel to format all the existing notes at once. Seriously frustrating!

I use a VBA macro to do this operation. If you've not used a macro before, follow the steps below:

VBA Macro Code for Formatting Notes

Sub FormatAllNotes()
Dim ws As Worksheet
Dim cmt As Comment 
         
Set ws = ActiveSheet

' Loop through each note (comment) in the active sheet
For Each cmt In ws.Comments

With cmt.Shape.TextFrame.Characters.Font

.Name = "Arial"       ' Set font type

.Size = 10            ' Set font size

.Bold = False         ' Remove/Apply bold

.ColorIndex = 1       ' Set text color (1 is black)

End With                  

' Automatically resize the note box to fit its text
cmt.Shape.TextFrame.AutoSize = True

'Optional: Change the background color of the note box

cmt.Shape.Fill.ForeColor.RGB = RGB(255, 255, 225) ' Light yellow

Next cmt

MsgBox "All notes have been formatted.", vbInformation

End Sub

Steps to Implement the Macro

Enable the Developer Tab (if not already visible):
Go to File > Options > Customize Ribbon.
In the right panel, check the box for Developer under Main Tabs and click OK.

Open the VBA Editor:In Excel, press Alt + F11 (or go to the Developer tab and click Visual Basic).

Insert a New Module:In the VBA Editor, in the Project Explorer pane on the left, right-click your workbook's name.
Select Insert > Module. A new code window will open.

Paste the Code:Copy the FormatAllNotes macro code provided above and paste it into the new module's code window.

Run the Macro:Close the VBA Editor.
Go to the Developer tab in Excel and click Macros (or press Alt + F8).
Select FormatAllNotes from the list and click Run.

The macro will iterate through all existing notes on your current sheet, apply the specified font formatting, and resize the 
comment boxes to fit the text.  You can change these values as required.

Note on "Comments" vs. "Notes": Excel 365 introduced "Threaded Comments," which are different from "Notes" 
(the older style of comments). 

The macro above works for Notes. If you are using Threaded Comments, the VBA code is different
(using ActiveSheet.CommentsThreaded instead of ActiveSheet.Comments).

I hope that helps.