How to automatically resize the text box to fit the content in Excel?
When you insert a text box in an Excel worksheet, by default, its size remains fixed. This means that if you add more text or delete some, the text box won’t adjust its size to fit the content automatically. As a result, you may find part of your text is hidden inside the box, or too much empty space if there’s less text than expected. For users dealing with dynamic content or frequently updated spreadsheets, manually resizing text boxes can be both time-consuming and inconvenient. Fortunately, Excel provides methods that allow text boxes to automatically resize in response to the text they contain, greatly improving efficiency and presentation.
Below are practical ways to make text boxes in Excel adjust their size automatically to match their content. These methods are suitable for various scenarios, such as reports, dashboards, forms, or collaborative spreadsheets where information may change regularly.
Auto resize a text box to fit the content by resizing shape to fit text feature
If you want an individual text box to automatically adjust its size as you enter or delete text, you can change a property of the text box so that it resizes dynamically. This method is ideal when you need only one or a few text boxes on your worksheet to adapt to their contents, such as when creating labels, instructions, or notes.
1. Once you have inserted a text box, move your cursor to the border of the text box and right-click. In the context menu that appears, choose Size and Properties. See screenshot:
2. This action opens the Format Shape pane on the right side of your worksheet. Under the Size and Properties
tab, go to the Text Box section. Check the box labeled Resize shape to fit text. After ticking this option, you can close the Format Shape pane. See screenshot:
From now on, every time you type in or remove text in this box, the height and/or width of the text box will expand or contract automatically to fit the content. This keeps your layout neat and prevents text from being hidden or overflowing out of view.
Note: This feature needs to be set individually for each text box; Excel does not provide a built-in bulk setting for multiple text boxes at once. If you have numerous text boxes in your worksheet that require this property, you need to repeat the steps above for each one.
Tips and Precautions:
- If the worksheet is protected, you may not be able to adjust this property until the protection is removed.
- Resizing the box will not change font size; it only alters the box dimensions to fit the given text.
- If the text box contains a large amount of text, the box may grow beyond your worksheet view, so check layout after making changes.
- Remember to save your work after adjusting settings to avoid losing changes.
Troubleshooting: If you do not see the Format Shape pane, ensure you are right-clicking specifically on the border of the text box and not inside the text area itself. Sometimes, this option appears as Format Text Box in older versions of Excel.
Applicable Scenarios: This method is best used when you need the resizing feature for a small number of text boxes, or when you want fine control over individual boxes’ behavior. The main advantage is simplicity, while the main limitation is the need to repeat the process for multiple items.
Auto resize all text boxes to fit the content by VBA code
If your worksheet contains many text boxes, manually changing each one can quickly become tedious. In such cases, you can use a VBA macro to process all text boxes at once, automatically setting them to resize with their content. This solution is especially useful for shared documents, survey forms, templates, or any scenario where a large number of text boxes are used and you want uniform behavior.
1. Press Alt + F11 on your keyboard. This shortcut opens the Microsoft Visual Basic for Applications (VBA) editor, where you can enter and manage your VBA code.
2. In the VBA editor window, click Insert in the menu bar, then choose Module. This action adds a new module to your Excel workbook. Copy and paste the VBA code provided below into the module window.
VBA code: Auto resize all text boxes to fit the content in a worksheet
Sub TextBoxResizeTB()
Dim xShape As Shape
Dim xSht As Worksheet
On Error Resume Next
For Each xSht In ActiveWorkbook.Worksheets
For Each xShape In xSht.Shapes
If xShape.Type = 17 Then
xShape.TextFrame2.AutoSize = msoAutoSizeShapeToFitText
xShape.TextFrame2.WordWrap = True
End If
Next
Next
End Sub3. To execute the code, press the F5 key while the module window is active. All text boxes in the current worksheet will instantly be set to resize automatically according to their content, as shown in the screenshot below.
After running the macro, your text boxes will continue to auto-resize whenever you add or delete text in the future. This approach saves time and ensures consistency across all text boxes, especially when designing interactive templates or forms.
Tips for Using VBA:
- Always save your workbook before running macros, and consider creating a backup if you are unfamiliar with VBA.
- Macros may be disabled in some Excel environments; you may need to enable macros via Excel's Trust Center settings.
- If you run the macro again, it will reapply the resizing property to any new text boxes you've added.
- The VBA method applies only to the active worksheet; switch sheets and repeat the process if you have text boxes on other worksheets.
- If an error occurs, check to ensure the worksheet contains text box shapes (usually referred to as "Shape" objects in VBA), and that your security settings permit macro execution.
Pros and Cons: The VBA method offers clear advantages for large-scale updates and reduces repetitive manual work. However, it requires basic familiarity with Excel’s VBA editor, and users in highly restricted workplace settings may encounter limited access.
Troubleshooting and Error Reminders:
- If you encounter "Macros are disabled" messages, make sure macros are enabled in your Excel settings.
- VBA code processing may not affect other objects such as images, charts, or shapes that are not specifically text boxes.
- You cannot "Undo" after running the macro.
Related article:
- How to auto-size comment box to fit its content in Excel?
- Auto fit text to cell using Excel formula
- Link objects (cell text) to text boxes for dynamic resizing
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in