USER: I have a large spreadsheet with many calculations. Results from section 1 are carried forward to cells in section 2. It would help to graphically illustrate that one cell flows to the calculation of another.

SOLUTION: You can use the Shapes feature to add arrows to indicate the flow of cells. Here’s how you use it.

fig1FLOW1). Select Insert ribbon, Shapes dropdown, and choose an arrow (Figure 1, left).

2). Click in the origin cell and drag to the final cell. Release the mouse button, and an arrow appears, pointing from the first cell to the end cell. Annoyingly, the shape is drawn in a light shade of the first theme color, which ends up as light blue in the Office theme.

fig2FLOW3). While the arrow is still selected, open the Shape Styles gallery on the Drawing Tools Format ribbon tab. Select one of the black styles in an appropriate thickness (Figure 2, right).

4). You can further control the arrow using the Shape Outline dropdown on the Format ribbon. Use the Weight, Dashes, or Arrows flyout menus. There are even more options available by pressing Ctrl+One with the arrow selected (Figure 3).

fig3FLOW5). By default, the arrow will resize with the cells. Say that the arrow stretches from column E to column C. If you make column D wider, the arrow will stretch. To turn off this behavior, right-click the arrow and choose Size and Properties. You can then decide if the shape should move, resize, and/or print.

Additional Details

Any line in the Shapes gallery can become an arrow. You might need to draw a curved arrow (Figure 4, below).

1). For a curved arrow, choose the Curve icon in the Shapes gallery.

fig4FLOW2). Click the starting point (the non-arrow side) of the line.

3). Start drawing with the mouse. Anywhere that you need the line to change direction, click the mouse and keep drawing. To draw the curve in Figure 5, you would click the mouse at each point shown (Figure 5, below).

fig5FLOW4). Double-click on the endpoint of the line.

5). With the line selected, go to the Shape Outline dropdown on the Format ribbon tab. Choose an arrow from the Arrows dropdown. Choose a darker color. Choose a heavier weight.

fig6FLOWIf you later need to edit the curves in the line, right click on the line and choose Edit Points. In Edit Points mode, when you click a point, you can move that point, or move one of the two white diamond handles to change the arc of the curve (Figure 6, left).

CFO contributor Bill Jelen. a.k.a. Mr. Excel, is an Excel MVP and the author of 35 books about Microsoft Excel. This article originally appeared in the August 25, 2014, edition of the CFO LearningPro newsletter.

, , ,

Leave a Reply

Your email address will not be published. Required fields are marked *