Microsoft Excel: Place Cell Contents In An Autoshape

Problem: Rather than static text in an AutoShape, you want to display the results of a calculation in the Shape, as shown in Fig. 1422.

Strategy: This is possible, although the method shown in Fig. 1422 is not the way to do it.

1) Select the AutoShape. Click in the formula bar; type =A1, as shown in Fig. 1423. Hit Enter.

2) Format to center and enlarge the text. Right-click the dots surrounding the shape, and then choose Format AutoShape. Use a large, bold font. Center horizontally and vertically on the Alignment tab, as shown in Fig. 1424.

Additional Information: The formula in the formula bar can only refer to a single cell. You cannot enter a formula in the formula bar for an AutoShape. 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.

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 Cell wrap is turned on. Otherwise, you will get a square character, as shown in Fig. 1425.

2) Draw a banner. Select the banner and enter =D2 as the formula for the banner. As shown in Fig. 1426, format the banner to be centeraligned with an interesting font. (This is ParkwayResortHotel from my friends at the Chank! Foundry).

3) Finally, move the banner so that it covers the formula in D2. As new orders are entered in the log, the total will update, as shown in Fig. 1427.

Gotcha: The text in the AutoShape is only updated when the worksheet is calculated.

Summary: This technique will allow you to put the results of a cell into an AutoShape.

Functions Discussed: =CHAR(); =TEXT()

See all Microsoft Excel tips

Images