By default, Excel has a certain row height and column width, and when you enter anything that occupies more space than the current size of size, you’ll see that it spills out (as shown below).
Mar 29, 2019 Open your workbook in Microsoft Excel. Double-click the file on your computer to open it now. To create a new workbook, open Microsoft Excel (in the Start menu in Windows, or the Applications folder in macOS), then click Blank workbook. If you move the linked Excel file (for example, to another folder), the link will break. To relink it, follow the steps above again. To edit the data, double-click the table to open the linked worksheet in Excel. If you edit the worksheet in Excel, the changes appear in the Word document when you save the Excel worksheet. If you use Microsoft Excel on your Mac, you can save the spreadsheets you create and open them in Numbers, Apple’s spreadsheet app. It’s a handy feature to use in case you can’t access.
In such cases, you can adjust the rows and columns to fit the text in the cell (so that the text is completely within the cell).
This can be done using the AutoFit feature in Excel.
In this tutorial, I will show you how to use Autofit in Excel using multiple ways. I will also cover some other Excel features you can use when you’re working with text data in Excel.
When you enter anything in a cell in Excel, you would notice that it spills when the size of the text/data is more than the size of the column.
AutoFit is a feature in Excel that allows you to quickly adjust the row height or column width to fit the text completely (so that there is no spilling over to other cells).
Also, AutoFit means that you don’t have to manually specify the column width or row height (or manually drag and adjust the column width). It’s Auto- i.e., it will figure out itself how much it should expand/contract to fit the current cell content
In most cases, you would notice that Excel automatically adjusts the row height when you enter more than one line in the same cell. In those cases, you won’t need to do any row height adjustment.
But sometimes, you may get a dataset from someone or as a download, where row height has already been fixed and needs adjustment. You can use the ‘AutoFit Row Height’ feature in that case.
There are a couple of ways to Autofit in Excel:
All these methods work fine and you can choose whichever works best for you.
In most cases, I use the mouse double-click method as I find it the most convenient.
Suppose you have a dataset as shown below where the text spills to the other column as the column width is less.
Here is how you can autofit column width using the mouse:
You would see that the column width changes automatically to adjust all the cells so that there is no overflowing of text to other cells. Note that the column width is expanded to adjust the cell which needs the maximum width.
You can also use the double-click method to adjust multiple columns at once.
Suppose you have the dataset as shown below where there are two columns and both have text that needs to autofit.
You can use the below steps to autofit column width using the mouse:
As soon as you do this, it will autofit both the selected columns (as shown below)
Note that for this to work, you need to select the entire columns and not the cells in the worksheet.
Another quick way to autofit cells in Excel is by using the option in the ribbon.
Suppose you have a dataset as shown below where you want to autofit Column A.
Below are the steps to do this:
The above steps would instantly expand the column to adjust the text in the cells.
You can also use the same steps to also autofit row height. To do that you first need to select the rows that you need to autofit and then select the ‘Autofit Row Height’ option.
If you’re not a fan of using the mouse and would prefer a keyboard shortcut instead, this method is for you.
Below are the keyboard shortcuts that will autofit the cells:
To Autofill column width:
To Autofill row height:
Here is how to use these keyboard shortcuts:
While Autofit works well in most cases, sometimes, you may find that this feature doesn’t work.
There could be many reasons for it not working, and in many cases, it sometimes works and sometimes doesn’t.
Here I am listing some possible reasons why autofit may not be working on your Excel:
Sometimes, Autofit refuses to work when there are merged cells in your Excel. This is a known issue if you’re using really old versions of Excel (2003 or prior versions).
Microsoft has even acknowledged this issue here. If this happens to you, the only workaround is to manually set the row height or column width.
When you have applied text wrap to a cell and you try and autofit the row, you may find that there are some extra spaces at the bottom or top of the row.
These do not space characters. This is just a space that exists for no reason. Sometimes, changing the column width makes this go away. And there is always an option to manually resize the row (or right-click and use the row-height option to specify the row height value)
Another place where autofit may not work is when you have applied wrap text to cell and you try to autofit the column. This is less of autofit not working and more by design. Since you have applied wrap text, Excel doesn’t know what is the column width it should use when you autofit.
After all, wrap text means that any text would be wrapped in such a way that it would remain within the cell. In such a case, trying to autofit the column won’t do anything. You can, however, adjust the column width manually.
While autofit is what you would want in most cases, there some other options as well that can help you present your data in a better way (especially in cases where autofit is not the way to go).
If you have a cell that has a lot of text, autofitting the column will expand it and make the column huge.
This may not be the right way to present your data, and it may make more sense to have a bigger row and narrower column.
When you apply wrap text to a cell, it makes the content of the cell remain within the cell. This allows you to choose what column width you want and then you can autofit the row height to match the column width.
Adjust the row height to what you want and then you can use auto-fit to adjust the column width to make sure text is all the cell content is visible and within the cell.
Another option you can use is Shrink to Fit.
Unlike Autofit, which adjusts the column width or row height, when you apply ‘Shrink to Fit’ on any cell, it would simply change the cell font to fit everything in the given column width.
For example, if you have some text in a cell and it spills to other cells, when you apply to Shrink to Fit on this cell, the font of the cell would be lowered to fit the cell content in the existing column width.
Below are the steps to apply the ‘Shrink to Fit’ format to a cell:
Note that Shrink to Text doesn’t work for cells that have the ‘Wrap Text’ applied to it.
So these are some of the ways you can use Autofit rows or columns in Excel. I have also covered some possible reasons why autofit may not work on your system and some alternatives to it in Excel.
I hope you found this tutorial useful!
You may also like the following Excel tutorials: