• Excel Book Excerpt

Excel Place Cell Contents in a Shape

This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Place Cell Contents in a Shape

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.

LE10001357.jpg 

Figure 1295 Can a shape display a value from a cell?

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.

LE10001358.jpg 

Figure 1296 Type = and a cell reference in the formula bar.

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.

LE10001359.jpg

Figure 1297 Build a formula in a cell to concatenate text and a sum.

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.

LE10001360.jpg

Figure 1298 Draw and format a banner.

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.

<-Previous Topic Next Topic->

For more resources for Microsoft Excel: