The dataset showcases a list of products with their sales values, and profits or losses.
Example 1 –Â Linking a Single Cell to a Text Box in Excel
To link a textbox to the total sales value in C13:
- Go to the Insert tab >> Text >> Text Box.
- Drag the symbol shown below to the right and down.
TextBox 1 will be displayed.
Step 2:
Link the value of C13 to the textbox.
- Select the textbox and enter the following formula in the formula bar.
=$C$13
- Press ENTER.
You will see the total sales value in the textbox.
- After formatting, this is the output.
Example 2 –Â Linking Multiple Cells to a Text Box
Link the values of B13 and C13 to a text box using the CONCATENATE function.
Steps:
- Follow Step 1 in Example 1 to draw the text box.
- Enter the following formula in B14.
=CONCATENATE($B$13, " ",$C$13)The CONCATENATE function will join the values of $B$13 and $C$13 with a blank.
- Select the textbox and enter the following formula in the formula bar.
=$B$14
- After pressing ENTER, and formatting, you will see the textbox linked to multiple cell values.
Example 3 –Â Formatting Values of a Linked Cell in an Excel Text Box
Use the TEXT function with the ampersand operator.
Steps:
- Enter the following formula in B15.
="The Total Sales Value is "&TEXT(C13,"$##,##0.00")The TEXT function will change the format of the number in C13 to $##,##0.00 format.
- Follow Step 1 in Example 1 to draw the following text box.
- Select the textbox and enter the following formula in the formula bar.
=$B$15
- After pressing ENTER, and formatting, you will see the textbox linked to multiple cell values with formatting.
Example 4 – Using the IF Function in a Text Box Linked to a Cell for Multiple Conditions
The total value in D13 is the summation of all profits or losses.
To indicate profit as a positive number and loss as a negative number, use the IF function.
Steps:
- Enter the following formula in B15.
=IF(D13<0, "The Loss value is "&TEXT(-D13,"$##,##0.00")," The Profit Value is "&TEXT(D13,"$##,##0.00"))D13<0 is the logical condition. When this condition is TRUE, the loss value is returned, otherwise the profit value.
- Follow Step 1 in Example 1 to draw the following text box.
- Select the textbox and enter the following formula in the formula bar.
=$B$15
- After pressing ENTER, and formatting, you will see the textbox linked to multiple cell values with formatting.
You can hide the row with the formula.
Read More: How to Align Text Boxes in Excel
How to Use the ActiveX Control Text Box to Link a Cell in Excel
Link the total sales value in C13 to the ActiveX Control Text Box.
Steps:
- Go to the Developer tab >> Insert >> Text Box (ActiveX Control).
- Drag the plus icon down and to the right.
TextBox1 is displayed.
- Right-click the sheet name and choose View Code.
In the Visual Basic Editor window:
- Enter the following code.
Sub link_txtbox()
TextBox1.Text = Range("C13").Value
End Sub
- Press F5.
The value of C13 is linked to the textbox.
Practice Section
Practice here.
Download Practice Workbook
Related Articles
<< Go Back to TextBox | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
