Excel: Cell Values as the Source for SmartArt Content

Problem: As discussed in "œPlace Cell Contents in a Shape" on page 567, Excel has been able to use values from an Excel cell as the source for text boxes on AutoShapes for fifteen years. It would be obvious to anyone that the best use of SmartArt would be to populate the text pane with cell references. However, nothing I try allows me to specify cell A1 as the source in the text pane. What's going on?

Strategy: Amazingly, Microsoft did not hook up this feature in Excel! It was obvious to you, and it was obvious to me, but Microsoft didn't think to include it.

From Microsoft's point of view, SmartArt is primarily a PowerPoint feature that is also available in Word and Excel. Heck, in PowerPoint, Microsoft even made the Convert Any Text to SmartArt functionality. But because PowerPoint doesn't offer cells and formulas, it was not a priority to enable this feature in Excel. Luckily, I have a workaround.

Follow these steps to build a SmartArt graphic that is tied to cell values:

1. Build a SmartArt graphic with the correct number of shapes. Type sample text of about the correct length in the shapes.
2. Choose a color scheme from the Design tab.
3. Choose a style from the Design tab. Get the diagram looking exactly as you will want it to appear, because after step 4, Excel will stop automatically formatting the SmartArt.

1. Build SmartArt with sample text of the right length.

   

2. Convert the SmartArt to regular shapes.
   4. On the Design tab, choose Convert to Shapes.
   5. Click on the first shape and look in the Name box to the left of the formula bar. If you see a name like Group 9, you know that Excel has grouped multiple shapes together. From the Drawing Tools Format tab, choose Group, Ungroup.
   6. Click on the words in the first shape. You should see a name such as Rounded Rectangle 5.
   7. Click in the formula bar. Type a formula such as =J28 and press Enter. You should see the text from J28 appear in the shape.
   8. Repeat steps 5 through 7 for the additional shapes.
   9. Select Home, Find & Select, Select Objects. Drag a rectangle around the collection of shapes to reselect them all. You need to exit Select Objects mode, so reselect Home, Find & Select, Select Objects.
   10. From the Drawing Tools Format tab, choose Group, Group in order to group all the objects into a single unit again.

   Results: Excel will create a diagram that looks like SmartArt that will get the values from formula in cells J28:J30.

   

3. This looks like SmartArt but is really shapes.