by In this tutorial, you will learn how to use the Excel AutoFit feature to make cells, columns or rows expand to fit text automatically.
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.
Excel AutoFit - the basics
Excel's AutoFit feature is designed to automatically resize cells in a worksheet to accommodate different sized data without having to manually change the column width and row height.
AutoFit Column Width - changes the column width to hold the largest value in the column.
AutoFit Row Height - adjusts the column width to match the largest value in the row. This option expands the row vertically to hold multi-line or extra-tall text.
Unlike column width, Microsoft Excel changes the row height automatically based on the height of the text you type in a cell, therefore you won't really need to auto fit rows as often as columns. However, when exporting or copying data from another source, row heights may not auto adjust, and in these situations the AutoFit Row Height opting comes in helpful.
When resizing cells in Excel, either automatically or manually, please bear in mind the following limits to how big columns and rows can be made.
Columns can have a maximum width of 255, which is the maximum number of characters in the standard font size that a column can hold. Using a bigger font size or applying additional font characteristics such as italics or bold may significantly reduce the maximum column width. The default size of columns in Excel is 8.43.
Rows can have a maximum height of 409 points, with 1 point equal to approximately 1/72 inch or 0.035 cm. The default height of an Excel row varies from 15 points on a 100% dpi to 14.3 points on a 200% dpi.
When a column width or row height is set to 0, such column/row is not visible on a sheet (hidden).
How to AutoFit in Excel
What I particularly like about Excel is that it provides more than one way to do most things. Depending on your preferred work style, you can auto fit columns and rows by using the mouse, ribbon or keyboard.
AutoFit columns and rows with a double-click
The easiest way to auto fit in Excel is by double-clicking the column or row border:
- To autofit one column, position the mouse pointer over the right border of the column heading until the double-headed arrow appears, and then double click the border.
- To autofit one row, hover the mouse pointer over the lower boundary of the row heading, and double click the border.
- To autofit multiple columns / multiple rows, select them, and double click a boundary between any two column / row headings in the selection.
- To autofit the whole sheet, press Ctrl + A or click the Select All button
and then, depending on your needs, double click a border of any column or row heading, or both.
AutoFit columns and rows by using the ribbon
Another way to make Excel cells expand to fit text automatically is by using the following options on the ribbon:
To AutoFit column width, select one, several or all columns on the sheet, go to the Home tab > Cells group, and click Format > AutoFit Column Width.
To AutoFit row height, select the row(s) of interest, go to the Home tab > Cells group, and click Format > AutoFit Row Height.
AutoFit column width and row height using a keyboard shortcut
Those of you who prefer working with the keyboard most of the time, may like the following way to auto fit in Excel:
- Select any cell in the column/row you want to autofit:
- To autofit multiple non-adjacent columns/rows, select one column or row and hold down the Ctrl key while selecting the other columns or rows.
- To autofit the entire sheet, press Ctrl + A or click the Select All button.
- Press one of the following keyboard shortcuts:
- To AutoFit column width: Alt + H, then O, and then I
- To AutoFit row height: Alt + H, then O, and then A
Please pay attention that you should not hit all the keys together, rather each key/key combination is pressed and released in turn:
- Alt + H selects the Home tab on the ribbon.
- O opens the Format menu.
- I selects the AutoFit Column Width option.
- A selects the AutoFit Row Height option.
If you are not sure you can remember the whole sequence, don't worry, as soon as you press the first key combination (Alt + H) Excel will display the keys to access all options on the ribbon, and once you open the Format menu, you will see the keys to select its items:
Excel AutoFit not working
In most situations, the Excel AutoFit feature works without a hitch. There are times, however, when it fails to auto size columns or rows, especially when the Wrap Text feature is enabled.
Here's a typical scenario: you set the desired column width, turn Text Wrap on, select the cells of interest, and double click a row separator to autofit the row height. In most cases, rows are sized properly. But sometimes (and this may happen in any version of Excel 2007 to Excel 2016), some extra space appears below the last line of text as show in the screenshot below. Moreover, the text may look correctly on the screen, but gets cut off when printed.
By trial and error, the following solution for the above problem has been found. At first sight, it may seem illogical, but it does work :)
- Press Ctrl + A to select the whole worksheet.
- Make any column a fair amount wider by dragging the right boundary of the column heading (because the entire sheet is selected, all the columns will be resized).
- Double-click any row separator to auto fit the row height.
- Double-click any column separator to auto fit the column widths.
Done!
Alternatives to AutoFit in Excel
The Excel AutoFit feature is a real time saver when it comes to adjusting the size of your columns and rows to match the size of your content. However, it's not an option when working with large text strings that are tens or hundreds of characters long. In this case, a better solution would be wrapping text so that it displays on multiple lines rather than on one long line.
Another possible way to accommodate long text is to merge several cells into one big cell. To do this, select two or more adjacent cells and click Merge & Center on the Home tab, in the Alignment group.
This is how you use the AutoFit feature in Excel to increase cell size and make your data easier to read. I thank you for reading and hope to see you on our blog next week!
36 comments
Data entry on our excell feeds multiple worksheets, one is out of room. How do we expand that worksheet to have more permanent lines without disturbing the links and formulas on that sheet? Our accountant who set it up died unexpectedly. Thanks!
I have .csv files and when I autofit the worksheet all cells expand as expected. I then save the file, close and reopen it to find the cells have been collapsed again. How can I maintain the full "autofit" column width? Thank you.
.csv files are text files and will not retain formatting. To save formats, save the file as an Excel Workbook.
Without a reef, there will be no beautiful waves; without setbacks, there will be no magnificent life
how autofit columns to be in one print layot page?
How to auto fit row and column in excel where data entry automatically.
Thanks for the help. I just wanna recall what I learned before. So far, your instrucions were helpful!I got to press the shortcut keys as you instructed.
I cannot make it to work in my excel. Does this has something to do with merged cells? because i still can't auto fit.
The solution for auto fit row height and it's alternate does not seem to work on my spreadsheet. - the row height to fit the contents, double-click the boundary below the row heading does not work.
I am facing one issue .
issue : I made a web Portal and deployed on IIS. I am using Javascript button to export datatable into excel but after the export when file opened the formatting did not same as like on web portal. So tell me any solution how to retain the formatting of table as like on web portal.
I've created a "form" in excel.
One of the cells, A4 uses data validation from a list. Users select their item and the cell automatically adjusts to their selection. No problem; autofit works here.
The user's selection then, triggers a lengthy sentence to appear, (using a VLOOKUP) in the same row, (row 4) but in column Z (Z4). autofit row height has no effect on the row when the VLOOKUP returns the lengthy sentence.
I suspect excel is treating the autofit function on a first come, first serve basis. In other words, the first cell to autofit sets the size for that row. If I double click the row, of course it autofits, but I'd like it to occur automatically for the user.
When I use row autofit in Excel I find that some fonts work well in the setting, but most fonts do not work. First, why do I see the discrepancy? Second, what are the best fonts you see that work well with the row autofit?
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
Thank you so much. It was indeed helpful
If only it worked for me. I still have random spaces at the bottom of some of the rows. Tried the method several times with increasingly increased widths, to the point of ludicrousness, but there were always some that ended up with those extra spaces.
Svetlana,
I have been around spread sheets since Lotus 123 days and have read and reviewed hundreds, if not thousands of suggestions on Google and Youtube "How To accomplish a task using a particular program". I have NEVER seen a better method of explanation than what you have displayed here. Congratulation: Keep up the GREAT work.
Thank you so much, Jim! It's a real pleasure to hear praise from a professional.
I witness how QuickBooks exports data to Excel and somehow executes an auto fit. Command. I often export data to an spreadsheet and then have to manually re size all columns. Is it possible to include nautofit command at the end of exports data that will execute and in turn automatically size the columns? Thanks
Hello,
Please try using a VBA macro. We are always ready to help you, but we do not cover the programming area (VBA-related questions).
You may try to find the solution in VBA sections on mrexcel.com or excelforum.com.
Sorry we can’t assist you better.
i have to adjust entire sheet according to column A
I have a query. I want to adjust data in one sheet to auto adjust value according to another sheet.
Example:
Sr. Code Value
1. 1234 1212
2. 4567 1814
3. 7890 1314
sheet 2
Sr.Code Value
1. 4567 1225
2. 1234 1817
3. 7890 1121
as shown codes are same but may vary in rows. how to auto adjust the code different values to put automatically in front of their dedicated codes.
Thanks in advance!
Hi,
Can u share the details with excel sheet.
I am using Excel 2016, have a workbook with 12 sheet, each sheet has 50+ columns. I spend almost as much adjusting column width as I do entering data.
I have seen a VBA written so that each time you "enter" data the column automatically adjust its width. Unfortunately, I cannot get it to work in my version or Excel, which is 2016.
I am wondering if you have something that would work? I already have one VBA that will do this for each sheet in the work book, but in order to run it I have to select alt+F8 and then select run. This is almost as time consuming at doing it manually and isn't what I am looking for. I would like to be able to type data and the minute I push the enter key the column should automatically adjust to fit the data.
Thanks,
Mark
Very nice and Excellent I admire your work and valuable knowledge you share.
Thank you for your kind words, Mohammad! Our team will continue doing our best to bring the most interesting and useful features of Excel to our readers.