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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
3 Possible Solutions for Comments in Excel Far Away from Cell
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 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.
Read More: [Solved:] Insert Comment Not Working in Excel (2 Simple Solutions)
Similar Readings
- Reference Comments in Excel (3 Easy Methods)
- Copy Comments in Excel Using VLOOKUP
- How to Add Comment in Excel (4 Handy Methods)
- Export PDF Comments into an Excel Spreadsheet (3 Quick Tricks)
- How to Print Worksheet with Comments in Excel (5 Easy Ways)
Solution 2: Use Format Comment Option from Context Menu
Now we will use another method to solve the problem by using 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 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.
Read More: Creating and Editing Excel Comments to Cells – [An Ultimate Guide]!
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: How to Find Comments in Excel (4 Easy Methods)
💬 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 row height after following each method.
Conclusion
That’s the end of today’s session. I strongly believe that from now, 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.
Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions. Keep learning new methods and keep growing!
Related Articles
- How to Extract Comments from Word Document into Excel
- Export Instagram Comments to Excel (with Quick Steps)
- How to Read Full Comment in Excel (6 Handy Methods)
- Hide Comments in Excel (4 Quick Methods)
- Print Comments in Excel (4 Suitable Ways)
- Remove Pop-up Comments in Excel (4 Suitable Ways)
- How to Extract Comments in Excel (3 Suitable Examples)