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.

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

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

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


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.