Excel’s AutoFit Row Height feature automatically adjusts row height to fit content, saving time and effort compared to manual adjustment.
But sometimes, it fails miserably no matter how many times you click that AutoFit button.
Using the AutoFit Row Height feature in Excel will automatically determine the best row height based on the most extensive content within that row(s), guaranteeing that all cell contents within the row(s) are visible.
Sometimes, the Autofit Row Height feature may not work as expected.
Whether it’s merged cells, protected sheets, or just plain glitchy behavior causing issues, we’ve got you covered. Follow along as we dive into practical solutions for taming AutoFit Row Height in Excel.
Reason #1: There are Merged Cells in the Range, and the Wrap Text Option is Selected
If you have merged cells in the range with the “Wrap text” option enabled, autosizing the rows will not work. Autofit Row Height doesn’t work with merged cells.
AutoFit Row Height won’t adjust row height in the following example dataset because cells F4 and F5 are merged, and the “Wrap text” option is selected on the “Format Cells” dialog box.
Fix #1 – Unmerge the Cells
If you have merged cells in the cell range you’re trying to autofit, you can unmerge them using the steps below:
- Select the merged cells.
- On the “Home” tab, open the “Merge/Unmerge” drop-down on the “Alignment” group and choose “Unmerge Cells.”
- Try again using the AutoFit Row Height feature to ensure all data in each row is visible.
Fix #2: Manually Enter the Row Height to Maintain Merged Cells
You can manually enter the row height if you do not want to interfere with the merged cells using the following steps:
- Select the merged cells. In this example, we have selected cells F4 and F5.
- On the “Home” tab, open the “Format” drop-down on the “Cells” group and choose “Row Height.”
- Enter the desired row height on the “Row Height” dialog box and click “OK.” In this example, we have entered the value 62.
The row height of the merged cells increases, making all the content visible.
Also read: Can’t Insert a Row in Excel – How to Fix!
Reason #2: The Worksheet is Protected
If the spreadsheet is protected, it may restrict you from making any changes to the row height.
Fix – Unprotect the Sheet
- Unprotect the worksheet by opening the “Review” tab and clicking “Unprotect Sheet” on the “Protect” group. You may be required to enter a password to unprotect the worksheet.
- Try using AutoFit Row Height again to make all content in the rows visible.
While I have covered some common reasons that may interfere with the proper functioning of autofit row height, sometimes even these fixes may not work.
In such cases, try the following:
- Turn off third-party add-ins.
- Restart Excel.
- Try doing it in a new workbook.
- Ensure you are using the latest Excel version.
Also read: How to Copy Row Height in Excel
How to Apply Autofit Row Height in Excel?
You can apply Autofit Row Height in three ways through the user interface in Excel.
Method #1: Use the Autofit Row Height Option on the Ribbon
- Select the row(s) you wish to adjust the height of.
Note: To select a single row, click the row’s number. To select multiple rows, click the number header of the first row, hold down the Shift key, and then click the last row number header in the range you want to select.
To select multiple non-consecutive rows, click the number header of the first row you want to select and hold down the Ctrl key while clicking the number headers of the additional rows you want to select.
To select all rows on the worksheet, press Ctrl + A or click the “Select All” button in the top left corner of the worksheet below the Ribbon:
- On the “Home” tab, open the “Format” drop-down on the “Cells” group and choose “AutoFit Row Height.”
The height of the rows will adjust automatically to fit the content, ensuring the visibility of all content:
Method #2: Double-click the Boundary Below a Row Number Heading
- Select the row(s) you wish to adjust the height of using any of the methods described in Method #1.
- Pont to the bottom boundary of the header of one of the selected rows and notice the double-headed arrow:
- Double-click the row header boundary, and the selected rows’ height will adjust automatically to fit the content.
Method #3: Use a Keyboard Shortcut
- Select the row(s) you wish to adjust the height of using the techniques described in Method #1.
- Press Alt, H, O, and A (Press the Alt, H, O, and A keys in succession).
Method #4: Use Excel VBA
If the worksheet is unprotected and there are no merged cells in the range, you can utilize Excel VBA code to automatically adjust the row height to fit content.
We use the following steps:
- Press the shortcut Alt + F11 to launch the Visual Basic Editor (VBE). Alternatively, on the “Developer” tab, click “Visual Basic” on the “Code” group.
In case you don’t see the Developer tab in your Excel ribbon, you can enable it using this guide.
- Click “Insert” on the menu bar and choose “Module” to insert a new module.
- Copy the following code and paste it into the new module:
Sub AutoFitRowHeight() ' Declare a Range object variable to store the selected range Dim rng As Range ' Set the Range object to the currently selected cells Set rng = Selection ' AutoFit the row height for all rows in the selected range rng.EntireRow.AutoFit End Sub
- Save the file as an Excel Macro-Enabled Workbook (*.xlsm).
- Press the shortcut Alt + F11 to switch to the active worksheet and select the dataset where you want to adjust the row height.
- Press Alt + F8 to open the Macro dialog box, select the “AutoFitRowHeight” macro and click “Run.”
The code executes and adjusts the row height to fit content as in our example dataset below:
Note: This method is effective but may be challenging to troubleshoot if it does not work as expected.
Additional Tips for Troubleshooting
You can troubleshoot the issue of AutoFit Row Height not working in Excel with these additional techniques:
- Turn off third-party add-ins and check if Autofit Row Height works correctly. Excel add-ins can interfere with built-in functions.
- Restart Excel and see if the issue is resolved.
- Try Autofit Row Height in a new, blank Excel workbook to see if the issue persists. If it works in a new workbook, the problem may be with the original file, and you may need to recreate the content in a new worksheet.
- Ensure to update and always use the most recent version of Excel, as updates can fix issues with Excel functions.
In this tutorial, we tackled the frustrating issue of AutoFit Row Height not working properly in Excel. We dove into a few reasons why you might run into problems with AutoFit and then covered some handy solutions to fix it.
Let me know if any part needs more explanation or if you run into other AutoFit problems. I’m always looking to help solve more Excel issues for you!
Other Excel articles you may also like:
- To Do this All Merged Cells Need to be the Same Size – Fix!
- How to Make all Cells the Same Size in Excel (AutoFit Rows/Columns)
- Excel Hyperlink Cannot Open the Specified File – Fix!
- Formulas Not Copying Down in Excel – How to Fix!
- How to Count Filtered Rows in Excel?
- How to Group Rows in Excel?
- How to Freeze the Top Row and First Column in Excel?
- Autofit Column Width in Excel