Adjust Excel Column Widths to Fit Specific Cells

Adjust Excel Column Widths to Fit Specific Cells

I have a quick tip for you today, that might make your worksheets look a little better. We’ll adjust column widths based on a specific cell.

When numbers don’t fit in a cell, Excel shows number signs instead. You can point to a cell that’s filled with number signs, and the value will usually appear in a popup box.

autofitselection01

That gets a bit tiresome though, and it doesn’t help if you need to print the worksheet.

Problems With AutoFitting the Column

A quick way to make things fit is to double-click on a border in the column headings. In the screen shot below, I’ve selected columns E and F, and will double-click on the right border of the column F heading.

autofitselection02

That automatically adjusts the widths, to fit the widest item in each column. Now column E looks good, but column F is much too wide – it fits the text in cell F1.

autofitselection03

I’ll undo the column adjustment, so that they go back to the previous widths.

Focus on Specific Cells

Instead of selecting the entire columns, you can select specific cells, and adjust the width to fit those cells.

  • Then, select cells E9:F9 – the cells with numbers that don’t fit.
  • On the Ribbon’s Home tab, in the Cells group, click Format
  • Click AutoFit Column Width

autofitselection04

The columns width will adjust to fit the widest items in the selected cells, instead of the entire column. That’s much better!

autofitselection05

Excel Chart Utility

Thanks again to Jon Peltier, who provided a copy of his Charting Utility in the recent giveaway. The winner was happy to receive the prize!

Be sure to check it out, if you frequently work with Excel charts. The Charting Utility can save you time, and makes it easier to create complex charts quickly.

_________________

0 thoughts on “Adjust Excel Column Widths to Fit Specific Cells”

  1. With a little bit of event code, we can make the columns auto-fit themselves whenever and entry would be too big to fit in one of its cells. If you wanted this functionality for a single sheet, just put this code in the worksheet’s code module…
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Target.Text Like "*[!#]*" Then Target.EntireColumn.AutoFit
    End Sub

    or if you wanted every worksheet in the entire workbook to have this functionality, then put the following code in the ThisWorksheet code module…
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Not Target.Text Like "*[!#]*" Then Target.EntireColumn.AutoFit
    End Sub

    After you have installed either of these, the columns for worksheets they apply to will automatically widen when an entry would be too big to fit. Of course, this may not be desireable for all situations (just think if you entered an extremely long text value in a cell), so you should use your own judgment whether to implement this event code or not… remember, only you know what kinds of data to expect on your worksheets.

  2. *** BUG FIX ***
    There is a minor problem with the code I posted in my last message… it will auto-fit the column if you enter one of more # signs into a cell. The following modified code should fix the problem…
    For individual worksheet…
    Private Sub Worksheet_Change(ByVal Target As Range)
    If (Not Target.Text Like "*[!#]*") And (Target.Value String(Len(Target.Value), "#")) Then Target.EntireColumn.AutoFit
    End Sub

    For all sheets in a workbook…
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If (Not Target.Text Like "*[!#]*") And (Target.Value String(Len(Target.Value), "#")) Then Target.EntireColumn.AutoFit
    End Sub

    1. The “less than greater than” symbols did not get printed out in my above “BUG FIX”. Here is the code again but restructured to eliminate the problem…
      For individual worksheet…
      Private Sub Worksheet_Change(ByVal Target As Range)
      If (Not Target.Text Like "*[!#]*") And Not (Target.Value = String(Len(Target.Value), "#")) Then Target.EntireColumn.AutoFit
      End Sub

      For all sheets in a workbook…
      Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
      If (Not Target.Text Like "*[!#]*") And Not (Target.Value = String(Len(Target.Value), "#")) Then Target.EntireColumn.AutoFit
      End Sub

      1. If I wanted to have this code to autofit the cells in a range such as: “O6:O56”
        How would I write this code and where do I put it (Module) or Worksheet?

  3. Thanks, Debra. This looked useful, but didn’t work on my Excel 2010. Is this feature 2013 specific?

    1. The procedure Debra outlined worked on my copy of Excel 2003 (using Format/Column/AutofitSelection from its menu bar as opposed to the Ribbon method of later versions of Excel), so it is not Excel 2013 specific. If you still have trouble making it work, I would be interested in know if the event code I proposed earlier in this thread also does not work for you (knowing it might help someone focus in on what the underlying problem is).

    1. @Jon,
      The ALT+O+C+A sequence from XL2003 seems to still work in XL2007 and XL2010 (I do not have XL2013 so I could not test it there).

  4. For Excel 2013, it’s a same keyboard shortcut Alt+H+O+I (for 2007 and 2010) for auto adjusting column widths. Keyboard shortcut helps to get works quickly.

Leave a Reply to Anonymous Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.