Place Cell Contents in a Shape
December 04, 2023 - by Bill Jelen
![Place Cell Contents in a Shape Place Cell Contents in a Shape](/img/excel-tips/2023/12/place-cell-contents-in-a-shape.jpg)
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.
![Cell A1 says 9:34 PM thanks to a =NOW() function. Click in the shape and type =A1](/img/content/2023/11/LE10001357.jpg)
![When you press Enter, the value from cell A1 appears inside the shape.](/img/content/2023/11/LE10001358.png)
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.
![A formula in a cell concatenates some text and a formula to show Today's Order Total: $2,495](/img/content/2023/11/LE10001359.jpg)
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.
![Use the formula inside a shape to create a stylish formatted shape with text showing the order total.](/img/content/2023/11/LE10001360.jpg)
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