Let Excel automatically resize columns as you type

When you type long text into a cell in Excel, the column width remains constant most of the time. In this case, the text either extends outside the cell or becomes invisible if the adjacent cell is full. Instead of double-clicking on the column line and adjusting the width manually each time, you can have Excel do this automatically as you type. For this, it is enough to use a small VBA code in Excel. After the code is added, when you enter new data into the page, the column width is automatically adjusted according to the content you type.

How to autosize Excel columns? First, open a new Excel sheet. Then, right-click on the page tab at the bottom, that is, the section that usually appears as “Page1”. Click on View Code from the menu that opens. This process will take you to Excel’s VBA editor. Add the following code to the code window that appears: Private Sub Worksheet_Change(ByVal Target As Range) Cells.EntireColumn.AutoFitEnd SubAfter adding the code, you can close the window.

Now, when you write or enter data into any cell on this Excel sheet, the column width will automatically be adjusted according to the content. What does this code do? The Worksheet_Change command here allows the code to run automatically when a change is made in the worksheet. In other words, when you enter new text, number or data into the cell, Excel detects it. The Cells.EntireColumn.AutoFit command automatically expands the columns on the page according to the content.

Thus, long titles, descriptions or data entries appear more neatly within the column. Be careful when saving the file. Since this method uses VBA code, the code may not work if you save the file in normal .xlsx format. When saving the file, you need to choose the Excel Macro-Enabled Workbook format. That is, the file extension should be: .xlsmAlso, when you open the file again, Excel may ask you to enable macros. You need to allow macros for the code to work.

Shortcut: one-time auto-expansion only If you want the columns to be automatically adjusted only once based on the current data, rather than as you constantly type, you can select the relevant columns and double-click on the line between the column headers. However, you need to use the VBA code above for automatic sizing as you type. This method saves time, especially when preparing tables, working with long product names, creating description columns, or editing reports.

Excel automatically adjusts the column width for you after each data entry.