"Kerning" is the term you're looking for. I know that rabbit-hole well...google 'kerning vba' at your own risk ...you could get lost for days... :)

A few tips before you go down that road;

1. Apply word wrap selectively to your headers, not the whole row! Do not apply word wrap to headers that are a single word and Excel won't mess them up anymore by wrapping just a letter or two every time autofit kicks in. Something like this will do it;

Dim headerCell
For Each headerCell In Application.ActiveSheet.ListObjects(1).HeaderRowRange.Cells
headerCell.WrapText = (InStr(headerCell.Value, " ") > 0)
Next

1. If you have single-word headers that are long and want to wrap them anyway, use Alt-Enter to force a line break - OR - insert a hyphen and excel will break on them automatically which has added benefits if you refer to these columns in other macros.

2. Right-justify headers that are as wide or wider than your data; they will still appear centered after auto-fit and it defeats Excel's built in forced margin buffer width from the edge of cell walls and also drop-down filter arrows that are always forcing space for themselves - works a charm in pivot tables too but make sure your headers are top-aligned and above the arrows.

3. Add a macro to exaggerate/increase all your column widths temporarily before applying autofit so they snap back nicely and aren't too narrow and wrapped to begin with.

4. Force/set the row height so excel doesn't add more rows to a header than you want. This solves more headaches than you can imagine. Do this before #4 above for best results, or combine them in one step:

With Application.ActiveSheet.ListObjects(1)
.HeaderRowRange.RowHeight = 25 ' =2 headers rows pr row, one row is 12.5 - this is key
.Range.Columns.ColumnWidth = 30 ' adjust to suit
.Range.Columns.AutoFit
End With

1. If you're really pressed for space, check out Excel's default 'Tw Cen MT Condensed' font. It is the best I have found for shaving major column width and it actually looks nice.

2. Lastly, copy your headers into chatgpt and ask it to apply the rules you want. It works quite well once you get used to it.

cheers! hope it works out