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

Get your clients ready for tax season

Upon its enactment in March, the American Rescue Plan Act (ARPA) introduced many new tax changes, some of which retroactively affected 2020 returns. Making the right moves now can help you mitigate any surprises heading into 2022.

100th ANNIVERSARY

Black CPA Centennial, 1921–2021

With 2021 marking the 100th anniversary of the first Black licensed CPA in the United States, a yearlong campaign kicked off to recognize the nation’s Black CPAs and encourage greater progress in diversity, inclusion, and equity in the CPA profession.