How to Format Comments in Excel (2 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

Most likely, you know the ways of adding comments in Excel but get troubled while formatting the comments in Excel. Is that correct? If the answer is “yes”, then this article will guide you to format comments in Excel without any hassle. In this article, we will learn 2 easy ways to format comments in Excel. So, let’s start the article and explore these methods.


How to Format Comments in Excel: 2 Ways

In this section of the article, we will learn 2 simple methods to format comments in Excel. Let’s say, we have the Leave Approval Status Table of an organization. The leaves that are Approved, contain a comment in each of them. Our goal is to format these comments.

how to format comments in Excel


1. Using Format Comment Option

Utilizing the Format Comment option is one of the most efficient ways to format comments in Excel. Let’s follow the steps mentioned below to do this.


Step 01: Enable Format Comment Dialogue Box

In the first step, we need to enable the Format Comment dialogue box. Let’s use the procedure discussed in the following section.

  • Firstly, select cell D5 and go to the Review tab from the Ribbon.
  • After that, click on the Notes option.
  • Then, choose the Edit Note option from the drop-down.

Enable Format Comment Dialogue Box to format comments in Excel

As a result, you will have the following output on your worksheet as shown in the following image.

  • Now, select the Border and right-click on it.
  • Following that, choose the Format Comment option.

Subsequently, the Format Comment dialogue box will open on your worksheet.

Note: After selecting the border of the comment, you can also use the keyboard shortcut CTRL + 1 to open the Format Comment dialogue box. 


Step 02: Edit the Font of Comment 

Now, we will edit the Font of the comment by using the Format Comment dialogue box. Let’s follow the instructions outlined below.

  • Firstly, go to the Font tab in the Format Comment dialogue box.
  • Next, choose your preferred Font. Here we selected the Times New Roman option.
  • After that, choose a Font Style. In this case, we chose the Bold Italic option.
  • Now, select the Size of the font. Here, we selected 11 as our font Size.
  • Then, choose your preferred Underline option. For instance, we chose the Double option.
  • Following that, select your desired font Color.
  • Finally, click OK.

Edit the Font of Comment to format comments in Excel

Consequently, you will have your desired formatting applied to the comment and have the following output.


Step 03: Format the Alignment of Comment 

In this step, we will format the Alignment of comments. Now, follow the steps mentioned below to do this.

  • Firstly, go to the Alignment tab in the Format Comment dialogue box.
  • After that, choose your preferred Alignment option in the Horizontal and Vertical fields. Here, we used the Center option in both fields.
  • Then, click OK.

Format the Alignment of Comment to format comments in Excel

As a result, you will have the following output on your worksheet.


Step 04: Change Fill Color 

We can also customize the Fill color of the comment using the Format Comment dialogue box. Let’s use the steps mentioned in the following section to do this.

  • Firstly, go to the Colors and Lines tab in the Format Comment dialogue box.
  • After that, click on the drop-down icon beside the Color field.
  • Then, choose your preferred Color from the drop-down.
  • Finally, click OK.

Change Fill Color to format comments in Excel

Consequently, you will see that your selected fill color is applied in the comment as demonstrated in the following picture.

Read More: Creating and Editing Comments in Excel


2. Applying VBA Macro Feature

Using the VBA Macro feature is another intelligent way to format comments in Excel. By applying a VBA Code, we can do all the formatting in just one step. Let’s follow the instructions outlined below to do this.

Steps:

  • Firstly, go to the Developer tab.
  • After that, select the Visual Basic option from the Code group.

Applying VBA Macro Feature to format comments in Excel

As a result, the Microsoft Visual Basic window will open on your worksheet as shown in the image below.

  • Then, go to the Insert tab in the Microsoft Visual Basic window.
  • Now, choose the Module option from the drop-down.

  • Following that, write the code given below in the newly created Module.
Sub format_comments()
Dim cell_comments As Comment
Dim area As Long
For Each cell_comments In ActiveSheet.Comments
With cell_comments
.Shape.AutoShapeType = msoShapeRoundedRectangle
.Shape.TextFrame.Characters.Font.Name = "Times New Roman"
.Shape.TextFrame.Characters.Font.Size = 11
.Shape.TextFrame.Characters.Font.ColorIndex = 1
.Shape.Line.ForeColor.RGB = RGB(0, 0, 0)
.Shape.Line.BackColor.RGB = RGB(0, 0, 0)
.Shape.Fill.Visible = msoTrue
.Shape.Fill.ForeColor.RGB = RGB(153, 204, 253)
.Shape.Fill.OneColorGradient msoGradientDiagonalUp, 1, 0.23
End With
Next
End Sub

 

Code Breakdown

  • Firstly, we created a Sub Procedure named format_comments().
  • Then, we declared 2 variables cell_comments As Comment and LArea As Long.
  • After that, we initiated a For Next loop for the cell that is selected in the active worksheet.
  • Now, we used a With statement to execute a series of statements.
  • Then, we defined various formatting criteria for the comment.
  • Following that, we ended the With statement.
  • Next, we closed the For Next loop.
  • Finally, we terminated the Sub Procedure.
  • After writing the code, click the Save option.

  • Afterward, press the keyboard shortcut ALT + F11 to return to the worksheet.
  • Now, select the cell you want to change the formatting of the comment. Here, we selected cell D5.
  • Then, go to the Developer tab.
  • After that, click on the Macros option from the Code group.

  • Now, in the Macro dialogue box choose the format_comments option in the Macro name field.
  • Then, click Run.

Consequently, you will have the formatting applied in the comment of cell D5 as marked in the following image.

Final output of method 2 to format comments in Excel

  • Follow the same steps for the remaining cells with comments to format them.

How to Format All Comments at Once in Excel

When there are a lot of comments that need to be formatted, it becomes quite difficult to format them individually. In Excel, we can format all comments at once quite easily by using VBA Code. This method is almost similar to the previous method. We can use the same VBA Code that was used in the 2nd method. Now, let’s use the instructions outlined below.

Steps:

How to Format All Comments At Once in Excel

  • After that, choose the format_comments option under the Macro name field in the Macro dialogue box.
  • Subsequently, click Run.

As a result, all of the comments will be formatted at once.

Final output of method 3 to Format All Comments At Once in Excel

Note: In the following image we have shown 3 out of the 5 comments for better visualization.


Practice Section

In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it by yourself.

practice section to format comments in Excel


Download Practice Workbook


Conclusion

So, these are the most common & effective methods you can use anytime while working with your Excel datasheet to format comments in Excel. If you have any questions, suggestions, or feedback related to this article you can comment below.


Related Articles


<< Go Back to How to Edit Comment in Excel | Comments in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Zahid Hasan
Zahid Hasan

Zahid Hassan, BSc, Industrial and Production Engineering, Bangladesh University of Engineering and Technology, has worked with Exceldmy for 1.5 years. He has written 95+ articles for Exceldemy. He has worked as an Excel & VBA Content Developer. He also worked as a VBA Developer for the Template team. Currently, he is working as a Junior Software Developer for the Excel Add-in project. He is interested in Software Development, Python, VBA, VB.NET, and Data Science, expanding his expertise in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo