[Fixed!] Comments in Excel Far Away from Cell

Get FREE Advanced Excel Exercises with Solutions!

If you are looking for some special tricks to solve the problem of  “comments in Excel far away from cell”, you’ve come to the right place. In Microsoft Excel, there are numerous ways to fix this problem. This article will discuss three methods to resolve the problem. Let’s follow the complete guide to learn all of this.


Comments in Excel Far Away from Cell: 3 Solutions

In the following section, we will use three effective and tricky methods to solve the problem “comments in Excel far away from cell”. This section provides extensive details on three methods. You should learn and apply all of these to improve your thinking capability and Excel knowledge.


Solution 1: Apply VBA Code

Here, we will demonstrate how to fix the problem of “Comments in Excel far away from cell”. Let us first introduce you to our Excel dataset so that you are able to understand what we are trying to accomplish with this article. We have a dataset showing some comments. But the comments are far away from the cell. Now, we want to resolve the problem.

Apply VBA Code

You can use Visual Basic For Applications (VBA) to solve the problem of “Comments in Excel far away from cell”. In order to do this, you need the Developer tab to show on your ribbon. After that, you have to follow the following steps to solve the problem.

📌 Steps:

  • Firstly, from the ribbon, go to the Developer tab.
  • Then, Select Visual Basic from the Code group.
  • Or, you have to press Alt+F11 to open the VBA editor.

Comments in Excel Far Away from Cell

  • In the VBA window, go to Insert and select Module.

  • Next, you have to type the following code
Sub Fix_Problem()
Dim ct As Comment
For Each ct In ActiveSheet.Comments
   ct.Shape.Top = ct.Parent.Top + 5
   ct.Shape.Left = ct.Parent.Offset(0, 1).Left + 5
Next
End Sub
  • Afterward, close the Visual Basic window, and press Alt+F8.
  • When the Macro dialogue box opens, select Fix_Problem in the Macro name. Click on Run.

  • This is how you can fix the problem.
  • Therefore, you can see the comments below in their proper place.

Comments in Excel Far Away from Cell

🔎 VBA Code Explanation

Sub Fix_Problem()

First of all, provide a name for the sub procedure of the macro.

Dim ct As Comment

Next, declare the necessary variable for the macro.

For Each ct In ActiveSheet.Comments

After that, starts For Each loop. Specify the worksheets where comments are available.

ct.Shape.Top = ct.Parent.Top + 5

Later, the Shape.Top property will return a value representing the point-to-point distance between the upper-most shape of a shape group and the upper edge of the worksheet.

ct.Shape.Left = ct.Parent.Offset(0, 1).Left + 5

Later, you will use Shape.Left property and Range.Parent property also with Offset method. These properties will bring the comment in proper position.

Next

Then, you have to execute each iteration of the For Each Loop.

End Sub

Finally, end the sub-procedure of the macro.


Solution 2: Use Format Comment Option from Context Menu

We can format comments in Excel to solve the problem. In this part, we will use the Format Comment option from the context menu. Occasionally, the position and shape of the comments box are likely to change when you modify the size of columns or rows. Now we want to solve this problem. To do this we have to follow the following steps.

📌 Steps:

  • Firstly, in the following picture, we can see that the comments box is in the proper place.

Use Format Comment Option from Context Menu

  • Now, if we are going to add some extra columns as shown below picture.
  • To do this, we have to select the columns and then right-click on the columns, and select the Insert option.

Comments in Excel Far Away from Cell

  • As a consequence, we can notice that the position and shape of the comments box are likely to change when we insert new columns.

  • Now, to solve this problem we have to follow some processes.
  • Next, we have to right-click on the edge of the comments and the shape will be highlighted, as shown below picture.

Comments in Excel Far Away from Cell

  • Next, you have to select the Format Comment option from the context menu.

Comments in Excel Far Away from Cell

  • When the Format Comment dialog box appears, select Properties. Then, check Don’t move or size with cells.
  • Next, click on OK.

  • Now we are going to check if our problem is solved or not.
  • Then, we have to select the columns, right-click on the columns, and select the Insert option.

Comments in Excel Far Away from Cell

  • As a consequence, we can notice that the position and shape of the comments box are not likely to change when we insert new columns.
  • This is how we solve the problem and we see the result in the following picture.


Solution 3: Modify Excel Options

Now we will use another method to solve the problem by enabling the built-in option feature. The following dataset shows some comments. But the comments are far away from the cell. Now, we want to resolve the problem. Let’s walk through the steps to solve the problem.

Modify Excel Options

📌 Steps:

  • First of all, you have to open File > Options.

Comments in Excel Far Away from Cell

  • When the Excel Options window appears, select Advanced. Then, check the Indicates only, and comments and notes on hover. Next, you have to check Left-to-Right from the Default direction.

Comments in Excel Far Away from Cell

  • This is how you can fix the problem.
  • Therefore, you can see the comments below in their proper place.

Comments in Excel Far Away from Cell

Read More: [Solved!] Comments Are Not Displaying Properly in Excel


💬 Things to Remember

✎ In solution 2, when you right-click on the edge of the comments and the shape will be highlighted, you have to remember this isn’t the flashing cursor within the comment text. If this is happening, after selecting Format Comment, you will get the following window where you will not get the Properties option.

In the above picture, you can see that there is no Properties option, and as a consequence, you will not be able to solve the problem.

✎ You have to adjust the row height after following each method.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

That’s the end of today’s session. I strongly believe that from now on, you may solve the problem ‘Comments in Excel far away from cell”. If you have any queries or recommendations, please share them in the comments section below.

Keep learning new methods and keep growing!


Related Articles


<< Go Back to Issues with Excel Comments | 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.
Saquib Ahmad Shuvo
Saquib Ahmad Shuvo

Saquib Ahmad Bhuiyan, holding a BSc degree in Naval Architecture & Marine Engineering from Bangladesh University of Engineering and Technology, skillfully integrates engineering expertise with a passion for Excel. He provides solutions to complex issues as an Excel & VBA Content Developer for ExcelDemy Forum. He demonstrates an enthusiastic mindset and adeptness in managing critical situations with finesse, showcasing his commitment to excellence. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, Rhinoceros,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo