MrExcel.com - Your One Stop for Excel Tips & Solutions

Add Formulas To Smartart

April 26, 2021 - by

Add Formulas To Smartart

Challenge: For Excel fans, the biggest disappointment with Excel 2007 SmartArt diagrams is that their text is static. You cannot have the text for a SmartArt diagram dynamically calculated by Excel.

Solution: As a workaround, you can use the SmartArt tools to build a diagram and then convert the diagram to shapes. You can then apply formulas to the shapes.

In Figure 78, a database query feeds individual sales figures in columns A:C. SUMIF formulas in G4:G6 show the current sales for each rep. RANK formulas in E4:E6 figure out which rep is in the lead. VLOOKUP formulas in F8:H10 combine an associate’s name and sales total. This report is functional, but it lacks visual interest.

Figure 78. Formulas create a functional but uninteresting report.
Figure 78. Formulas create a functional but uninteresting report.

Follow these steps:

  1. Build a SmartArt diagram that has three shapes. Use dummy text of about the right length. Use the SmartArt tools to format the diagram. In Figure 79, the Format ribbon was used to resize the individual shapes.
    Figure 79. The text is still static text as this point. It is there to help with sizing the boxes.
    Figure 79. The text is still static text as this point. It is there to help with sizing the boxes.
  2. Click inside the SmartArt but not on any shape. Press Ctrl+A to select all the shapes in the SmartArt diagram (Figure 80).
    Figure 80. All the shapes are selected.
    Figure 80. All the shapes are selected.
  3. Press Ctrl+C to copy the shapes.
  4. Click outside the SmartArt and press Ctrl+V to paste the shapes onto the worksheet.
  5. Delete the original SmartArt diagram.
  6. Click the first shape in the worksheet. Drag to select the text in the shape. Click in the formula bar, type =H8, and press Enter. The text in the selected shape changes to reflect the result of the formula in H8.
  7. Repeat step 6 to assign =H9 to the second shape and =H10 to the third shape.

You now have something that looks like a SmartArt diagram, but the text for the shapes comes dynamically from the worksheet (Figure 81).

Figure 81. Now the text in the diagram is a live result from the data.
Figure 81. Now the text in the diagram is a live result from the data.

As the query in A:C updates with new sales, the formulas in E:H and thus the text in the diagram automatically update. While Mary was on a break, Ted made a $395 sale. The worksheet updates as shown in Figure 82.

Figure 82. Excel dynamically calculates the text in this SmartArt.
Figure 82. Excel dynamically calculates the text in this SmartArt.

Summary: Although SmartArt in Excel 2007 cannot dynamically update, you can use SmartArt to create a diagram and then convert it to shapes and dynamic formulas.

Title Photo: Timon Klauser on Unsplash

This article is an excerpt from Excel Gurus Gone Wild.