Text Box in Excel - A Complete Overview

The sample dataset contains sales of different accessories in different locations. The text box below showcases a summary of the dataset, displaying the highest and lowest sales.

What Is a Text Box?

A Text Box is a rectangular-shaped object that can hold and display texts within a specific area of a worksheet.

How to Add Text Box in Excel: 4 Methods

The dataset contains sales data of a company.

Method 1- Using the Text Box Option

Go to the Insert tab and select Text Box in Text.

The cursor will change.
Click an area in the worksheet to insert the text box and drag it down.

Enter the text in the text box.

Using Keyboard:

You can also press Alt, N, X . It will create a text box on the worksheet.

Method 2- Adding a Text Box from Shapes

Go to Insert >> Illustrations >> Shapes.
Select Text Box in Basic Shapes.

The cursor will change.
Click an area in the worksheet to insert the text box and drag it down.

Enter the text in the text box.

Method 3 – Inserting Text Box from the ActiveX Controls in Excel

The ActiveX Control text boxes provide more functionality and customization options:

Creating interactive forms
Creating data entry fields
Displaying dynamic information

FTo insert an ActiveX Control Text Box:

Enable the Developer tab on the Ribbon.
Go to Developer >> Controls >> Insert.
Select Text Box (ActiveX Controls) in ActiveX Controls.

The cursor will change.
Click an area in the worksheet to insert the text box and drag it down.

The object is in design mode. To turn it off, click Developer >> Controls >> Design Mode.

Enter the text in the text box.

Note

Use Shift+Enter to insert line breaks in ActiveX Control text boxes.

Adding a Multi-Line Text Box with a Scrollbar

Create an ActiveX Control text box first using the previous method.
Right-click the text box and select Properties.

In the Properties window, select True as MultiLine.
Choose 1, 2, or 3 for ScrollBars. Here, 2 to create vertical scrollbars.

Enter your text. The scrollbars will be displayed in the text box.

Method 4 – Using a VBA to Add Multiple Text Boxes in Excel

Click on the image to get a detailed view

Go to Developer >> Code >> Visual Basic.

Select Insert >> Module.

Insert the following code in the module.

Sub InsertMultipleTextBoxes()
    Dim ws As Worksheet
    Dim textBox As Shape
    Dim i As Integer
    Dim numTextBoxes As Integer
    Dim textArray() As String

    ' Set the worksheet where you want to insert the text boxes
    Set ws = ThisWorkbook.Sheets("4.1 VBA Multiple Box") ' Replace "4.1 VBA Multiple Box" with your actual sheet name

    ' Set the number of text boxes you want to insert
    numTextBoxes = 3 ' Change this to the desired number
    
    ' Array of different texts for each text box
    textArray = Split("Sioux City has the highest television sales,Stony Brook has the highest mobile sales,Green Bay has the highest monitor sales", ",")

    ' Loop to insert multiple text boxes
    For i = 1 To numTextBoxes
        ' Insert a text box
        Set textBox = ws.Shapes.AddTextbox(Orientation:=msoTextOrientationHorizontal, _
                                          Left:=50 + (i - 1) * 200, Top:=230, Width:=150, Height:=50)
        
        ' Set the text inside the text box
        textBox.TextFrame.Characters.Text = textArray(i - 1)
        textBox.TextEffect.FontSize = 12
        textBox.Line.ForeColor.RGB = RGB(0, 0, 255)
    Next i
End Sub

Click on the image to get a detailed view

Press F5 to run the code.

Click on the image to get a detailed view

Note

Use the same code without a loop to create a single text box:

Sub InsertTextBox()
    Dim ws As Worksheet
    Dim textBox As Shape
    
    Set ws = ThisWorkbook.Sheets("4. VBA") ' Replace "4. VBA" with your actual sheet name
    
    Set textBox = ws.Shapes.AddTextbox(Orientation:=msoTextOrientationHorizontal, _
                                      Left:=60, Top:=230, Width:=250, Height:=80)
    
    textBox.TextFrame.Characters.Text = "Total Sales by Store:" & vbNewLine & "Green Bay has the highest total sales with $50,960, followed by Sioux City with $41,614." & vbNewLine & "Rock Island has the lowest total sales at $14,628."
    textBox.Line.ForeColor.RGB = RGB(0, 0, 255)
End Sub

How to Format Text Box in Excel

1. Changing Shapes and Editing Points

Click the text box and go to Shape Format.
Select Shape Format >> Insert Shapes >> Edit Shape >> Change Shape.

Click on the image to get a detailed view

The selected text box will change.

Change Edit Points:

Click the edge of the text box. The mouse cursor will display a pointed plus sign.

Select Shape Format >> Insert Shapes >> Edit Shape >> Edit Points.

Click on the image to get a detailed view

Click and drag the edge points to move the edges and change shape manually.

2. Changing Shape Styles

Select the text box and go to the Shape Format tab.
Select a style in Shape Styles.

Click on the image to get a detailed view

Access Quick Styles by clicking the downward arrow.

Click on the image to get a detailed view

Click a style.

Click on the image to get a detailed view

The text box will change. Here, Subtle Effect- Black, Dark-1 was selected.

3. Changing the Fill Color of Text Boxes

Click the text box and select Shape Format >> Shape Styles >> Shape Fill.
Select a color.

Click on the image to get a detailed view

You can also change the fill color choosing Fill in Font in the Home tab.

4. Removing the Border

Click the text box and select Shape Format >> Shape Styles >> Shape Outline >> No Outline.

Click on the image to get a detailed view

The border will be removed.

5. Changing Font Size, Style, and Color

Go to Home >> Font >> Font Color.

Note

Select the text inside the text box if you want to change its color.

There are other editing options in Font.

Access different WordArt Styles in Shape Format.

Click on the image to get a detailed view

6. Changing the Line Spacing Inside a Text Box

Select the whole text.
Right-click the text and select Paragraph.

In Paragraph, select Spacing.
Choose different line spacing options.
Click OK.

Here, 6pt was selected.

7. Rotating the Text Box

Select the text box and you will see a clockwise rotating icon at the top of the box.
Click it and move your mouse cursor to rotate the text box.

Observe the GIF.

8. Resizing the Text Box to Fit Text

Resizing Text Box Automatically to Fit:

Select the text box by clicking its edge.
Press Ctrl+1 to open the Format Shape option.
Select Text Options >> Textbox
In Text Box, check Resize shape to fit text.

Click on the image to get a detailed view

Height and width will be adjusted to match the text.

Click on the image to get a detailed view

Resize to Fit and Wrap:

Select Wrap text in shape.
Height will be adjusted and width will be kept.

Click on the image to get a detailed view

You manually change its width.

Resize to Fit Using VBA:

Open the VBA editor and insert the following code in the module.

Sub FitTextBoxToText()
    Dim ws As Worksheet
    Dim textBox As Shape
    
    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("8. Resize to Fit Text")
    ' Replace "YourSheetName" with your actual sheet name
    
    ' Set the name of the text box
    Dim textBoxName As String
    textBoxName = "TextBox 1"
    ' Replace with the actual name of your text box
    
    ' Check if the text box exists
    On Error Resume Next
    Set textBox = ws.Shapes(textBoxName)
    On Error GoTo 0
    
    If Not textBox Is Nothing Then
        ' Fit the text box to its content
        textBox.TextFrame.AutoSize = True
    Else
        MsgBox "Text box not found!"
    End If
End Sub

Click on the image to get a detailed view

Press F5 to run the code. It will resize the text box.

Click on the image to get a detailed view

9. Aligning Text Boxes in Excel

Aligning Text Boxes Horizontally:

Select all text boxes by holding Ctrl and left-clicking them.

Select Shape Format >> Align Objects >> Align Top.

align top option for aligning text boxes

Click on the image to get a detailed view

The boxes will be horizontally aligned.

Aligning Text Boxes Vertically:

Select all text boxes.
Select Shape Format >> Align Objects >> Align Left.

align left option for vertical alignment

Click on the image to get a detailed view

This will align the text boxes vertically.

Click on the image to get a detailed view

Note

10. Changing the Transparency of a Text Box

A text box can cover the cell values it is displaying.

To change the transparency of the text box:

Select the text box by clicking its edge.
Press Ctrl+1 to open the Format Shape window.
Select Shape Options >> Fill & Line >> Fill.
Change the Transparency level in Color.

Click on the image to get a detailed view

11. Anchoring a Text Box in Excel

A text box is usually attached to a cell. So changing the column width or row height also changes the size of the text boxes in it.

To anchor a text box to a cell:

Select the text box by clicking its edge.
Press Ctrl+1 to open the Format Shape window.
Select Shape Options >> Size & Properties >> Properties.
Check Don’t move and size with cells.

Click on the image to get a detailed view

Lock a Text Box:

Go to Home >> Cells >> Format >> Protect Sheet.

Click on the image to get a detailed view

Uncheck Edit objects in Protect Sheet and click OK.

The text box won’t be resized by changing the size of a column or row.

You need to unprotect the sheet to edit the text box again.

How to Copy a Text Box in Excel

Select the text box by clicking its edge.

Press Ctrl+C to copy and Ctrl+V to paste.

How to Get a Text Box Linked to a Cell in Excel

Create a text box.
Click the middle of the box. Instead of entering text there, use the formula bar.
Enter “=” followed by the cell you want to reference to. Here, =$C$16 to link to C16.

Press Enter. The value will be displayed in the text box.

Double-click the text box and enter your text.

Read More: Get a Text Box Linked to Cell

How to Create a Dynamic Text Box in Excel

Select a cell to insert a location and go to Data >> Data Tools >> Data Validation.

In Data Validation, select Settings.
Select List in Allow and enter the range in Source. Here, source data is B6:B13.

Click on the image to get a detailed view

A drop-down arrow will be displayed in the cell. Choose a value.

Enter the following formula in C17.

=VLOOKUP(C16,B6:F13,4,TRUE)

Insert a text box, select it and link it to the cell by entering =$C$17 in the formula bar.

Read More: Add a Dynamic Text Box

How to Create a Floating Text Box in Excel

Open the VBA window.
Select Insert >> UserForm.

In Toolbox, select TextBox and drag and drop your text box.

Set Multiline as True and choose 2 in the ScrollBars option.

Create a Module and enter the following code.

Sub floating_text_box()
UserForm1.TextBox1.Text = "Monthly sales of accessories in different branches of XYZ company"
UserForm1.Show vbModeless
End Sub

vba code for floating text box in module

Click on the image to get a detailed view

Press F5 to run the code. The floating text box will be displayed.

Enter your text in the text box , like in an ActiveX text box.

Click on the image to get a detailed view

Read More: Create Floating Text Box

How to Add Hyperlinks to a Text Box in Excel

Insert a text box.

Add formatting to the box (optional).

Right-click the text box and select Link.

Choose a place for the link in Insert Hyperlink. Here, Place in This Document in Link to: was selected and a named sheet was chosen.

Click OK.

The text box will work as a button. Clicking it will take us to the homepage.

Note

Use Ctrl+Left click to select a text box with the hyperlink. A simple left click will take you to the linked location.

Read More: Anchor Text Box in Excel

How to Apply Conditional Formatting to a Text Box in Excel

Use ActiveX text boxes:

Create an ActiveX text box.
Select the box and rename it in Name Box. Here, “ConditionalTextBox”. (The Design Mode in Controls must be active)

Right-click the sheet name and select View Code.

Insert the following code in the editor.

Private Sub ConditionalTextBox_Change()
If ConditionalTextBox.Value < "0" Then ConditionalTextBox.BackColor = rgbBlack
If ConditionalTextBox.Value < "0" Then ConditionalTextBox.ForeColor = rgbWhite
If ConditionalTextBox.Value > "0" Then ConditionalTextBox.BackColor = rgbWhite
If ConditionalTextBox.Value > "0" Then ConditionalTextBox.ForeColor = rgbBlack
End Sub

vba code for text box with conditional formatting in module

Click on the image to get a detailed view

Close the VBA editor.

The color will change to white if the value is positive and to black if zero or negative.

Note

If the text box is blank or has non-numeric values, the fill also turns black.

How to Convert Text Box Content into Cell Content in Excel

Open the VBA Editor and insert a Module.
Enter the following code.

Sub ConvertTextBoxToCell()
    Dim Sh_xRg As Range
    Dim Sh_xRow As Long
    Dim Sh_xCol As Long
    Dim Sh_xTxtBox As textBox
    Set Sh_xRg = Application.InputBox("Select a cell):", "Convert Text Box to Cell ", _
                       ActiveWindow.RangeSelection.AddressLocal, , , , , 8)
    Sh_xRow = Sh_xRg.Row
    Sh_xCol = Sh_xRg.Column
    For Each Sh_xTxtBox In ActiveSheet.TextBoxes
        Cells(Sh_xRow, Sh_xCol).Value = Sh_xTxtBox.Text
               Sh_xRow = Sh_xRow + 1
    Next
End Sub