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:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
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: