Overview
In Excel, you can link the text displayed in shapes (such as rectangles or text boxes) to cell values. When the cell is updated, the display within the shape changes automatically. This article explains the setup procedure using the formula bar and provides practical tips for concatenation and formatting adjustments useful in business contexts.
Basic Procedure
Let’s use an example of “displaying the text string from cell A1 in a shape.”
- Enter text (e.g., “Forest”) in cell A1.
- Select the shape where you want to display the text (e.g., a rectangle from the Insert tab).
- With the shape selected, click inside the Formula Bar, type
=A1, and press Enter. - The text from A1 appears in the shape. If you change A1 to “Woods,” the shape automatically updates to “Woods.”
Key Points
- To reference another sheet, write it as
=Sheet1!A1. - Important: If you edit the text directly inside the shape, the link will be broken. To fix this, select the shape again and re-enter
=Referencein the formula bar. - You can link multiple shapes to the same cell to synchronize them all at once.
Advanced Techniques
Concatenating with Fixed Text
If you want to add a label before a name, create a concatenation formula in the cell and link the shape to that cell.
- Example: Enter
="Name: " & A1in cell B1, then set the shape to=B1.
Including Line Breaks
Create a string containing a line break within the cell formula.
- Example: Enter
=A1 & CHAR(10) & "Dept: Sales"in cell B1. - Enable “Wrap text” for the cell B1. The shape will also display the line break.
- (Note: Manual line breaks are
Alt+Enteron Windows,Control+Option+Returnon Mac.)
- (Note: Manual line breaks are
Formatting Numbers and Dates
Shapes display the raw calculation result of a cell. To control the visual appearance, use the TEXT function to convert the value to a string.
- Example: Enter
="Updated: " & TEXT(A1,"yyyy/mm/dd")in cell B1, then set the shape to=B1.
Referencing Other Sheets or Workbooks
- Other Sheet:
=Sheet2!A1 - Other Workbook:
=[estimate.xlsx]Sheet1!A1- Note: Be careful with file path changes or file name changes for the source workbook.
Preventing Text Cut-off
For long text, check the Format Shape settings under Text Options > TextBox. Using “Resize shape to fit text” helps prevent text from being cut off.
Common Pitfalls and Solutions
Formula Bar is Missing
Go to the View tab and check the box for Formula Bar to display it.
Link Disconnected
Directly editing the text inside the shape breaks the link. To restore it, select the shape and re-enter =Reference in the formula bar.
Shows Error or Blank
Check if the reference cell is empty or contains an error. Use IFERROR to provide an alternative display.
- Example:
=IFERROR(A1, "Not Set")
Format is Incorrect
Do not rely on the cell’s visual formatting. Use the TEXT function to finalize the display string within the cell formula for stability.
Not Reflecting in Some Objects
Use standard shapes or text boxes. This method may not work with text inside complex objects like SmartArt.
Usage Examples
- Name Tags/Cards: Update template displays simply by switching the reference cell in a roster.
- Dashboards: Switch emphasis labels or headings based on calculated cell results.
- Gantt Charts: Link labels on process charts to assignee or date cells.
Operational Tips
- Complete Formatting on the Cell Side: Perform all concatenation and formatting in the cell formula so the shape only refers to the final result. This makes maintenance easier and prevents display issues.
- Intermediate Cells: Create a dedicated intermediate cell for the text generation. Linking the shape to this specific cell clarifies the logic and reduces troubleshooting.
- Synchronization: Referencing the same cell from multiple shapes allows for easy global updates.
Summary
The basic method to link a string display to a shape is simply selecting the shape and typing =ReferenceCell in the formula bar. By preparing fixed text, line breaks, formatting, and external references within the cell’s formula beforehand, the shape will always display the latest finalized text.
This technique works well with templates and contributes to efficiency in various scenarios, such as creating name tags, building dashboards, and annotating process charts.
