How do I get cells on a sheet to automatically fit data?

[Guest]

I want to get the columns in the worksheet to fit the longest piece of data
in each cell. For instance, if I type a short word in a cell, and in the
cell beneath it, the word is longer, I want to learn a shortcut on how to get
the cell width to fit the data entered into it so the column will be adjusted
to show the longest word without wrapping. I have 10 columns and each one
has data from 2 letters to abut 40 letters. I know there is a way to get
each column to resize itself based on the longest text or numbers in them for
the whole sheet, but I've forgotten how. Any help will be greatly
appreciated.

 

[Paul Sheppard]

I want to get the columns in the worksheet to fit the longest piece of
data
in each cell. For instance, if I type a short word in a cell, and in
the
cell beneath it, the word is longer, I want to learn a shortcut on how
to get
the cell width to fit the data entered into it so the column will be
adjusted
to show the longest word without wrapping. I have 10 columns and each
one
has data from 2 letters to abut 40 letters. I know there is a way to
get
each column to resize itself based on the longest text or numbers in
them for
the whole sheet, but I've forgotten how. Any help will be greatly
appreciated.

Hi Mariel

Select the 10 columns and Format > Column > Autofit

 

[Guest]

Highlight the column and follow the Excel menu path Format / Column / Autofit
Selection.

You can also move your mouse just to the right of the column heading so that
it turns into a crosshair. Then double-click and the column will adjust to
the longest piece of data. Example, if you want to autofit column C you
would place your mouse in the column headings just between column C and D
until it turns into a crosshair (plus sign) and then double-click.

You can also use the wrap text property if you want text to wrap around
within a cell rather than widing the column.

Hope this helps.

Bill Horton

 

[Guest]

This is just the information I was hoping to find out. Thank you very much,
William!

 

[Guest]

This is my first time to any type of discussion group, but I really need a
fast answer. You and the other man answered me so fast, and I am so happy
that this worked out for me. Thank you both for your assistance and 2
answers that will both work.

 

[Craig Kalugin]

All the answers i see only changes the column width for an instant in time.
isn't there any way to have the coulmns continuasly change width as more data
is typed into them?

 

[dorrie]

When I set the columns to autofit (and word wrap) the longer words like
"Suggested" are wrapped to the next line instead of the letters being kept
together "Suggeste>d". Any suggestions to fix this automatically?

This is my first time to any type of discussion group, but I really need a
fast answer. You and the other man answered me so fast, and I am so happy
that this worked out for me. Thank you both for your assistance and 2
answers that will both work.

 

[dorrie]

Not sure if my first post was typed under right comment so.... When I format
column to auto fit and word wrap my longer words don't wrap properly. Example
"Suggeste" (on 1st row) and "d" (on 2nd row). Can you help?

 

[Gord Dibben]

You're kinda wanting two things that can't be done together.

Wrap text will mean that the row autofits to give you more than one line in
the cell.

Autofit columns will not work at same time as row autofit and wraptext.

Best to just have row autofit and wrap text then adjust column width
manually to suit the text.

If you want to decide where the lines stop and start within a cell use Alt +
Enter to insert a linefeed in the cell.

this is one line Alt + Enter
this is next line Enter


Gord Dibben MS Excel MVP

Not sure if my first post was typed under right comment so.... When I format
column to auto fit and word wrap my longer words don't wrap properly. Example
"Suggeste" (on 1st row) and "d" (on 2nd row). Can you help?

 

[Jay]

Is there a keyboard shortcut?

Highlight the column and follow the Excel menu path Format / Column / Autofit
Selection.

You can also move your mouse just to the right of the column heading so that
it turns into a crosshair. Then double-click and the column will adjust to
the longest piece of data. Example, if you want to autofit column C you
would place your mouse in the column headings just between column C and D
until it turns into a crosshair (plus sign) and then double-click.

You can also use the wrap text property if you want text to wrap around
within a cell rather than widing the column.

Hope this helps.

Bill Horton

 

[Bill]

I have another problem on the same subject:
This is my first post, so I hope it is in the right place?
when I highlight the entire sheet and set column width to "auto fit" - if I
then type a long figure (easiest if formatted to accounting) the column will
indeed self adjust it's width however, if the long figure is the product of a
function i.e. £456.78 * 2300, the resulting cell fills with hashes and will
not auto fit, I know you can again highlight the column or entire sheet and
do another autofit but in my mind the program should do it automatically,
particularly on a protected sheet, this can be quite a time waster on large
sheets.
Is there an answer to this or is it a bug in the program?

Regards
Bill

 

[confused outlook window]

I am having a similar problem with row autofit. I am using Wrap Text and
double clicking to autofit, but it won't seem to do it for merged cells (it
will for single cells). Is that just the way Excel 2007 was coded or am I
doing something wrong?

 

[Gord Dibben]

Merged cells will not respond to row autoheight.

Either get rid of them or you will need VBA code to deal with them.

See google search thread for code by Greg Wilson. Watch out for word wrap
in the URL which all one line.

http://groups.google.com/group/micr...60cbeb27874?lnk=st&q=&rnum=4#ca1c160cbeb27874


Gord Dibben MS Excel MVP

 

[confused outlook window]

Thanks for the information. Now I just have to figure out how to use VBA
code.

 

[Gord Dibben]

To install Greg's code, copy the code then in Excel, right-click on your
sheet tab and "View Code"

Paste the code into that module.


Gord