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.
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.
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.
Read More: How to Edit Comment in Excel
Similar Readings
- How to Find Comments in Excel
- How to Filter Cells with Comments in Excel
- How to Reference Comments in Excel
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.
Consequently, you will have your desired formatting applied to the comment and have the following output.
Read More: How to Add Comment in Excel Formula
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.
As a result, you will have the following output on your worksheet.
Read More: Creating and Editing Comments in Excel
Similar Readings
- How to Populate Comment from Another Cell in Excel
- Vlookup to Copy Comments in Excel
- How to Insert Picture in Excel Comment
- How to Copy Comments in Excel
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 Color 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.
Consequently, you will see that your selected fill color is applied in the comment as demonstrated in the following picture.
Read More: How to Extract Comments in Excel
Similar Readings
- How to Extract Comments from Word Document into Excel
- [Fixed!] Comments in Excel Far Away from Cell
- How to Reply to a Comment in Excel
- How to Add Floating Comment 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 from Ribbon
- After that, select the Visual Basic option from the Code group.
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 from Ribbon.
- 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.
- Follow the same steps for the remaining cells with comments to format them.
Read More: [Solved!] Comments Are Not Displaying Properly in Excel
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:
- Firstly, follow the previously mentioned steps in the 2nd Method.
- After that, select all the cells of the Status column.
- Then, go to the Developer tab from Ribbon.
- Now, click on the Macros option from the Code group.
- 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.
Note: In the following image we have shown 3 out of the 5 comments for better visualization.
Read More: [Fixed!] Excel Comment Only Showing Arrow
Practice Section
In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it by yourself.
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
- How to Read Full Comment in Excel
- How to Export Instagram Comments to Excel
- Convert Comments to Notes in Excel
- Difference Between Threaded Comments and Notes in Excel
- Anchoring Comment Boxes in Excel
- How to Make Flashcards in Excel
- How to Remove Comments in Excel
- How to Add Floating Comment in Excel
- How to Hide Comments in Excel
