How to dynamically adjust column width in Microsoft Excel based on cell contents

One of the most basic problems we all face while dealing with spreadsheets in common is to resize the width of the columns every time we enter something that exceeds the current width. The common solution is to resize the column every time. However, there is always a better and more permanent solution to automatically resize the columns based on the entries.

The solution is easier than you might think, and if you are on the latest version of Microsoft Excel, i.e. Microsoft Excel 2021, I am here with how to automatically resize columns in Microsoft Excel. That way, you will never have to resize the columns manually. If you do not have any intention to print the sheet, this is a great solution for those who will view the spreadsheet, and also who will analyze the data for subsequent tasks.

So, without any further delay, let’s get started with how to resize columns in Microsoft Excel.

Dynamically adjust column width in Microsoft Excel

Open an existing spreadsheet or create a new one, right-click on the current sheet name, and click on ‘View Code’.

Now, enter the following code.

Cells.EntireColumn.AutoFit

Make sure that the Object is set to ‘Worksheet’, and Procedure to ‘SelectionChange’. Refer to the screenshot below for a better understanding of what I meant.

Now, whenever you enter some text in the cell, the width of the cell will dynamically change based on the contents.

As the object is set to Worksheet, if you have additional worksheets in the workbook and you want dynamic width adjustment, you have to repeat the above steps for every worksheet within the document.

While saving the document, it will be saved in (.XLSM), or Microsoft Excel Macro-enabled format. Hence, the dynamic width adjustment will work only with Microsoft Excel. Even if you are using a spreadsheet program that supports macros, you will have to do workarounds for the dynamic width allocation operation to function.