Microsoft Excel: How to link text boxes to data cells

By J. Carlton Collins, CPA

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.

techqa-1


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.)

techqa-2


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.

techqa-3

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.

SPONSORED REPORT

Supercharge your audit process with AI

Auditors today can employ AI to automate tedious tasks and gain far greater insights from their clients’ information. This free report lays out a five-step process for implementing AI and shows ways AI can add value to the auditing process.

RESOURCES

Keeping you informed and prepared amid the coronavirus crisis

We’re gathering the latest news stories along with relevant columns, tips, podcasts, and videos on this page, along with curated items from our archives to help with uncertainty and disruption.