First, What Do We Mean By "Linking"?

Linking in this context does not mean hyperlinking. (To do that, just right-click on a shape and select the Hyperlink option from the context menu.) Instead, we mean that the shape's text is bound to a worksheet cell, so that when the cell's content changes the text in the shape is updated automatically.

How To Bind A Shape's Text To A Worksheet Cell

Picture 1, below, shows three items circled in red - cell P7, a shape, and the formula bar. Normally, when a shape is selected, the Excel formula bar is empty. To link the shape to the The Excel formula bar contains the formula ‘=P7’. In Cell P7 containing the text ‘Step 2’.

Common Issues

Cell Formatting: When a shape is first linked to a cell via formula, the shape font formatting is suddenly changed to match the formatting of the cell. To fix it, you will need to select the shape and change the font format using the Excel formatting tools. Starting with Excel 2007, once the shape's format is changed back, subsequent changes to the linked cell do not affect the shape again. However, in older versions of Excel (2000-2003), the behavior is inconsistent and any changes to the cell content can cause the shape font to change again.

Complex Formulas: In Picture 1, the formula is simple - ‘=P7’. But what if you want the shape's text to be a combination of two or more cells? Or, perhaps you want the text to be conditional upon an Excel formula. With shapes, the formula can link only to a single cell. So if you want a complex formula, you will need to put the formula in a cell, and then link the shape to the cell containing the formula. For example, in Picture 2, below, the shape formula is still ‘=P7’, but now cell P7 itself contains the formula shown in the picture - ‘=IF(P6<=250, "Step 2A", "Step 2B")’.

Is There A Faster Way?

If you have a flowchart with a lot of shapes, wiring up the formulas to link each shape to a cell can be very time consuming. The Extract Text tool in FlowBreeze, our flowchart automation add-in for Excel, will grab all the text entries in the flowchart shapes, output them to the worksheet, and optionally link the shapes to their respective cells. You can use Excel's Cut, Copy, and Paste tools to move the text to different cells and all the shapes' link formulas will update automatically.

---

About the Author