Page 1 of 2 12 LastLast
Results 1 to 10 of 13

How to change cell width by formula ?

This is a discussion on How to change cell width by formula ? within the Excel Questions forums, part of the Question Forums category; Is it possible, without use of VBA , to increase/decrease column width ?? I was successful in increasing width using ...

  1. #1
    Board Regular
    Join Date
    Dec 2004
    Posts
    599

    Default How to change cell width by formula ?

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

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    69,418

    Default

    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.

  3. #3
    Board Regular
    Join Date
    Dec 2004
    Posts
    599

    Default

    help...

  4. #4
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,284

    Default

    I was successful in increasing width using formula but fail to shrink it ...
    How did you do that?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    Board Regular
    Join Date
    Dec 2004
    Posts
    599

    Default

    Cell format > Wrap text

    Then use formula to change text to longer string.

  6. #6
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,284

    Default

    Then use formula to change text to longer string.
    What formula?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  7. #7
    Board Regular
    Join Date
    Dec 2004
    Posts
    599

    Default

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

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

  8. #8
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,284

    Default

    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.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  9. #9
    Board Regular
    Join Date
    Dec 2004
    Posts
    599

    Default

    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 ??

  10. #10
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    27,284

    Default

    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.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com