Tom’s Tutorials For Excel: Linking a Drawing Object to a Cell
Here’s how you can link a cell’s value to be displayed inside a drawing object. In this example, a Text Box is being used, but this method will also work for Rectangles and other draw-able Forms shapes.
In Excel version 2003, find the Text Box icon on the Drawing toolbar by clicking View > Toolbars > Drawing to show that toolbar, then click onto the Text Box icon as shown.
For Excel versions 2007 or after, click onto the Insert tab, click the Shapes button, and click onto the Text Box icon as shown.
As you see in the following pictures, draw the shape onto your worksheet, and select it. In the formula bar, enter a formula such as =A1
, or whichever cell whose value you want the shape to show.
Whether the linked cell (A1 in this example) is meant for manual data entry, or it holds a formula, the shape will also show that cell’s contents.
There’s a function that is “not in the ribbon” called ‘Camera’ which pretty much does the same thing as this. You put it on your quick access bar, first you highlight an area you want to photograph then click the camera. Then, wherever you click next is where your “picture” will be placed. You can put it on another tab, anywhere within the workbook. It comes in handy when you want to see the result of a change you’ve made on another tab without having to leave your work. You don’t have the option of many different shapes, but it’s really quite handy.
Depending on how long the on-screen information is needed, the Watch Window, available from the Formulas tab, can also provide real-time view of selected values.
That said, nothing like having multiple ways of doing the same thing, each with their own application.
* The formula-in-shape works well when laying a shape over a chart to give information not in the worksheet.
* The Camera give a great read-only view of an area of a worksheet like an executive might want to see
* The Watch Window helps when trying to see the impact of a change in data or formula in another worksheet or some place otherwise not on the screen