Place Cell Contents in a Shape
December 04, 2023 - by Bill Jelen

Problem: I don’t want to use just static text in a shape; I want to display the results of a calculation in the shape.
Strategy: This is possible, although typing the formula in the shape is not the way to do it. Here’s how you do it:
1. Select the shape.
2. Click in the formula bar and type
=A1
. When you press Enter, the value from A1 will appear in the shape.


Additional Details: The formula in the formula bar can refer to only a single cell. You cannot enter a formula in the formula bar. However, there is a workaround. Say that you want to display today’s order total in a banner at the top of an order entry log. The banner will appear in rows 1 through 4 of the log. Here’s what you do:
1. Move the banner out of the way and build a formula in cell D2 to hold the text for the banner. The formula might be:
=”Today’s Order Total:”&CHAR(10)&TEXT(SUM(C8:C200),”$#,##0”)
The CHAR(10)
function will add a linefeed in the result if Wrap Text is turned on. Otherwise, you will get an unprintable character symbol.

2. Draw a banner. Select the banner and enter
=D2
as the formula for the banner. Format the banner to be center-aligned and in an interesting font.

3. Move the banner so that it covers the formula in D2. As new orders are entered in the log, the total will update.
Gotcha: The text in the shape is updated only when the worksheet is calculated.
Additional Details: Say that you add a shape to a chart. If you want the text in the shape to come from a cell, you must precede the cell reference with the sheet name. For example, =Sheet2!D2
will work, but =D2
will not.
This article is an excerpt from Power Excel With MrExcel
Title photo by Salvatore Andrea Santacroce on Unsplash