How to automatically adjust column widths in excel every time?

Question

I know a keyboard shortcut to complete this is ALT + H + O + I.
Another way this can be completed is to select a given column, and using one's mouse and double clicking on the selected column (or columns).

However, after every time I type in a cell in a column that appears to be longer than the previously adjusted column width, I have to continue to do the keyboard shortcut.

I hope you understand what I am trying to say; it gets very tedious to do the keyboard shortcut (or use the mouse) every time I type in a cell in the column where the contents of the cell appears to be longer than the previously adjusted column width.

Is there a way to permanently automatically adjust column widths?

I have been reading that this could possibly be done through excel macro; however, I have no experience with such.
Is there a keyboard shortcut to permanently enable this?

Answer 1

Hello WBA-1222,

Thank you for initiating a question in the Microsoft Community.

Unfortunately, there may be no keyboard shortcuts that make this easy to accomplish. But I think there are two ways to solve this problem.

Select all cells and set

After editing the contents of all cells, click on the triangle symbol in the upper left corner of the worksheet (as shown below) to select all cells, and then select Home -> Cells -> Format -> AutoFit Column Width in turn. after setting, the following result is achieved.

However, the disadvantage of this method is that, after setting the column width of the entire worksheet, if you edit a new cell, it will not adapt to adjust the column width, and you still need to set it for a specific cell or for all cells by the above method.

Macro

To automate the column width change after editing a cell, as you said, this automation requires a more advanced tool: macros.

Actually, macros are not that difficult to implement, and based on what I've given, it should be possible. If you think it's necessary, you can refer to the following steps and code, which, if run correctly, will give you better results in terms of auto-adaptation.

To save a worksheet with the auto-fit column feature as a template in Excel, follow these steps:

Step 1: Write the VBA Code

Add VBA Code to the Worksheet Module

1. Open the VBA Editor by pressing Alt + F11.
2. In the Project Explorer on the left, double-click the target worksheet (e.g., Sheet1).
3. Enter the following code: Private Sub Worksheet_Change(ByVal Target As Range)     Dim col As Range     For Each col In Target.Columns         col.EntireColumn.AutoFit     Next col End Sub

Add VBA Code to the ThisWorkbook Module (if you want it to apply to the entire workbook)

1. In the Project Explorer on the left, double-click the ThisWorkbook object.
2. Enter the following code: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)     Dim col As Range     For Each col In Target.Columns         col.EntireColumn.AutoFit     Next col End Sub

Step 2: Save the Workbook as a Template

1. Return to Excel: Press Alt + F11 to return to Excel.
2. Save as Template:
   1. Click the "File" menu.
   2. Select "Save As".
   3. Choose the location where you want to save the file (e.g., Desktop or Documents).
   4. In the "Save as type" dropdown menu, select "Excel Template (*.xltx)".
   5. Name the template and click "Save".

Step 3: Use the Template

1. Create a New Workbook Based on the Template:
   1. Click the "File" menu.
   2. Select "New".
   3. Click the "Personal" tab (or "My Templates" depending on your Excel version).
   4. Choose the template you saved.

Notes

• Ensure macro security settings allow you to enable macros. In Excel, go to "File" -> "Options" -> "Trust Center" -> "Trust Center Settings" -> "Macro Settings", and select "Enable all macros" (only if you trust this template).
• When saving the template, if you need to share it with others, make sure their version of Excel is compatible with macros.

By following these steps, you can create an Excel template with an auto-fit column feature that automatically adjusts column width every time you create a new workbook.

Whichever method you choose, I respect your choice and I hope this has been helpful, if you still have questions, please feel free to ask.

Sincere greetings,

Ian - MSFT | Microsoft Community Support Specialist

Answer 2

Hello,

Although this code does indeed enable an excel workbook to permanently adjust column widths, I am selecting control + z to undo; this doesn't work.

I created a new macro enabled workbook, simply typing in different cells as a trial to adjust the column widths (which work).

Now, one thing I have tried is filling cells with colors, and then trying to undo it with control + z; this works.

However, control + z does not work when I try to undo a cell where I have previously typed in.

Regards.

Answer 3

Hello,

I have a second inquiry.

My question is essentially the same; however, in this scenario, I am asking about permanently and automatically adjusting row widths?

Now, I can understand this can be somewhat tricky with cells being enabled as wrapped text or not; nonetheless, I am still curious is there such a macro that may complete this (while simultaneously being able to function with all the other buttons, such as control + z)?

Regards.

Answer 4

Hello,

I wanted to reach out regarding my question, seeking to enable in excel how one can automatically and permanently adjust row heights and column widths.

I have been referred to using macros to complete this inquiry.

When utilizing macros I was previously referred to in this discussion forum in Microsoft, I realized that functions such as the undo function (control + z) doesn't function in many scenarios.

I was then referred to ask my question in stack overflow.

I have been told by one individual from stack overflow that what I am trying to complete is impossible; I was told that "not every action can be undone, and excel will even warn one about this".

I am simply following up since I am curious if there has been any luck with my inquiry.

I would like to emphasize that as long as my inquiry can be fulfilled, I am satisfied.

Whether it would be a macro, or a button in excel that I am missing / haven't been referrred to yet, whatever completes my inquiry / can answer my question solves all my problems.

Regards.

Answer 5

Hi WBA-1222,

Sorry for my late response.

I tested again using an Excel document with the code previously inserted and encountered the same problem as you. After the column widths were automatically changed, I could not use Ctrl+Z to undo the previous step in the workbook, so I fully understand the inconvenience.

I assume that this may be due to the fact that when column widths are adjusted using the AutoFit method, this is considered a "change" to the worksheet. Therefore, when the column width is adjusted, Excel logs this action as a separate event. When we try to use Ctrl+Z to undo an AutoFit column width adjustment after it has been performed, it may attempt to undo the column width adjustment action rather than undo the editing of the cell contents.

And when undoing a cell's fill color, the fill color change can be undone normally since the column width has not changed.

I have tried many times to adjust and test the VBA code, but have not yet implemented a method that takes into account the availability of automatic column width adjustments and undo keys. I apologize for this.

Since this community does not officially support VBA, there may be a lack of experience with this type of issue. You may also want to consider copying this question and posting it on a more specialized question.

You may use the vba tag and other related tags to post VBA programming questions to Stack Overflow.

DISCLAIMER*: Microsoft provides no warranties and/or guarantees, implied or otherwise, and is not responsible for the information you receive from third-party linked sites or for any support related to the technology.*

However, In the meantime, I will continue to research this code and do my best to find a solution for you. If successful, I will contact you. Sorry again.

Sincere greetings,

Ian - MSFT | Microsoft Community Support Specialist