Autofit Data Automatically in Columns in MS Excel

Home More Excel (VBA) Training Videos

Here is a query of a website visitor received by email:

How can I make data fit in a column automatically in Microsoft Excel? We do a lot of data entry work in our organization that is shared. Now when a user enters data like a full name (first and last name) I would like the column width to change automatically. The user need not have to drag or double-click to autofit the data in the worksheet cells. Can this be done with an Excel formula?

Normally we enter data into Excel cells and when we wish to 'fit' the data into the worksheet columns we either drag the column ends or double-click to autofit the data. We can also use the 'format' item in the cells group of the home tab to 'autofit column width' - a lenghty and time consuming process.

In the automatic method you take the help of Excel VBA as follows:
  • Click on the developer tab
  • Select Visual Basic
  • In the Microsoft Visual basic for Applications Window that opens double-click on 'ThisWorkbook' item on the left window and in the workspace on the right select 'workbook' by clicking on the left drop-down arrow and from the right-dropdown arrow select 'SheetChange' as shown in the Excel training video.
  • Two lines of code are inserted by the Visual Basic Editor for you
  • Now enter the line: Columns().Autofit
  • The macro code will autofit the data you input in a cell and after you press 'enter'
The macro code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
End Sub

In the Excel VBA code above we have not defined the column address and written only 'Columns()'. This means that all columns will Autofit the data whenever you enter data in any cell in the worksheet or workbook. If you wish that the data only in a specific column should Autofit then you can write the code as 'Columns("A").AutoFit' to have the data Autofit in column A. You can also select multiple columns and have the data AutoFit automatically using 'Columns("A:E").AutoFit" to Autofit the data in columns A, B, D, D and E.
You can even use column numerical values like 'Columns(1).Autofit' to Autofit data in column A. You would have noticed that the numerical value need not be placed in quotes.
Watch the Microsoft Excel training video for a complete demo: