CELLWIDTH

[jdellasala]

CELLWIDTH returns the cell width of the optionally provided Cell Reference.

=LAMBDA([Cell],IF(ISOMITTED(Cell),INDEX(CELL("width"),1),INDEX(CELL("width",Cell),1)))

It returns ONLY the cell width, not the Spilled Array CELL("width") now returns. Note that the value returned is the width of the column "rounded off to an integer".

Used on its own, CELL("width") returns a one row, two column array of the width of the column referenced in the left cell, and "TRUE if the column width is the default or FALSE if the width has been explicitly set by the user" in the right cell. That TRUE/FALSE "default" is based on the value of the Template used, not Excel's default width. My Excel setup starts up with and uses a Template where the column width for Sheet1 is 10 (and row height is 15). I learned here that [Ctrl]N will also use that template for a New Workbook, however, if I use File -> New -> Blank Workbook, Excel's true defaults are used - Column Width of 8.54 (reported as 9), and a row height of 14.6.

Probably worth noting here that neither CELL nor CELLWIDTH work with Arrays. (perhaps another LAMBDA CELLWIDTHS()?

Back in 2020 (2019?) I had a large Worksheet where I had a LOT of columns to adjust the width of. Other things also had to be done, so I wrote a VBA script to automate all the changes. One of the things the VBA Script did was to add the formula CELL("width") above the column headers so it could be used after rearranging columns. Then suddenly one day the script broke badly because the formula was in a lot of adjacent cell and returned #SPILL! instead of the value needed.

The LAMBDA is VERY simplistic, but after browsing this board a lot, it doesn't appear that there's anything too short and basic to post as a LAMBDA!

Maybe this is something you'll need one day!

Disclaimer: I use a leading underscore for my LAMBDAs to make them easier to access when a lot are loaded. You're obviously welcome to call it whatever you want!

 

[jaeiow]

It's odd that I have a lambda formula that in one spreadsheet it automatically updates if I show/hide a column. in another spreadsheet, it doesn't. Question: does this update for you when you adjust column width?

 

[jdellasala]

It's odd that I have a lambda formula that in one spreadsheet it automatically updates if I show/hide a column. in another spreadsheet, it doesn't. Question: does this update for you when you adjust column width?

No, it is not volatile, so won't update until something forces the Workbook to recalculate.

 

[jaeiow]

Ok. I see that if I integrate an offset in my formula to call my lambda, I can refresh the cell widthts. Good and thanks.

Edit: Nevermind, something is doing the trick in my one workbook to trigger calculation when I hide/unhide columns, I can't determine what.

 

[Jeffrey Mahoney]

I had created a formula to calculate the cell width awhile ago, but using a lambda seems cool. Here is my version:
=LAMBDA([Cel],LARGE(CELL("width",Cel),1))

 

[jdellasala]

I had created a formula to calculate the cell width awhile ago, but using a lambda seems cool. Here is my version:
=LAMBDA([Cel],LARGE(CELL("width",Cel),1))

I was using CELL("width") to help with formatting a worksheet, then they changed the output to spill two values across columns! What the French Toast??!! To get just the width from that function you have to use

=INDEX(CELL("width"),,1)

The second value is the ever so useful "a Boolean value, the value is TRUE if the column width is the default or FALSE if the width has been explicitly set by the user." - information I desperately needed?

Book1
	A
1	10
Sheet1

Cell Formulas
Range	Formula
A1	A1	=INDEX(CELL("width"),,1)

 

[Jeffrey Mahoney]

I was using it to determine of the column was hidden which equals zero. The LARGE function removes the second variable

 

[jdellasala]

I was using it to determine of the column was hidden which equals zero. The LARGE function removes the second variable

Here, Column B has a width of 0, and Column E is hidden:

Book1
	A		C	D		F	G
1			0	FALSE		0	FALSE
Sheet1

Cell Formulas
Range	Formula
C1:D1,F1:G1	C1	=CELL("width",B1)
Dynamic array formulas.

Unhiding Columns A:F does not change the value for or display Column B. Also, there's no real need for the LAMBDA regardless, although it does work (except for the 0 width thing).
That said, it's would be nice to have a function ISHIDDEN since there's no really reliable way to determine that, just like there's no way (using a formula only) if a cell has Data Validation with a function ISDATAVALIDATION which would also be a welcome addition, even if rarely needed.
One other thing about CELL - a Recalc is needed for it to update. The value won't change if you hide or change the width of a column.
Yes, I've spent way too much time thinking about that function!