Dynamic Text Boxes

by Mynda Treacy
(last updated October 11, 2016)

27

One of the great features of an Excel Dashboard report is that they are typically interactive, in that they enable the reader to choose different ways to filter the data. As a result it's important that the labels in the report reflect the selections made.

For example, in my Tour de France dashboard below I have a few different ways to filter the data, one is to toggle between viewing the results for 'All' riders or just the 'Top 10'. This is done by selecting from a Combo Box list. (Tip: Drop-down lists, a.k.a. data validation lists, work just as well as Combo Box lists.)

Notice how the label 'Avg. Speed Km/h' updates to include 'All' or 'Top 10'as the different selections are made: (See Figure 1.)

Figure 1. Example dashboard in Excel.

I created these dynamic labels using text boxes that are linked to a cell which contains the dynamic text (more on dynamic text in a moment). The reason I like to use text boxes is that they can be positioned anywhere in your workbook and don't have the constraints of a cell. This is because they're actually an object that hovers above your worksheet just like charts and images.

Inserting Text Boxes

You'll find text boxes on the Insert tab of the ribbon in the Text group. Simply click the Text Box tool and hold the left mouse button while you drag to draw it on your worksheet. (See Figure 2.)

Figure 2. A portion of the Insert tab of the ribbon with the Text Box tool marked.

Linking Text Boxes to Cells

In order to link the text box to a cell you need it to be active. You can tell the text box is active because it will have small square boxes/circles on the outer edges. (These are the handles that allow you to resize the text box.) If the text box is not active, simply click on it once to select it. (See Figure 3.)

Figure 3. A text box is selected when you can see handles around it.

Now, while the text box is selected click in the Formula bar and type an = sign, then click on the cell you want to link to and press Enter to confirm. (Note: your linked cell can be any worksheet in your workbook.)

You can see in the image below that my text box is linked to cell D1 which contains a formula that combines the words "Year to Date" with the text in cell B1 (which is a data validation list in which the user can select a month). (See Figure 4.)

Figure 4. This text box has been linked to the value in a cell.

As I select a different month from the data validation list in B1 you can see both the result in cell D1 and my text box dynamically update. (See Figure 5.)

Figure 5. As information is changed, the text in the text box changes dynamically.

Of course, in your report you would have your data in cell D1 tucked away on a different worksheet so that the reader only sees the data validation list and the text box label.

(Editor's note: Mynda teaches a great Excel Dashboard course in which you can learn how to combine tricks like these to create your own dynamic dashboards.)

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (5832) applies to Microsoft Excel 2007, 2010, and 2013.

Author Bio

Mynda Treacy

Mynda Treacy is co-founder of My Online Training Hub, author of their comprehensive Excel Formulas list, and popular Excel Blog. ...

MORE FROM MYNDA

10 Commandments for Excel Charts

Excel makes creating charts easy. Even though it is easy, you still need to exercise prudence in making sure that your chart ...

Discover More

Linked Combo Boxes

Want to add a great way to interact with your worksheet users? Try adding combo boxes that can modify the information shown ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Finding Text in Text Boxes

Want to search for text that may appear in a text box rather than in a regular worksheet cell? You can only perform the ...

Discover More

Placing Textbox Text Into a Worksheet

Want to get rid of your text boxes and move their text into the worksheet? It's going to take a macro-based approach, ...

Discover More

Using Text Boxes

Text boxes are handy for placing information in a container that can "float" over your worksheet. This tip explains what text ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is four less than 9?

2017-01-21 00:55:22

Nathanael Morrow

Is there supposed to be a limit to the amount of text from a cell that a text box will display? I have an entire paragraph I am trying to display, and it gets cut off part way through the paragraph. Any help you could offer would be awesome.


2016-08-07 22:51:53

Francis

Very useful - just what I needed to get me heading in the right direction


2016-07-29 12:05:34

Robert

Mark, I know this is late, but perhaps for the benefit of others ...

The problem is you're typing your formula (your equal signs) in the text box itself. This is incorrect. You're meant to click the text box to make it active, *then* type the formula (equal sign) in the *formula bar*, the area above the actual spreadsheet cells, and below the ribbons.


2016-06-23 14:06:23

Karen

Thank you this was very clear and helpful!


2016-04-12 19:38:12

JeteMc

Thank You for the tip. I tried to combine/concatenate the text box formula as shown in figure 4, however I kept getting error messages. I am using Excel 2010.


2016-03-09 12:23:29

Robert Halverson

Great tip, so Simple, Who Knew? Thanks


2016-01-22 16:20:53

Shari

I have a cell that contains a statement and upper management would like to reference a cell within the statement. For example: "If Cell I27 does not match..." and if a row is added or deleted, then cell I27 would change to I26 or I28. I have tried Cell("address") and I can reference the cell in the statement, but it shows as $I$27 and they do not want to see the absolute dollar signs, any suggestions?


2016-01-07 18:22:02

Mynda

Hi Simon,

Text boxes are finicky like that. You'll just have to reapply the formatting after linking it to the cell.

Mynda


2016-01-07 13:11:44

Simon Tildesley

Trying to update an existing text box. Followed the steps below. Cell content is being copied but losing the original text box formatting. Any idea why?


2015-10-22 19:43:31

Mynda

Hi Mark,

What version of Excel are you using?

Mynda


2015-10-22 11:55:36

Mark Lovell

I followed the steps above:
- Add a Text Box
- Ensure the Text Box was active
- Type =
- Click on a cell... At that point, the cell Text Box becomes inactive, the cell becomes active and Text Box does not display the contents of the cell.


2015-08-07 16:50:02

John Doe

I am using the CONCATENATE function in Excel to take text in three separate cells, combine them and deposit them in a forth cell. The result = "The Jones Family".

I then have to upload the spreadsheet in a specific format to a website. The format allows only one column for the customer's name - "The Jones Family". If, however, I delete the initial columns in my original spreadsheet, "The Jones Family" loses its reference.

Any suggestions?


2015-06-08 14:33:09

Patrick

I have a range of cells with data that changes based upon user input. I can't seem to get the text box to display the range of data. It only displays the first cell referenced. any suggestions?


2015-04-16 06:13:07

Alex

I have a load of text in a cell that I need to display in a text box on a different sheet.
My formula of ='project Scope - Notes'!A1 only shows the first 7 lines, the last of which is cut off after 19 characters.

If I put the exact same formula into a cell, or a set of merged cells it displays all the text. All 31 lines.

Any idea why it is truncating the text or how I can make it show it all?

I have currently got round it by merging a load of cells but I want the user to be able to resize the text box to fit as I have second text box under it?

Much Obliged


2015-04-09 09:18:58

Rizwan

Dear All,
I need technical help in preparing the attendance sheet in excel.
I’ve already prepared a sheet in which when I change the month from the drop down list, the dates & days are automatically updated.
The problem is that when I change the month, the dates & days are automatically updated but the columns (or table) associated with these days are not updated. E.g. in the month of January, 2015; 4th day was Sunday, means it’s a weekend so I put “H” (Holiday) in all the cells under 4th January, 2015 but when I change the month to February, 2015; the 4th day was Wednesday but the cells under 4th Feb, 2015 is showing “H” (Holiday), which is the value of month of January.
Is it possible that when I change the month, the columns under the days/dates are also updated and most importantly I can change the value as per requirement?

Thanks,


2014-09-18 17:04:57

Rick

Huge, huge help! Thanks for the info! Cheers!


2014-05-07 05:10:40

Barry

@David
I think you might be trying to enter the "=K2" formula directly into the text box itself which just accepts this as text input rather than a formula. What you need to do is select the text box and enter the formula (=K2)into the formula bar not the text box.


2014-05-06 09:01:54

DavidFunderburke

For some reason I cannot get this to work. I use Excel 2010. When I enter = within the active text box and then click on the cell I want to show in the box, focus goes to the other cell, and all that remains in teh text box is the "=" I entered. If I type in the cell reference as =k2, that text appears in the text box, and not the value from cell K2.


2013-07-29 18:55:33

Terry

Wow! Great tip, and even better way of presenting and explaining it!

- Terry


2013-07-28 20:28:49

Rob

Drop-down lists are created by selecting "Data Validation", on the "Data" ribbon.

In the settings tab, Allow: List, and in Source: type the items you want in the list, separated by commas. Make sure you tick the "in-cell dropdown" option, and the "Ignore blank" option will depend on your use.


2013-07-28 13:58:50

Juan

Excellent tip, I didn't know that, amazing!


2013-07-28 08:43:58

Bryan

Mynda, I didn't know you were writing for tips.net! I'm excited to see more!


2013-07-28 03:32:15

Zaigham

Awesome :)


2013-07-27 20:17:51

Bob Davey

Mynda is showing us just how a tutorial should be written. No unneceassary fantasy third party required, easy to read & absorb language, some interactive graphics and bingo, informative and well structured. We could have more of this.
Bob.


2013-07-27 15:08:33

awyatt

Tony,

Check here:

http://excelribbon.tips.net/T006191

-Allen


2013-07-27 15:03:05

Tony

This is good but I've forgotten how to create the drop-down list in the first place. Help!!


2013-07-28 23:08:46

Mynda

Juan, Bryan, Zaigham and Bob, thank you! I'm glad you like my tip :-)


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.