Do you spend a lot of time changing the column width in Excel? On the one hand, you’d like to see as many columns as possible for having a good overview. On the other hand, you want to see as much content as possible within a column. In the worst case, you’d only see ### instead of values. Let’s have a look at how to adjust the rows and columns of an Excel sheet.
Method 1: Distribute rows and columns manually
The first method is the most intuitive one: Manually per drag and drop to adjust the width of each column.
Just click on the small column (or row) divider as shown in the image on the right side. Hold the mouse down and move it in the direction you want.
This way also works if you select many columns (or rows) at the same time. All columns get the same width then.
Tip: If there are hidden columns in between, you can unhide them by selecting all columns from the left to right column of the hidden column. Then change the column width for just one column and all of them (also the previously hidden column) get the same width.
Have you ever built an app based on Excel?
Method 2: Double click for automatic sizing the whole column
A faster way is to double click on the divider between columns (or rows). Double click on the line between the column letters on the top of the window can find the smallest possible width.
There is a keyboard shortcut combination for adjusting the column width:
- Select the complete column with Ctrl + Space on the keyboard.
- Press the following keys after each other: Alt –> H –> O –> I.
The keyboard shortcut for rows is quite similar:
- Select the row with Shift + Space.
- Press Alt –> H –> O –> A after each other.
Alternatively you can go to “Home” –> “Format” (under “Cells”) –> “AutoFit Column Width” or “AutoFit Row Height”.
Please note: That way, the column width (or row height) will adapt to the contents of the complete column (or row).
Method 3: Adjust the column width for only the selected cell content
Please take a look at the example on the right side. You got a table in the cell range B2 to C7. Below, in cell B9 is a footnote, which is quite long.
You want to adjust the column width to fit the upper table, B2 to C7 in it.
It’s quite similar to our method 2 above. The only exeption: Instead of adjusting the entire column, just select the cell range B2:C7.
Now apply the keyboard shortcut from above: Alt –> H –> O –> I. Alternatively, click “Home” –> “Format” (under “Cells”) –> “AutoFit Column Width”.
If you want to do the same for the row it, use the following methods after selecting all the rows:
- Press Alt –> H –> O –> A on the keyboard after each other.
- Go to “Home” –> “Format” (under “Cells”) –> “AutoFit Row Height”
Do you want to boost your productivity in Excel?
Get the Professor Excel ribbon!
Add more than 120 great features to Excel!
Method 4: Adjust the column or row width with a fixed value
The previous methods aren’t exact enough for you? Then please consider this way.
You can set a specific column width (or row).
- Select a cell within the column (or row) you want to adjust.
- Click on “Format” on the “Home” ribbon and then on “Column Width…” (or “Row Height…”).
- Type your desired value and confirm with OK.
Please note: The value you type represents the number of characters shown in the standard font. “0” means, that the column (or row) is hidden.
How to deal with a column width larger than the screen?
This might happen, if you automatically distribute columns. The easiest way to solve this: Set a specific column width (e.g. “8”) according to the steps in method 4.
What if you can’t change the column width?
There are several possible reasons if you can’t change the column width. Please check the following:
- Another window is open (maybe in the background).
- The sheet is locked for editing. If yes, click on unlock.
- The workbook is not enabled for editing (is there s yellow bar on top of the rows?).
- You are editing a cell content (also in another window of Excel possible). Press Enter of Esc for leaving the cell.
The column is not shown (e.g. column A) and you can’t scroll to it?
Does your worksheet have frozen panes? If yes, unfreeze them by clicking “View” –> “Freeze Panes” –> “Unfreeze Panes”.
I want to change the row OR column height in Excel sheet without affecting hide row/column.
Kindly help me on this issue.