Flowcharting in Excel Series -

By Nicholas Hebb

This article gives an overview on how to create flowcharts in Excel. It applies primarily to Excel 2003 and previous versions. Excel 2007 has a different user interface and more formatting features. Flowcharting with Excel 2007 will be covered in a future article. Also, most of the techniques described here can also be applied to creating flowcharts in Word or PowerPoint, but in my humble opinion, of all the Office Drawing tools, the Excel drawing tools are the most user friendly.

1) Enable the Drawing Toolbar

The first step to drawing flowcharts in Excel is to make the Drawing toolbar visible. This can be done by selecting View > Toolbars > Drawing from the main menu or by clicking the Drawing toolbar icon on the main Excel toolbar:

2) Create a Flow Chart Grid (Optional)

This step is optional, but it makes for a nicer flowcharting environment. To create a flow chart grid in Excel, select all the cells by clicking on the corner of the spreadsheet, as shown in the picture below-left. Then, right click on one of the columns and select Column Width. As shown in the picture below-right, enter 1.71 for the column width (which equals 17 pixels). The standard row height is 12.75 points, which also equals 17 pixels on most systems, so you get a nice tight square grid.

Excel 2007 Update: For Excel 2007, the standard row height is 2.14, so you will need to set the column width to 2.14 as well.

In general, the standard height is dependent on the default font. The default font in Excel 2003 and previous is Arial 10, but it has been changed to Calibri 11 in Excel 2007.

3) Enable The Excel Snap to Grid Feature (Recommended)

This isn't required, but turning on the snap to grid function makes flowcharting in Excel so much easier I can't imagine creating flow charts without it. This feature makes the shapes align to the Excel worksheet cells when you add them, re-size them, or move them. It's great for ensuring your flowchart symbols are uniformly sized and aligned.

To turn snap to grid on, simply click the Draw button on the Drawing toolbar. Then, click Snap then To Grid, as shown below.

4) Set the Page Size and Boundaries

It's always good to know your limits, and making flow charts is no exception. You need to set the page size and then do a print preview because this will display the page breaks.

To setup the page properties, click File > Page Setup... from the main menu. Set properties such as portrait or landscape, paper size, and margins and close the form. One consideration you should make when setting the properties is where the flow chart will be published. For example, if you copy and paste the flow chart into Word, then it's good to remember that Word's default lateral page margins are 1" less than Excel's (i.e., 1/2" on both the left and right sides).

After the page properties are set, click the Print Preview button on the main toolbar. Alternately, you can click File > Print Preview from the menu. Close the preview screen and the page breaks should now be visible. If there is nothing on the worksheet yet to preview, Excel will pop open an error message. If it does, just click OK - the page breaks should now be visible anyway.

5) Create the Flow Chart Swim Lanes and Title Block (Optional)

If you're creating a Deployment Flow Chart, an Opportunity Flow Chart, a Process Relationship Flow Chart, or any other type of flow chart that requires swim lanes or swimming pools, then it's a good idea to create the structure of the flow chart before adding the flow chart symbols.

A full explanation of each of these specialized types of flow charts deserves an article of its own. But quickly, you can create the flow chart column and row headers in two ways. The first way is to use Excel's cell merging and borders. This is the easiest way if you plan on publishing the flow chart in Excel.

The second way is to create the headings with Process flow chart symbols and create the swim lane dividers with autoshape lines. The advantage to this method is that the swim lane heading shapes and dividing lines can be selected along with the flow chart symbols, so you can easily copy and paste the whole diagram if you're going to publish the flow chart in Word or some other Office application.

In the flow chart swim lane examples below, the swim lanes on the left were created using cell borders and the swim lanes on the right were created using process flow chart symbols and autoshape lines. As you can see, the appearance is identical.

Also, if you plan to add a title block, including the process name, author(s), and revision info, then doing so before creating the flow chart is a good idea.

6) Add a Flow Chart Symbol

To add a flow chart symbol to the worksheet, you need to click the AutoShapes button on the Drawing toolbar, then click Flowchart, then select the shape you want to add, as shown in the picture below:

The mouse cursor will change to a crosshair. Left click on the worksheet location where you want the top left corner of the flow chart symbol to be and drag the mouse until the flow chart symbol is the size you want. See the flow chart terminator symbol below for an example.

7) Adding Text to a Symbol

To add text to an Excel flow chart symbol, simply click on the symbol and start typing. Note: If you've created Word flow charts before, this is one of the differences between creating flow charts in Excel and flow charts in Word. In Word, you have to right-click on the shape and select Add Text from the context menu.

Tip: If you want to force line breaks at certain points in the text, hold the ALT key down when pushing the ENTER key.

The text inside the shape can be formatted using the standard formatting toolbar buttons. You can select the text and edit it by clicking inside the shape.

8) Add a Connector (Flow Line) Between Two Symbols

[Note: A Flow Line is an arrow showing the order of the process steps. In Excel, flow chart lines are called Connectors. But Connector is also the name for a flow chart symbol used to depict a labeled node indicating a jump to another part of the flow chart. I will typically use the term "Flow Line" to avoid confusion, but in this section Flow Line and Connector are used interchangeably.]

Connectors are named as such because the lines actually connect to the flow chart shapes. When a shape is moved around, a Connector will remain attached to the shape, whereas a standard Excel line or arrow will not be connected.

To add a flow line between two shapes, first select the Connector type you want to use, as shown in the picture below. Tip: The Elbow Connector is versatile for flow lines because it will look just like a straight connector when the shapes are aligned.

After you've clicked on a Connector type, the mouse will change to a crosshair. Click on the edge of the first flow chart symbol and drag the mouse over to the edge of the second flow chart symbol, then release the mouse button. A faint dashed line will show the path of the flow line. When you hover the mouse over a flow chart symbol, the possible connection points will show as blue dots. Also, the mouse cursor will change to a bomb site when you're near one of the connection points (see the picture below).

When an Excel flow chart Connector is connected to a flow chart symbol, the ends of the Connector are red dots. If one of the Connector ends is not connected it shows as a green dot. The figures below show a unconnected flow line on the left and a connected flow line on the right. To connect a flow line like this, just click and drag the endpoint to the correct spot.

A yellow diamond (not shown) on a Connector is a line routing handle. You can click and drag that to re-route the connector line without changing the position of the endpoints. It's a handy feature, especially when a long flow line is routed behind other shapes.

9) Adding Callouts

Sometimes you need to add a note or explanation for a flow chart process step that doesn't fit into the flow chart symbol. For these circumstances you can add a callout. Callouts are added from the Excel Drawing toolbar in the same way that you add a flow chart symbol or flow line.

10) Move a Flow Chart Symbol

To move an Excel flow chart symbol, just click on the shape with your left mouse button and drag it to its new location. Excel will show dashed lines to preview the new layout, as shown below.

You can also move a flow chart symbol with the arrow keys. Normally, the arrow keys will nudge the shape a small amount, but if the snap to grid feature is enabled, the arrow keys will move the flow chart symbol one cell at a time. To nudge a shape when snap to grid is enabled, hold the Control key down when you press the arrow key.

11) Resize a Flow Chart Symbol

First, Excel has an autosize feature available in the shape formatting dialog. Don't use it. Excel's idea of flow chart shape autosizing is to resize the shape so that all the text fits on one line.

Tip: You can move, resize, delete, or format multiple flow chart symbols at once. See this article on selecting multiple flow chart shapes.

To resize a flow chart symbol, first select the symbol by clicking on it with your mouse. The symbol will be highlighted and little circular handles will appear on the sides and corners, as shown in the picture below-left. Click and hold one of the handles and drag it in the direction that you want to resize the shape, as shown in the picture below-right. Excel will also display a dotted outline of the new shape size (not shown in the picture). Release the mouse button when the shape is the size you want.

12) Aligning and Distributing Flow Chart Symbols

After you move or resize flow chart shapes, the alignment may get thrown off. Plus, if you resize a bunch of shapes to make them bigger, the spacing between the shapes may get scrunched up. Excel has a tool that can be found under the Draw menu on the Drawing toolbar to assist the Align or Distribute functions.

To use these functions, you must first select the shapes you want to align or distribute, then simply select the one of alignment (left, center, right, top, middle, bottom) or distribution (horizontally or vertically) functions to get the flow chart squared up. Alignment is self-explanatory, but distribution will take a set of shapes and spread out the distance between them uniformly.

13) Deleting Flow Chart Symbols

One of the least enjoyable things to do with a flow chart is maintain it. Here's a tip to make that process a little easier. Lets say you start with a flow chart that looks like this:

You want to delete Step 2, but that will leave a void, as shown below ...

... which means, you have to move the other shapes. You can select multiple shapes using various methods and drag them to their new home. That works OK many times, but if the flow chart is really big it can be a hassle to select them and move them all.

By default, Excel sets the flow chart autoshapes to move when cells are deleted, inserted, or resized. We can use this to our advantage by deleting cells to move the shapes. Select a range of cells as shown in the picture below. You must select a range of cell as wide or wider than the shapes you want to move! Right-click on the cells and select Delete... from the context menu.

In the example, we want to move the shapes in this swim lane up, so we select Shift cells up.

The result can be seen below. First there's the small issue of the flow line (Connector). It's not connected. Step 3 was moved into position to be connected to Step 1, but the connection still needs to be closed manually.

The second, and bigger, issue is the symbols in the Dept 3 swim lane. This method would have worked fine if there were only one swim lane. But the flowchart symbols in the Dept. 3 swim lane didn't get shifted. To remedy this we could have elected to delete the entire rows to have everything shift up. This option depends on the flow chart layout and what other shapes might be effected by such a move.

Another option would be to backtrack and select the cells from both the Dept 2 and Dept 3 swim lanes, as shown below. Then do a Delete... and Shift cells up as we did before.

Much better. Now all you need to do is close the connection between Step1 and Step3.

14) Inserting Flow Chart Symbols

Inserting new flow chart symbols is essentially the same process as deleting a flow chart symbols - just in reverse. As with a Delete operation, we select the range of cells to perform the insert on. Again, the range is selected so that the cell shifting effects the other flow chart symbols in the desired way.

A space has been opened up to place the new flow chart symbol:

A here the new flow chart symbol has been added:

15) Change a Flow Chart Symbol Type

Sometimes you decide a different flow chart symbol is needed. It's a common practice to make all shapes Process symbols (rectangles), but there's a lot of semantic information in symbols that conveys added meaning at-a-glance when you use more specific symbol types. To change a flow chart symbol type, first select the symbol. Then select the new shape from the Change AutoShape menu as shown below.

Here is the flow chart after the symbol change was made:

16) Formatting Flow Chart Symbols

Many of the formatting features are available on the Excel Formatting toolbar (e.g., bold, italic, horizontal text alignment), and many others are available on the Excel Drawing toolbar (e.g., fill color, line color, line thickness, drop shadow).

Some of the formatting options are only available on the Format AutoShape dialog, such as vertical text alignment. This form can be opened by double-clicking on the outside border of the flow chart shape. Some of the more advanced formatting options are available in the Fill Effects sub-dialog, including gradient fills, textures, and adding pictures to flow chart symbols. The Fill Effects dialog is opened by clicking on the Fill Color dropdown as shown in the picture below.

---

Related Articles

Want more useful flowcharting tips? View the list of other flow chart, process map, and process improvement articles here.

---

About the Author

When he's not writing about flow charts Nicholas Hebb runs BreezeTree Software, maker of FlowBreeze Flow Chart Software - a program that cuts the time to create a flow chart dramatically compared to traditional flow chart tools. What makes FlowBreeze different?

It converts your text into flow chart symbols based on the words you type, adds a flow line from the last shape, and many other time saving features. And, because it's a Microsoft Excel add-in, you will create flow charts that are highly portable and integrate directly with Microsoft Office for easy sharing and collaboration.

Click here to view the On-Line FlowBreeze Flow Chart Software Demo and see for yourself. (It's a short, 30 second video that loads fast.) You'll be impressed.