When you work with a large amount of data in Excel, sometimes you may find the column widths are not set optimally. However, you can overcome this unwanted situation if you know how to autofit all columns in Excel.
Resizing the width of every column in an Excel spreadsheet manually involves a tiring and time-consuming procedure. And using the “Wrap text” command increases the row height to make texts of a cell visible.
But. AutoFit adjusts the column width, keeping the row height constant.
Do you want to get a quick illustration of what we are going to do in this article? Check out the image below.
About Columns in Excel
Column width in Excel refers to the horizontal measurement of a column, which determines the amount of data that can be displayed in the column.
By default, the column width is 8.43 characters wide, but you can adjust the width of any column to fit the content of the cells within it.
The image of the spreadsheet above contains some data where many of them cross the edge of several cells. When you have this type of data in your Excel sheet, you must be wondering how to autofit all the columns at a time.
We will try to give several solutions for this. This section will cover 5 different ways to autofit all columns in Excel.
1. Double Clicking Column Header to AutoFit Columns
By implementing this method, Excel will automatically adjust the size of the cells, either vertically or horizontally, to fit the largest text that a column or row contains. But if we do that with all the cells selected, we can autofit all columns of an Excel spreadsheet.
- Select all cells by selecting the triangle icon where column headers and row headers meet on the top left of the spreadsheet.
- This will select all the columns in your spreadsheet.
- Put your cursor in between any column headers. Thus, your cursor icon will change like the following figure.
- Double-click the mouse.
That’s it. That is all you have to do to apply this method to autofit all columns in Excel at once.
2. Applying the “AutoFit Column Width” Feature from the Home Tab
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.
- Select all the columns >> go to the Home tab >> click Format under Cells group >> select AutoFit Column Width from the drop-down list.
- This feature will autofit all the columns in the spreadsheet.
3. AutoFit All Columns with Keyboard Shortcut
There is a simple keyboard shortcut to autofit columns in Excel.
Just press CTRL+A >> ALT+H >> O >> I and Excel will autofit the columns.
- First, press CTRL+A. That selects all columns.
- Then Press the ALT key, and that enables the shortcut keys to be visible for all features.
- Next press H >> O >> I consecutively.
As a result, Excel will have all the columns in the worksheet.
4. Applying VBA Code to AutoFit Multiple Columns
VBA code is also available to autofit all the columns in your Excel sheet. I just, have to use the EntireColumn and Autofit properties of VBA.
- Press ALT+F11 to activate the Visual Basic Editor window.
- From the toolbar options of the VBA window, click Insert >> select Module.
- Now insert your VBA code in the Module window.
Code:
Sub AutoFitAllColumns()
Cells.EntireColumn.AutoFit
End Sub- Click Run on the toolbar option to execute the code.
- The code will make all the columns of your worksheet autofit with the text content.
What Is the Alternative to AutoFit All Columns in Excel (Auto Wrap Text)
You can use the “Format Cells” dialog box to adjust your columns. But this method incorporates the “Wrap Text” command. So, it increases the row height first to make the cell value visible. In the end, it adjusts the column width slightly.
- Select all the columns >> Right-click on one of the selected columns >> choose “Format Cells” from the menu that appears.
- In the Format Cells dialog box, click on the Alignment tab >> check the Wrap text checkbox under the Text control section >> click OK.
- This will make the data visible like the image below. The “Wrap text” checkbox ensures that any text that is too long to fit in a cell will wrap to the next line, allowing the column to be sized appropriately.
- Double-click on the boundary between the column headers to autofit the column width based on the contents of the cells.
How to Manually Change Column Width in Excel
The default width of a column in Excel is 8.43 units. But on the demand, you can change the column width. Just put the cursor in between column headers and hold & drag the mouse to increase or decrease the column width.
And when you have a requirement to fit the width to a particular unit,
- Select any cell in that column >> go to the Home tab >> select Format under Cells group >> click Column width.
- Put the desired unit in the Column Width box to change the width according to your needs.
How to Use AutoFit Feature in Merged Cells in Excel
The autofit feature is not workable for merged cells.
If want to autofit a column containing a merged cell, Excel will autofit the column based on the largest cell values of that column, but it will have no effect on the merged cell.
Why Is AutoFit Not Working in Excel?
- If the column width is set to a specific value, autofit will not adjust the width of the column. You can check the column width by selecting the column and looking at the “Width” value in the “Cells” group on the “Home” tab of the Excel ribbon.
- If the column contains hidden cells, autofit may not work as expected. You can unhide the cells by selecting the column and going to “Home” > “Cells” > “Format” > “Hide & Unhide” > “Unhide Rows” or “Unhide Columns”.
- If there are line breaks in the text, you must manually adjust the column width.
- If the Excel worksheet is protected, you may be unable to autofit columns. You can check for worksheet protection by going to “Review” > “Changes” > “Protect Sheet”.
Frequently Asked Questions
1. Can I autofit a specific column in Excel?
Yes, you can. Select the specific column only and use the AutoFit feature.
2. Do auto-fitting columns affect the cell contents or formatting?
No, it just changes the column width. The content and formats remain the same as it was before autofitting.
3. How do I make sure that columns stay autofitted even after I add or delete data?
If you add or delete data to the cells within that column, the column width may no longer be optimal. In that case, you can use the “Wrap Text” feature stated in Method 4 of this article.
Takeaways from this Article
- Double-clicking the column header is the simplest way to autofit a single column or multiple columns in Excel.
- If you need to autofit columns frequently, using VBA code can automate the process and save you time in the long run.
- The “Format Cells” dialog box gives you more control over column width adjustments, including the ability to set a specific width or adjust column width based on the contents of the cells.
- Remember to save your work frequently to avoid losing any changes you make to your spreadsheet.
Download Practice Workbook
You can download the workbook used for the demonstration from the link below.
Conclusion
Setting columns to autofit by text can be a time-saving technique for managing large amounts of data. You can make your worksheet more reader-friendly if you know how to autofit all columns in your Excel sheet based on the largest text content. Each of the methods stated in the article has specific advantages depending on the requirement of your data. Getting acquainted with multiple methods helps you to choose the best one suited for you. Don’t forget to leave a comment about which method you like the most.
