Microsoft

Apply auto-fit property to multiple columns simultaneously

If you're fairly new to Excel, here's an oldie that you'll appreciate--how to adjust the width of multiple columns at once!

Does the mess in the following figure look familiar to you? I just imagine it does! That's what happens when values are longer than the column can accommodate. The solution is simple—widen the column(s).

You probably know that you can double-click the right border of a column's header cell to automatically adjust the width of a column. Specifically, Excel will increase the column width to accommodate the largest entry in the column—with just a double-click! What you might not know is that you can adjust the width of multiple columns at the same time by selecting a block of columns first. Here's how:

  1. Select the columns in question—columns A through E using the example sheet above. (Just drag across the column headers to select multiple columns.)
  2. Hover the mouse pointer over the right-border of any of the selected columns until the double-headed arrow appears..
  3. Double-click a column header border.

This is one of those simple tips that I assume everyone knows until I see someone doing it the hard way. What other oldie but goody tip do you assume everyone knows? Share those with us--you might be surprised how many readers aren't familiar with your tried and true favorite!

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

5 comments
stapleb
stapleb

I would click the select sheet button, above the row number 1 and left of column letter A, then double click to do all columns, or rows if height needs adjustment. I may be tempted to use Ctrl + A, but that does not always select the sheet, it so depends where you are at the time. For the happy little keyboard user above, Ctrl + Space will select a column, then Ctrl + Shift + Right Arrow key to select the columns, but this must be started in a cell containing data for it to work properly.

Neon Samurai
Neon Samurai

Select area to base column autofit on. Select column autofit. Use the keyboard for both. With most programs and most functions within programs, having to reach for the mouse slows you down and promotes RSI. Excel is very much an example with only a very few functions requiring the mouse. First, take the time to learn the keypad for navigation. It's all there; page up, page down, end, home, arrow keys - and, it's easy to enable numlock for a quick numeric entry then disable it again for normal navigation use. Get used to holding shift and crtl to select and using end-arrow to make larger jumps (with or without shift selection). This is basic Excel 101 for the folks who haven't gotten used to it and reduced the use of the mouse crutch. Now, on to the column width and such. crtl+home - jump to the upper left of your selection area or otherwise get the selected cell to your upper most left most applicable cell. hold shift, press end, press right arrow (#6 with numlock off) - this is end then right not end+right at the same time. the result should be selecting columns from applicable left to right most. repeat if you stop at an empty cell and have to move further right still. hold shift, press end, press down - like above, repeat if you stop at an empty cell and need to continue down further. alt+o, c, a - almost faster to type then "the". options -> column -> autofit. Your columns are now autofit to based on the rows/columns of selected cells. You'll be autofitting entire data tables in less time then it takes to reach for the mouse; let alone mouse-select an area and click on stuff. In all cases with Excel, consider reasons to use the mouse the anomaly not the norm.

Glenn from Iowa
Glenn from Iowa

Ctrl + Shift + Space does this nicely, as Shift + Space selects an entire row. Another tip that's handy to use with Ctrl + Space and Shift + Space is Ctrl + + (the plus key on the number pad) and Ctrl + - (the minus key on the number pad) to insert or delete respectively a column or row. Another shortcut I use almost constantly, especially after Ctrl + + or - is F4 to redo. For selecting the entire used range (for example, when setting a filter), Ctrl + End then Ctrl + Shift + Home selects all the used cells on the sheet, since Ctrl + End selects the bottom-right used cell and Ctrl + Home selects the top-left cell. The End then (arrow) shortcuts are nice sometimes, but far too slow and inconsistent when you want to select all the used cells. Ctrl + * (either the asterisk on the number pad or Ctrl + Shift + 8) does a fairly good job of selecting a range of contiguous filled cells too. Another tip allows you to navigate a selected area without scrolling - press Tab to go to the next cell in the selected range and Shift + Tab to go to the previous cell in the selection. All of this without losing your selected range! Finally, if you're entering data in a table format - several cells in a row then down to the beginning of the next line - press the Tab key instead of Enter or the Right Arrow when you're done entering in a cell. It will move to the cell to the right, but then when you're done with that row, press Enter, and voila, it will select the first cell in the next row!

DBlayney
DBlayney

... I don't agree. Some things are just easier for some people if they use the mouse. Making a fetish of not using it is unnecessary. No doubt it is possible to select multiple non-contiguous cells without a mouse but why bother? Anyway, what I really wanted to add is that the same principle applies to auto-fitting rows although Excel does not always get the height right, especially with long text strings in narrow columns.

Neon Samurai
Neon Samurai

I think I tended to go with Excel managing row heights but true, the same process works for fitting height and width. In general, the keyboard in Excel is more about constantly looking to use one's tools better rather than a fetish. Some programs are very much mouse oriented but with Excel, it's really a hindrance and health risk. If you work with spreadsheets for more than five minutes a day, you'll never look back after getting the keyboard involved. From experience, I could work a keyboard much longer than the other analysts could work with the mouse and not a one could keep up in terms of speed. Well, with the exception of the few Excel functions that don't have a keyboard control bound to them. Given only one tip to pass on to newer Excel users; keyboard has to be it.