Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to AutoFit All Columns in Excel (3 Easy Ways)

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.

dataset for how to autofit all columns in excel

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.

Steps:

  • First, select all cells by selecting the triangle icon where column headers and row headers meet on the top left of the spreadsheet.

selecting all cells how to autofit all columns in excel by applying double clicking method

  • Then put your cursor in between any column headers. Thus your cursor icon will change like the following figure.

applying double-clicking method

  • Now left click on the mouse two times.

how to autofit all columns in excel applying double-clicking method

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)


Similar Readings


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.

Steps:

  • 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.

using excel ribbon to autofit all columns in excel

As a result, all of the columns will autofit to fit the text automatically either by expanding or shrinking in Excel.

how to autofit all columns in excel by using excel ribbon

Read More: How to Group and Ungroup Columns or Rows 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.

Steps:

  • 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.

how to autofit all columns in excel using keyboard shortcut

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.

Merged Cells

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.

manually fitting merged cells

Wrapped Text

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-

Wrap Texts

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.

how to wrap a text in excel

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.

shrink to fit in format cells option

  • Finally, click on OK.

text shrunk to fit cell size

The text will now shrink to adjust the cell size you need. This is another alternative to autofit cells you can follow.


Conclusion

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.


Related Articles

Abrar Niloy

Abrar Niloy

Hi! my name is Abrar-ur-Rahman Niloy. I have completed B.Sc. in Naval Architecture and Marine Engineering. I have found my calling, if you like, in Data Science and Machine Learning and in pursuing so, I have realized the importance of Data Analysis. And Excel is one excel-lent tool do so. I am always trying to learn everyday, and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo