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.

• 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
loExistingTable.Unlist
End If
Else
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

Notes:

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.