About MrExcel
Consulting Services
Learn Excel Resources
Challenge of the Month
MrExcel Seminars

Message Board

MrExcel Store


SmartArt in Office 2007

Amber MacArthur, Bill Jelen, Leo LaPorte, Andy Walker on the set of Call for Help on TechTV Canada.

The tip in this show is from Learn Excel from MrExcel.

One of the cool new features in Office 2007 are the business diagramming tools. Microsoft calls these SmartArt.

SmartArt is available in Excel 2007, Word 2007, and PowerPoint 2007. It actually works a little bit better in PowerPoint 2007. In PowerPoint, you can highlight any existing text, right click, and choose Convert to SmartArt.

Office will convert the text to a SmartArt graphic.

SmartArt is available in 84 built-in layouts. There are layouts for cycle charts:

Layouts for picture lists:

Pyramid charts:

Arrow charts

Gear charts:

The big letdown is that Microsoft did not have time to finish one important feature. In the old AutoShapes, there is a trick to have the text in the AutoShape come from a value in a cell. SmartArt does not allow this.

On the show, I mentioned that I figured out a workaround.

1. Create the static SmartArt. 2. Use the Design and Format ribbons to get the graphic as close to finished as possible.
3. Click inside the SmartArt border, but not on any shape in the SmartArt.
4. Type Ctrl+A to select all of the shapes.
5. Ctrl+C to Copy
6. Select a new cell outside of the SmartArt
7. Ctrl+V to paste. You now have an identical copy of the SmartArt, but this version is actually made up of AutoShapes.

Once you have a diagram comprised of old fashioned shapes, you can use the familiar trick to make the text come from another cell.
1. Select the text in the Shape
2. Click in the Formula Bar
3. Type =G45 and press enter. This formula will put the results of the formula in G45 as the text in the shape.
4. Repeat steps 1 - 3 for each additional shape The diagram below was created with these steps. Every time that a new value is calculated in G45:G48, the text in the shape is updated.

SmartArt is a cool new feature in Office 2007. For the BEST TV show on technology, check out Call for Help.

This tip was originally published on August 23, 2006 and aired on TechTV in Canada and Australia on August 23, 2006.

The permanent URL for this page is

If you are looking for show notes from another episode, visit my complete list of TechTV appearances. Consulting can be hired to implement this concept, or many other cool applications, with your data. provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.


Excel is a registered trademark of the Microsoft® Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.

All contents Copyright 1998-2008 by MrExcel Consulting.