How to auto fit columns in excel?

You probably know how to change the width of a column in Excel, but do you also know how to automatically fit the widest entry in a column?

The default width of a column is 64 pixels.

1. You can change the width of a column by clicking and dragging the right border of the column header.

2. To automatically fit the widest entry in a column, double click the right border of a column header.

3. To autofit multiple columns, first select multiple columns by clicking and dragging over the column headers.

Note: to select non-adjacent columns, hold CTRL while clicking the column headers.

4. Next, double click the right border of one of the column headers.

5. To change the width of all columns, first select all columns by clicking the Select All button.

6. Next, change the width of a column.

The techniques described above can also be used to change the height of a row. Instead of clicking the right border of a column header, simply click the bottom border of a row header. There's one more way to autofit columns or rows.

1. First, select multiple rows by clicking and dragging over the row headers.

2. On the Home tab, in the Cells group, click Format.

3. Click AutoFit Row Height.

Result:

• Select the column you need to autofit.
• Click the Home tab.
• In the Cells group, click on the 'Format' option. This will show additional options in the drop-down.
• Click on the 'Autofit Column Width' option.

Select the column or columns that you want to change. On the Home tab, in the Cells group, click Format. Under Cell Size, click AutoFit Column Width. Note: To quickly autofit all columns on the worksheet, click the Select All button, and then double-click any boundary between two column headings.

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:

That’s it!

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.

Or vice-versa…

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.

Microsoft Excel provides a handful of different ways to change column width and adjust row height. The easiest way to resize cells is to have Excel automatically determine how much to widen or narrow the column and to expand or collapse the row to match the data size. This feature is known as Excel AutoFit and further on in this tutorial you will learn 3 different ways to use it.