Microsoft Excel: How to link text boxes to data cells

Q. Is it possible to link the contents of a text box in Excel to data in a cell?

A. Yes, you can link the contents of an Excel text box to data in a cell as follows:

1. Insert a text box. Insert a text box in Excel from the Insert tab by selecting Text, Text Box, and then use your mouse to drag to a region on your worksheet.

2. Insert a formula in the text box. With the Text Box still selected, press the F2 key. This will activate the formula bar (indicated by a blinking cursor in the formula bar). Write your desired formula (in the example shown above, I entered the formula =B2) and press Enter. Thereafter, the text box will display the data contained in the linked cell (405,843 in this example), even if the contents of that cell change.

Additional comments about text boxes:

a. The linked text box will continue to work even if you move it to another worksheet or to another workbook.

b. The linked text box will work even if you create the link in a different worksheet or workbook.

c. This type of link also works with other shapes, such as the four example speech bubbles pictured below. (In this screenshot, I also applied a few of the available formatting effects involving colors, shadows, bevels, and glow effects.)

d. With a little tech-savvy trickery, it is possible to combine text and numbers to embed a full sentence in a text box or speech bubble. For example, below in cell A4, I have used the Concatenate symbol (the “&” symbol) to combine both text and numbers. I also used the Text function to control the formatting of the values included in the sentence. I then embedded the resulting formula in the speech bubble and included a clip art image of myself. The result is an image with a speech bubble that changes automatically as the values in the worksheet change.

This type of effect may be useful in certain situations to help emphasize key results produced in a workbook. You can download this example workbook at carltoncollins.com/textbox.xlsx.

About the author

J. Carlton Collins (carlton@asaresearch.com) is a technology consultant, a CPE instructor, and a JofA contributing editor.

Note: Instructions for Microsoft Office in “Technology Q&A” refer to the 2007 through 2016 versions, unless otherwise specified.

Submit a question

Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.