Excel – Avoid cell resizing when creating a table using excel

microsoft excelmicrosoft-excel-2016

I've searched far and wide on the Internet, but all the solutions for the questions I find are on how to keep excel from resizing cells in an already existent table or pivot table. I also searched in the settings under options, but I didn't see any one setting that would fix my issue.

My exact problem is that I have a single worksheet in which I want to create multiple tables that store information about my paychecks by year. I've already did the first table for the first year for which I already sized the columns to make the data easy to read. However, when I select my new headers and click Format as Table, Excel resizes all my columns to fit the new text.

Some of my headers do not quite fit in the columns, but this table is for personal use, and I prefer my headers to have clear names (although longer) to reference them easier later. So I hope someone has a solution to this. It would be really annoying having to manually resize all the columns again.

Best Answer

  • There are two ways of accomplishing this:

    1. The simplest way is to create an empty table first and then set it up the way you want.
    2. Use a macro that saves the selection's column widths, formats it as a Table, and then restores the column widths. The macro can be set up to work with a shortcut key or a command button, or it can be added to the ribbon. It is even possible to have it intercept the Format as Table ribbon tool.

    For method 2, add the following code to a standard module:

    ' Module     : <any standard module>
    ' Version    : 0.1.0
    ' Part       : 1 of 1
    ' References : N/A
    ' Source     : https://superuser.com/a/1332155/763880
    Option Explicit
    Public Sub ToggleTable_NoResize()
           Dim ¡ As Long
      Const s_DefaultStyle As String = "TableStyleMedium9" ' Change this for a different default style
      Dim asngColumnWidths() As Single
      ReDim asngColumnWidths(1 To Selection.Columns.Count)
      For ¡ = LBound(asngColumnWidths) To UBound(asngColumnWidths)
        asngColumnWidths(¡) = Selection.Columns(¡).ColumnWidth
      Next ¡
      Application.ScreenUpdating = False
        Dim loNewTable As ListObject
        On Error Resume Next
          Set loNewTable = ActiveSheet.ListObjects.Add(xlSrcRange, Selection, , xlYes)
        On Error GoTo 0
        If loNewTable Is Nothing Then
          Dim loExistingTable As ListObject
          On Error Resume Next
            Set loExistingTable = Selection.ListObject
          On Error GoTo 0
          If Not loExistingTable Is Nothing Then
          End If
          loNewTable.TableStyle = s_DefaultStyle
          For ¡ = LBound(asngColumnWidths) To UBound(asngColumnWidths)
            Selection.Columns(¡).ColumnWidth = asngColumnWidths(¡)
          Next ¡
        End If
      Application.ScreenUpdating = True
    End Sub

    To set it up with a shortcut key:

    • Make sure the Developer tab is showing
    • Press Alt+L+P+M; select the macro; press Options…; and set the shortcut key


    The default style of the Table can be changed in the code where indicated.

    As an added feature, running the macro again will convert the Table back to a normal range of cells.