Displaying Formulas and Column Width

To display formulas in Excel, as opposed to displaying the formula results, you press the Ctrl+` key combination (that’s an accent grave and it’s to the left of the 1 on most US keyboards). When you do that, Excel helpfully increases the column width to show you more of the formula.

Excel increases the column width, but it doesn’t increase the ColumnWidth. That is, the ColumnWidth property is still the same even though you can see more characters than before. I’m not sure what exactly is changing here, but that’s not really the point of this post. The increase in width is about double. The percent of times that Excel makes the column wide enough to show me what I want to see is 0%.

If I have a 20 character formula, I’m probably not going to use Ctrl+` to check it out. It’s the longer formulas where I need to ensure consistency. I need to increase the column width even more, but that creates another problem. When I change the column width I also change the ColumnWidth. And when I go back to displaying formula results, my column width is now wider than it was. I’m working on a solution, but there’s a lot of moving pieces, so this isn’t fully baked yet.

First I need to store the ColumnWidth before any changes. Then I need to increase the column width so I can see the whole formula. Then I need to set the ColumnWidth back to what it was. For the above formula, that’s pretty straightforward. It’s only about 150 characters. But the one below is about 280 characters and that strategy will make the column wider than the screen – no good.

Round one of my attempt to replace the Ctrl+` behavior is below.

Sub Macro1()

    Static dColWidth As Double
    Dim i As Long
    Dim ash As Worksheet
    Dim aw As Window 'activewindow
    Dim ec As Range 'activecell.entirecolumn
    
    Set ash = ActiveSheet
    Set aw = ActiveWindow
    Set ec = ActiveCell.EntireColumn
    
    'toggle
    aw.DisplayFormulas = Not aw.DisplayFormulas
    
    If aw.DisplayFormulas Then
        'store the old column width
        dColWidth = ec.ColumnWidth
        ec.AutoFit
        
        'Keep reducing the width by 10 until it fits on the screen
        For i = ec.ColumnWidth To ash.StandardWidth Step -10
            If ec.Column < aw.VisibleRange.Cells(aw.VisibleRange.Cells.Count).Column Then Exit For
            ec.ColumnWidth = i
        Next i
        
    Else
        'If the static variable loses scope, set
        'the column to the standard width
        If dColWidth > 0 Then
            ec.ColumnWidth = dColWidth
        Else
            ec.ColumnWidth = ash.StandardWidth
        End If
        
    End If
    
End Sub

Sub Macro1()

Static dColWidth As Double

Dim i As Long

Dim ash As Worksheet

Dim aw As Window 'activewindow

Dim ec As Range 'activecell.entirecolumn

Set ash = ActiveSheet

Set aw = ActiveWindow

Set ec = ActiveCell.EntireColumn

'toggle

aw.DisplayFormulas = Not aw.DisplayFormulas

If aw.DisplayFormulas Then

'store the old column width

dColWidth = ec.ColumnWidth

ec.AutoFit

'Keep reducing the width by 10 until it fits on the screen

For i = ec.ColumnWidth To ash.StandardWidth Step -10

If ec.Column < aw.VisibleRange.Cells(aw.VisibleRange.Cells.Count).Column Then Exit For

ec.ColumnWidth = i

Next i

Else

'If the static variable loses scope, set

'the column to the standard width

If dColWidth > 0 Then

ec.ColumnWidth = dColWidth

Else

ec.ColumnWidth = ash.StandardWidth

End If

End Sub

To combat really long formulas, I AutoFit the formula, then reduce the column width until it fits on the screen. It’s a bit more indirect than converting points to characters and all that funny business associated with ColumnWidth, but I like it. I haven’t tried to break it with hidden columns or anything like, so there’s probably some fragility there.

2 thoughts on “Displaying Formulas and Column Width”

Tris says:

February 10, 2024 at 10:52 am

Hi, this great, I just found a link to it from 7 years ago and! Did you ever get any further with developing the code?

I just tried it out but it’s just for one column at a time it seems, I’d like to see a few columns (or the whole sheet) adjust for each column so that I can visually compare and check is all is at it should be.

It seems such an obvious need yet your article is all I can find out there – it’s strange that no one else needs to be able to view the formulas in all their glorious length without ruining their normal column layout!!!

Any ideas how to do more columns at a time?

Thanks,
Tris
Dick Kusleika says:

April 4, 2024 at 3:59 pm

Sadly, no. My current strategy is to save the file, mess with the column widths all I want, then close the file without saving and reopen it back to its old column widths. Not great.