When you create a spreadsheet you might want to change the column width to accommodate the length of text in the column. In a previous tip I showed you two ways to do this. Using either way to change the column width can be rather time-consuming and monotonous if you keep adding more data. Wouldn’t it be lovely if Excel just changed the column width automatically? Well, you may be surprised to hear that it can! But I must warn you that what I am about to show you carries a price. We will delve a little into VBA which is a coding language and will use the macro functionality within Excel.
Ultimately, the spreadsheet you create will be saved as a macro-enabled file. Some companies switch off the use of macros for security reasons, and the ability to save or use these types of files. Reading the article on Macro Security for Microsoft Office found on the National Cyber Security Centre webpage would be a good idea before embarking on this exercise. Even when using Macros within Excel at home you should make yourself aware of the risks and ensure you have the right security software.
How to set automatic column widths
Here is a great way to make sure that the column width is what you need without having to change it yourself each time.
- Before entering any data right click on the [sheet tab]
- From the list select [View code]
- Change the setting from [General] to [Worksheet] by selecting from the dropdown arrow.
- Between the first row of text and the last, type the following.
[Cells.EntireColumn.AutoFit]
As you start to type each part of this code, a list appears so that you can double-click on what you require.
- Type a full stop between each command you are typing or selecting
- Once typed, go back to Excel.
How to exit the VBA sheet
Either
- Click back on the sheet you were working on. by right clicking on the Excel Icon on the task bar.
Or
- Select the [Excel tool] from the toolbar.
- Then, within a cell within your current sheet, type in a long word or sentence that exceeds the current width of the column
- Watch as the column self-widens to fit the entry.
- In a new column, type in a short word
- Watch as the column self-shrinks to fit the short entry.
Then start in column A again and change the length of the text as below.
Final result. 
And they are paying you to play with Excel. How fabulous is that?
The only thing is that the code setting of autofit columns is just for the sheet you are in. But as you build this sheet, it will continue to work. Then, as you save the file, you need to save it as a macro-enabled workbook, otherwise it will stop working.
Word of advice: when receiving a file that holds a macro it would be wise to stop and consider where this file came from. do you trust the source? Do you have malware detection installed on your device? It is most certainly worth being safe than sorry. malware has caused a mountain of trouble to companies. So beware please!
