By default, Excel column widths are 8.43 characters. This approximates 64 pixels. But we can make them in custom sizes, from zero(eg hiding the column) to 255 characters. We can do that with various methods. But changing every column’s width to a custom size to fit values in the spreadsheet can be a drag. So in this tutorial, we will discuss how to autofit all columns at once in Excel.
Download Practice Workbook
You can download the workbook used for the demonstration from the link below.
3 Easy Ways to AutoFit All Columns in Excel
Now we will demonstrate three methods we can use to autofit all columns in Excel. These methods are to adjust column width in Excel, but we will select all columns beforehand so that all will expand or shrink according to the text of cells they are containing automatically. Before we dive into the demonstration, let’s look at the dataset for this article which needs expanding.
As you can clearly see, some cells (the headers) in this dataset have more enormous text values than the cell size. Now we can apply any of the following methods to autofit all columns so that the cells expand to fit text size automatically in Excel.
1. Applying Double-Clicking Method
We have the double-clicking method to auto-adjust the column width or row height of a spreadsheet. The idea is to simply put the cursor on the end of the column or row header and double-click. Excel will thus automatically adjust the size of the cells, either vertically or horizontally, to fit the largest text that column or row contains. But if we do that with all the cells selected, we can autofit all columns of an Excel spreadsheet.
Follow these steps for more details.
- First, select all cells by selecting the triangle icon where column headers and row headers meet on the top left of the spreadsheet.
- Then put your cursor in between any column headers. Thus your cursor icon will change like the following figure.
- Now left click on the mouse two times.
That’s it. That is all you have to do to apply this method to autofit all columns in Excel at once.
Read More: How to Add Columns in Excel (5 Quick Ways)
- How to Lock Columns in Excel (4 Methods)
- Freeze Columns in Excel (5 Methods)
- How to Swap Columns in Excel (5 Methods)
- Unhide Columns in Excel (8 Methods)
2. Using Excel Ribbon
There is a feature in the Excel ribbon that offers to adjust column widths and row sizes. We can use that to autofit all columns in Excel.
Follow these steps to see how we can do that.
- First, select all cells by clicking the triangular icon on the top-left of the spreadsheet where the column and row headers meet.
- Now go to the Home tab on your ribbon.
- Then select Format from the Cells group section.
- After that, select AutoFit Column Width from the drop-down list.
As a result, all of the columns will autofit to fit the text automatically either by expanding or shrinking in Excel.
3. Utilizing Keyboard Shortcut
There is a series of keyboard combinations we can use to change cell size and autofit them. We can call it the keyboard shortcut to autofit Excel column width too.
Follow these steps for the shortcut and how we are applying such for the dataset.
- First, select a cell outside of the dataset. Selecting the outside cell on the spreadsheet is important for the later steps to go accordingly.
- Then press Ctrl+A to select all cells on the spreadsheet.
- Now first press Alt+H on the keyboard.
- Then press O.
- After which press I.
And this is how we can utilize the keyboard shortcut to autofit all columns in Excel according to the text size perfectly.
What to Do When AutoFit Does Not Working in Excel?
Sometimes you may find that none of the autofitting methods are working in your Excel worksheet, for both individual and all selected columns. This can mainly happen for two reasons.
One primary reason the autofitting methods are not working in some of the columns or rows contain merged cells. Let’s look at the following figure.
Here the range B3:D3 are merged into one and no matter what method we choose to autofit any of the B, C, or D columns, it won’t autofit to hold the text size. So the only workaround would be to manually expand or shrink the column headers by clicking them and adjusting the size.
This problem occurs mainly while we try to autofit row heights. The wrapped text feature in Excel wraps the text in a cell in such a way that the cell can contain it. So trying to autofit them doesn’t work in the cells that have already adjusted contents.
A workaround for the problem is to change the column width of the column containing the cell manually. Of course, you can change the row height manually according too.
Some Alternatives of AutoFit in Excel
Although autofitting cell size is the most suitable one for most cases of any excel formatting, there are some situations where we would prefer to use the alternatives. Some of these alternatives are-
Autofitting a column width with a cell that contains a very large text makes the column width very large. This may not be the best presentable option out there always. The wrap text feature allows us to change the row height to fit the column size and vice versa and wraps the visible content within the cell so that the cell can contain them.
To wrap a text in a cell, select the cell and select the Wrap Text feature from the Alignment group of the Home tab.
Shrinking Text to Fit
Instead of increasing column width or row height, we can reduce the text fonts so that they can be within the cell size. This can be helpful when you want to keep all of the cells in a dataset at the same size. Take the following figure for example. The text is spilling out of the cell here.
To use this feature, follow these steps.
- First, select the cell and press Ctrl+1 on your keyboard.
- Then go to the Alignment tab on the Format Cells box that popped up.
- Next, check the Shrink to fit option under Text Control.
- Finally, click on OK.
The text will now shrink to adjust the cell size you need. This is another alternative to autofit cells you can follow.
So that was all about the autofitting of individual and all columns in Excel. Hopefully, you can change them according to your intentions now. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know in the comments below.
For more guides like this, visit Exceldemy.com.