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.

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

Last Chance for Chart Utility Discount

As a consolation to those who didn’t win his Charting Utility in the recent giveaway, Jon Peltier gave me a coupon code that you can use to buy the Chart Utility at a 15% discount.

  • Click this link to go to the order page
  • Click the "Add to Cart" button, then enter the coupon code 55Ec9QGz in the order window.
  • The coupon will expire tomorrow — Wednesday, 21 August, 2013 — at 11:59:59 pm Eastern time .

_________________

You may also like...

9 Responses

  1. Rick Rothstein (MVP - Excel) says:

    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. Rick Rothstein (MVP - Excel) says:

    *** 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

    • Rick Rothstein (MVP - Excel) says:

      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

  3. Daniel Emkes says:

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

    • Rick Rothstein (MVP - Excel) says:

      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).

  4. Jon Acampora says:

    Great tip! This method of auto adjusting column widths can also be done with keyboard shortcuts.

    Alt+O+C+A (Excel 2003+)
    Alt+H+O+I (Excel 2007+)

    • Rick Rothstein (MVP - Excel) says:

      @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).

  5. Dipti says:

    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 Deb Dobson Cancel reply

Your email address will not be published. Required fields are marked *