Dynamic Text Boxes

Written by Mynda Treacy (last updated March 5, 2021)
This tip applies to Excel 2007, 2010, 2013, and 2016


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, 2013, and 2016.

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

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

Sizing Text Boxes and Cells the Same

Adding a text box to a worksheet is easy. Making sure that text box is the exact size of a cell in the worksheet may not ...

Discover More

Resizing a Text Box in a Macro

Text boxes are easy to add to a worksheet and manually resize, as needed. If you want to resize the text box in a macro, ...

Discover More

Adding a Drop Shadow to a Text Box

One way to make your text boxes "stand off" the page is to add a drop shadow to them. This tip shows just how easy it is ...

Discover More

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

View most recent newsletter.


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}] (all 7 characters, in the sequence shown) 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 two more than 7?

2022-01-21 07:12:44

Raymond Ong A Kwien

a couple of years ago I used Excel also to transfer a lot of information by means of textboxes in cells which contain a lot of text. In order to make the text good readable I was able to configure the textbox in such a way that one could scroll the text in the texbox up and down. However, I have not used this feature for many years now, and I wonder If you could refresh my memory in order for me to use this again.
Many thanks in advance,

Raymond Ong A Kwien

2020-07-19 18:10:34

Genni Tucker

Hi Mynda,

This tip is working beautifully for an excel dashboard I created, except that when I reopen and refresh the source data for the report, the text box formulas don't update. They only reflect the updated figures when I double-click on each text box, which then loses the text surrounding the updated figure as well as the formatting/font. Any idea how to fix this so I don't need to click on dozens of text boxes, reformat, and re-type the text surrounding the updated number?

Thank you,

2020-05-18 07:50:54

Sam Swartz

Thanks Peter. I was trying to avoid using select/selection. I found a work around using a direct property of the shapes object:

Worksheet("Sheet1").Shapes("NameOfShape").DrawingObject.Formula = "=TypeFormulaHere"

My actual macro, used for updating a dashboard based on certain data from each weekday:

Sub UpdateDashMonday()

'This only updates one weekday, need to use other days' macros respectively.
Dim db As Worksheet, vRow As Long, i As Long

Set db = ThisWorkbook.Worksheets("Dashboard")
vRow = 4

For i = 1 To 13

db.Shapes("FV" & i & "Label").DrawingObject.Formula = "=Monday!B" & vRow
db.Shapes("FV" & i & "Extract").DrawingObject.Formula = "=Monday!D" & vRow
db.Shapes("FV" & i & "pH").DrawingObject.Formula = "=Monday!E" & vRow
db.Shapes("FV" & i & "Status").DrawingObject.Formula = "=Monday!F" & vRow
db.Shapes("FV" & i & "Temp").DrawingObject.Formula = "=Monday!I" & vRow

vRow = vRow + 1

Next i

End Sub••••ˇˇˇˇ

2020-05-18 07:05:04

Peter Atherton

Here is a better Delete macro that only deletes existing textboxes. The previous procedure deleted Buttons (formControls)

Sub deleteTextBoxes()
'Deletes TextBoxes
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
If shp.Type = 17 Then
End If
Next shp
End Sub

2020-05-17 07:18:36

Peter Atherton

Here is another macro that allows you to delete all previous text boxes (but not charts). So two macros really!

Sub AddTextBox2()
Dim response
response = MsgBox("Do you want to delete all shapes from sheet", vbOKCancel, _
"Charts will not be deleted")
If response = vbOK Then
End If

ActiveSheet.Shapes.AddTextBox(msoTextOrientationHorizontal, 30, _
50, 100, 25).Select
' numbers= position from Left, Top of sheet _
shape Width, Height
With Selection.ShapeRange
.Fill.ForeColor.RGB = RGB(220, 220, 250)
.Line.Weight = 2.25
.Line.ForeColor.RGB = RGB(255, 192, 0)
End With
With Selection
.ShapeRange.TextFrame2.VerticalAnchor = msoAnchorMiddle
.Formula = "=$E$1"
.Font.Bold = True
End With
End Sub

Sub deleteDrawingObjects()
'Deletes all shapes but charts
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
If Not shp.Type = msoChart Then
End If
Next shp
End Sub

2020-05-16 06:14:28

Peter Atherton

Try this link

Ot this - works with existing textbox and sets a link to cell and formats Fill then text.

Sub RefTxtBx()
ActiveSheet.Shapes.Range(Array("TextBox 1")).Select
Selection.Formula = "=E1"
With Selection.ShapeRange.Fill
.ForeColor.RGB = RGB(192, 0, 0)
End With
With Selection.ShapeRange.TextFrame2.TextRange.Font.Fill
.ForeColor.ObjectThemeColor = msoThemeColorBackground1
Selection.ShapeRange.TextFrame2.TextRange.Font.Bold = msoTrue
End With
End Sub

2020-05-15 08:05:41


Dp you know how to reference the text boxes from a macro coded through VBA?

2019-03-25 17:29:37


I imagine Shari has solved this by now, but for anyone who needs it, you can remove the "$" absolute referencing in the result of =CELL("address:) in the standard way by wrapping it in SUBSTITUTE():


Separately, using Excel 2019, I permanently lose the ability to format the text box AFTER linking it.

2018-03-19 10:56:46

Neeraj Shah

Thank you Mynda. This was helpful. It helped automate one of tasks and gave me back my 15 minutes every week :)
Keep it up.

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


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

2016-07-29 12:05:34


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


Thank you this was very clear and helpful!

2016-04-12 19:38:12


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


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


Hi Simon,

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


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


Hi Mark,

What version of Excel are you using?


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


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


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


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?


2014-09-18 17:04:57


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

2014-05-07 05:10:40


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


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


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

- Terry

2013-07-28 20:28:49


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


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

2013-07-28 08:43:58


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

2013-07-28 03:32:15


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.

2013-07-27 15:08:33



Check here:



2013-07-27 15:03:05


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


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

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.