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.
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.
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.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
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 MoreText boxes are handy for placing information in a container that can "float" over your worksheet. This tip explains what ...
Discover MoreWant to search for text that may appear in a text box rather than in a regular worksheet cell? You can only perform the ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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,
Genni
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
shp.Delete
End If
Next shp
End Sub
2020-05-17 07:18:36
Peter Atherton
Sam
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
deleteDrawingObjects
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
[a1].Select
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
shp.Delete
End If
Next shp
End Sub
2020-05-16 06:14:28
Peter Atherton
Sam
Try this link
https://www.thespreadsheetguru.com/the-code-vault/2014/3/23/reference-a-worksheet-textbox
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
[A1].Select
End Sub
2020-05-15 08:05:41
Sam
Dp you know how to reference the text boxes from a macro coded through VBA?
2019-03-25 17:29:37
Roy
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():
=SUBSTITUTE(CELL("address"),"$","")
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
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 :-)
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments