Auto Resize Excel Text Boxes
If other people will be using the Excel files that you build, it might help them if you add some instructions in a Text Box. After you add the text, drag the handles to adjust the Text Box size, so all the instructions are visible.
If you plan to edit the text occasionally, or change the text formatting, you can set the Text Box to resize automatically.
- Right-click on the Text Box, and click Format Shape,
- Then, click the Text Box category, and add a check mark to Resize Shape to Fit Text.
In Excel 2007, the Text Box loses its Wrap Text setting, if you turn on the auto resize. Instead of a narrow text box, you'll often end up with a really wide one.
Tip: Add a few manual line breaks, if you want the Text Box to be narrower.
Excel 2010 Auto Resize With Wrap Text
Thanks to Bob Ryan, of Simply Learning Excel, who let me know that the Text Box resize feature has improved in Excel 2010.
Now, when you open the Format Shape window, there is also a Wrap Text in Shape checkbox.
After you change the Resize setting, the Text Box will get taller, to fit all the text, instead of getting wider.
Adjust the Text Box
With the Auto Resize setting turned on, you can drag the side or corner handles to change the size of the text box. The text box will adjust to the width that you have set, and automatically changes the height, so all the text is visible.
Warning: If you resize the text box with the top or bottom handle, the Resize to Fit setting is automatically turned off.
Text Box Resize Macro
If you're using Excel 2010, and have lots of text boxes in a workbook, you can use a macro to change the Resize to Fit setting.
You can see the Text Resize code, and download the sample workbook, on the Contextures website: Excel TextBox Formatting
Watch the Text Box Resize Video
To see the steps for inserting a text box, and formatting it in Excel 2007 and Excel 2010, you can watch this short Excel video tutorial.
Or watch on YouTube: Excel TextBox Auto Resize and Word Wrap