Shape Shifting

Shapes are sometimes handy for adding emphasis to a particular number.  For instance, in the sheet shown below, we can use a shape to highlight the total spending on fuel costs for the year in a given region. 

However, we don’t want to have to manually change the value in the shape every time we change the region.  Instead, we can actually link our shape directly to the cell that contains the total.  For instance, in the case above, we could link our shape to cell C22, so that every time C22 changes, the value in the shape changes. 

To do this, we need to have our shape active, but don’t want to type any text into it.  Click on the outside edge of the shape until it activates (you’ll either see the fuzzy line around the border in Excel 2003 or you will see the re-size handles shown below in Excel 2007.)

Once the shape is active, click in the formula bar and type in the formula   =C22, as shown above.  Hit Enter.  Now, whenever the value changes in cell C22, the value will also change in the shape.  On the other hand, if you see the text, “=c22” in your shape, you likely didn’t have the shape activated correctly.  Delete the text from the shape and try activating it again by clicking on the outside border.  You may need to click on the border more than once in order to activate the shape rather than activating the text editing within it.

Just as a side note, we used a pivot table in the above example but this may be somewhat risky in terms of linking the grand total to a shape.  The number of rows in a pivot table can change depending on the filters selected (e.g., some regions may not have had fuel spending in one or two months, so the total might appear in C20 or C18 instead.)  We’ll talk about a way around this over the next few posts.

That said, the long weekend is here and I’m declaring it an extra long weekend, so we’ll see you again on Tuesday morning!  Have a great weekend!