How to change cell width by formula ?

[mrchonginhk]

Is it possible, without use of VBA, to increase/decrease column width ??

I was successful in increasing width using formula but fail to shrink it ...

 

[Andrew Poulsom]

I'm not aware of any formula that will change the column width. Formulas can only return values. They can't perform actions.

Of course if the column is set to AutoFit, its width will increase if you enter something in a cell that won't fit the existing width. But AutoFit only works one way on data entry - upwards - unless you choose it again from the menu, or double click the boundary on the right side of the column heading.

 

[mrchonginhk]

help...

 

[Joe4]

I was successful in increasing width using formula but fail to shrink it ...

How did you do that?

 

[mrchonginhk]

Cell format > Wrap text

Then use formula to change text to longer string.

 

[Joe4]

Then use formula to change text to longer string.

What formula?

 

[mrchonginhk]

=if(A1=1,"Loooooooooong","short")

In case A=1, column expanded. However, if A1 is set to 0 afterwards, column did not shrink...

 

[Joe4]

I think you are confused on how Excel is working. It is not the formula that tells Excel to expand the cell, it is the Autofit property. As Andrew said, formulas, in and of themselves, cannot change cell attributes (i.e. size) they can only return values. It is properties like Autofit and Wrap Text that change cell size.

Also note what Andrew said about Autofit:

Of course if the column is set to AutoFit, its width will increase if you enter something in a cell that won't fit the existing width. But AutoFit only works one way on data entry - upwards - unless you choose it again from the menu, or double click the boundary on the right side of the column heading.

That explains why you cell can grow to fit your text, but does not shrink.

 

[mrchonginhk]

Of course we know formula did not directly change the width. We MAKE use of that to achieve what we need. However it is only half way success. Any other think out of box solution ??

 

[Joe4]

However it is only half way success. Any other think out of box solution ??

Yes, use VBA. You can make it AutoFit columns after every cell entry.

I can't think of any other way other than to use VBA.