Creating Dynamic Text Box entries in Excel

Flexible text

Text boxes are flexible because you can place them anywhere on your spreadsheet.

You can link a text box to the contents of a cell.

(The link to the example file is at the end of the post.)

First draw a text box on your sheet.

Then when the text box is selected click in the Formula bar and type = then click on the cell to link to. You can link to a cell in other sheets. Changing the cell contents will change the text box contents. See image below.

This allows you to display dynamic messages around your spreadsheet. You can use IF functions in the formula in the linked cell to create a dynamic text message.

The formula in cell A5 in the above image is

=IF(B1<0,A3&TEXT(-B1,"$#,###"),A2&TEXT(B1,"$#,###"))

The TEXT function formats values to use as text.

This technique is handy for dashboards where you may want to display an important KPI result in a large font size.

You can also use it to display validation messages eg “Journal is out of balance”.

Dynamic Text Box Example Excel file

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

4 thoughts on “Creating Dynamic Text Box entries in Excel

  1. A very handy technique Neale – as you have commented this is a great way of collecting important results from many sheets to give a dashboard presentation on a summary sheet.

  2. Thanks Glyn Yes text boxes provide an flexible approach to layout. If you want to line up the text boxes to the grid lines you can hold the Alt key whilst you move it.

  3. This was really nice. Small things such as this makes the overall presentation good. Thanks Neale.