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.
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.
- 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.
🔎 VBA Code Explanation
Sub Fix_Problem()First of all, provide a name for the sub procedure of the macro.
Dim ct As CommentNext, declare the necessary variable for the macro.
For Each ct In ActiveSheet.CommentsAfter that, starts For Each loop. Specify the worksheets where comments are available.
ct.Shape.Top = ct.Parent.Top + 5Later, 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 + 5Later, you will use Shape.Left property and Range.Parent property also with Offset method. These properties will bring the comment in proper position.
NextThen, you have to execute each iteration of the For Each Loop.
End SubFinally, 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.
- 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.
- 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.
- Next, you have to select the Format Comment option from the context menu.
- 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.
- 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.
📌 Steps:
- First of all, you have to open File > Options.
- 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.
- This is how you can fix the problem.
- Therefore, you can see the comments below in their proper place.
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
- How to Populate Comment from Another Cell in Excel
- How to Extract Comments in Excel
- How to Extract Comments from Word Document into Excel
- How to Export Instagram Comments to Excel
- Convert Comments to Notes in Excel
- [Solved!] Comments Are Not Displaying Properly in Excel
- Difference Between Threaded Comments and Notes in Excel
