About expanding Excel cells horizontally?

Question

I am writing some data into an Excel cell and when it reaches the line at the end of the cell it deletes it and moves into the next cell as I continue writing. So it kind of merges the two cells together.

How can I set Excel so that it will expand the cell by moving the end line as I keep writing? And cause all cells above and below to expand to the same extend to keep uniformity? Wrapping the text so that it moves down is not an option here.

Thanks in advance.

Answer 1

If you type a long sentence or phrase into Excel, it isn't actually merging or combining cells - it just appears that way until you type something into the next cell. Once the cell overflows, it will appear to be cut off when the next cell to the right has a value entered. All the text is still there, it just isn't visible on the worksheet.



To autofit the column width, just double click on the line between the two columns. For example, if your long text is in column A, click on the line between the A and B column header at the very top of the worksheet. You'll see a little vertical line with two arrows when you hover over this line. Just double click and the width for column A will autofit to fit the width of the longest line of text in column A. There is no way of doing this as you type automatically (unless using a VBA as another suggested), but doing the autofit is quite easy, so I wouldn't worry about programming something you can do with a simple mouse click.

Answer 2

You can manually 'autofit' your column or you can use VBA to do it automatically. To manually autofit your column after data entry, position your mouse over the *right* border of the column letter at the top until it changes to a vertical line with left and right facing arrows. Then double click your mouse. This will autofit that column.



Or, you can use this VBA solution to do it automatically. This example solution that parses column E. If your column is not 'E', change the "E:E" reference to your column reference, i.e. "B:B", "M:M", etc



Then, copy this event handler, modified as required, to the clipboard (highlight the event handler, right click inside the highlighted area, and 'Copy'):



Private Sub Worksheet_Change(ByVal Target As Range)

If Len(Target) > Target.ColumnWidth Then

Columns("E:E").AutoFit

End If

End Sub



Select the worksheet containing the column to fit and right click the sheet tab at the bottom.



Select 'View Code'.



Paste the event handler into the white editing area to the right (right click inside the area and 'Paste').



Close the VBE (red button - top right).



Save the workbook as an Excel Macro-Enabled Workbook to retain the VBA functionality when subsequently reopened. Archive or delete the non-VBA version of the workbook to avoid confusion.

Answer 3

All recent packages of Microsoft Office have offered the 'simple UI' in both Excel and Word. Typically, one would have to click twice on the rows, they would auto-format to the text/number input there. The older versions, one would have to drag them.