How to auto fit column width in Excel?
In Excel, you can adjust the column width to fit the cell contents quickly by using the AutoFit Column Width feature. Sometimes, you would like the column width automatically update based on the longest text in that column, that is to say, when input some long text string in cell, the column width will be resized automatically to fit the contents’ length. Is this possible to deal with it in Excel?
Auto fit columns width with VBA code
Auto fit columns width with shortcut keys
Auto fit columns width with VBA code
It seems to make the column width automatically updated with the cell contents’ length is an interesting trick, you can using the following VBA code to finish it.
1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.
2. Then double click ThisWorkbook in the VBA Project objects in the left pane to open a blank module, and then copy and paste following VBA code into the Module:
VBA code: auto fit column with in workbook
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Update 20141127
Dim xCol, xoutCol
xCol = Target.Column
If xCol > 52 Then
xoutCol = Chr(Int((xCol - 1) / 52) + 64) & _
Chr(Int((xCol - 27) / 26) + 64) & _
Chr(Int((xCol - 27) Mod 26) + 65)
ElseIf xCol > 26 Then
xoutCol = Chr(Int((xCol - 1) / 26) + 64) & _
Chr(Int((xCol - 1) Mod 26) + 65)
Else
xoutCol = Chr(xCol + 64)
End If
Columns(xoutCol & ":" & xoutCol).AutoFit
End Sub
3. Then save and close this code, and now, when you enter long text into a cell, the column width will adjust to fit the content automatically.
Auto fit columns width with shortcut keys
If you have multiple columns with part of cell contents are hidden as following screenshot shown, you want to resize the column widths at once to fit their contents. The following shortcut keys may help you.
1. Select the data range which you want to resize the column width.
2. Hold down Alt key and press H + O + I, and now your column widths have been auto fitted to their contents immediately. See screenshot:
Note: If you are using Excel 2003 and earlier version, you need to use these shortcut keys: ALT + O + C + A.
Related articles:
How to auto fit row height of merged cells in Excel?
How to resize row height or column width to fit text in Excel?
Best Office Productivity Tools
Supercharge Your Spreadsheets： Experience Efficiency Like Never Before with Kutools for Excel
Kutools for Excel boasts over 300 features, ensuring that what you need is just a click away...
Supports Office/Excel 2007-2021 & newer, including 365 | Available in 44 languages | Enjoy a full-featured 30-day free trial.
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
