<

Use Excel to create Flowcharts

Published on
13,558 Points
7,058 Views
5 Endorsements
Last Modified:
Approved
Flowchart software can be quite pricey for some smaller organisations, however MS Office is often provided as a desktop requirement almost regardless of organisation size. With MS Office, you do have the facilities to create professional looking Flowcharts.

Below is a reference for using MS Excel to create flowcharts which can be incorporated with all the added power of spreadsheets. All the examples are based in Excel 2007, though they are mostly unchanged for later versions.

All the tools you require are accessed from the Shapes button via the Insert tab on the ribbon. There is a section specifically for flowcharts towards the bottom, however you can use any shape in much the same manner.

Shapes tools
Click on the desired shape. Then click and drag the mouse to the desired dimensions. You can use the cell lines as a reference to keep things lined up.

rectangle shape
To type within the shape, right click it and select Edit Text. This creates an area inside the shape for data input.

context menu edit text
To edit the text from now on simply click inside that shape.

edit text
To edit the properties of the text box itself (margins, text alignment, adding columns) right click the text and select Format Text Effects...

To edit the shape's properties (line colour, shadow, fill colour, etc) right click the shape and select Format Shape...

There are a lot of effects here including 3D rotation, bevels, etc. These can be adjusted based on personal taste or chart theme.

format shape options
When you select the shape you will see a line above it with a green dot at the tip.
Moving the mouse pointer over it with turn the pointer into a circular arrow.
Clicking and dragging in this state will rotate the shape.
You may need to adjust the text alignment and orientation to fit the new position of the shape.

rotate shape
To add a connector between processes select the straight arrow or elbow arrow tools from the Connectors floating menu

 connector options
Move the mouse to the edge of the shape with the originating process. Blue dots will appear stating where the connectors can attach.
Moving the mouse over the blue dots changes the pointer to a square target. Left click on the desired blue dot.

end points
Move the mouse to the shape for the second process. The same blue dots appear where the connector and attach.
Left click on the desired location.

second end point
A new arrow is connected between the processes.

completed link
To edit the arrow properties.
Clicking on the line changes the items displayed in the ribbon. Adjust the properties as required.

arrow properties
Connecting to some shapes may cut the lines through other shapes.

link cut through objects
To stop this use the elbow arrow connector instead.

elbow connector
If you need to reposition any shape with a link, the connector will extend, shorten, or move itself to align correctly.

Elbow connectors will position themselves away from other objects. There are some instances when the system can't get a perfect fit around objects. In these cases simply click on the elbow connector. A yellow diamond appears in the middle. Click and drag this to manually reposition the link.

reposition link
For this reason alone it is best to always use the elbow connector. You never know when you have to move objects down the track.

To delete a shape or arrow simply select it and hit the Delete key on the keyboard.

You can use the cells in Excel to make comments next to the connectors. This is useful for when a decision needs to be made.

The comments can be edited just as you would do in Excel normally. You can also use formulas if required.

comments example
Some flow charts may require additional details that are unable to fit in the shapes, or require a reference to another process.

It is recommended to create a Reference sheet in the workbook. You can then use hyperlinks to go to the desired cell in of the sheet.

Create a new AutoShape, preferably the Process shape from the flowchart floating menu.

process shape
Position this inside an existing shape.

process inside shape
Edit the properties of the new shape (via right click > Format Shape).

On the Lines Color group change the line color to No Line.
 
no line format
Enter some text (right click > Edit Text).

Some shapes will need to be displayed in front of other shapes. To change their depth position, right click the shape and select Bring to Front >Bring Forward.
This will bring it one step closer to the front. Selecting Bring to Front will place the shape on top of every other shape so it will always be displayed on top.

bring to front context menu
Right click on the shape again and select Hyperlink.

hyperlink context menu
Select Place in This Document on the left.
Select the appropriate sheet.
Type in the cell you want to link to.
Click OK.

reference hyperlink
To change the displayed tool tip click on the ScreenTip button.
Then enter the desired text, and click OK.

Tool tip selection
When moving the mouse over the link the point will change to a hand and a tool tip will display stating where it will link to.

Tool tip in action
Clicking on it will display the desired details.

Linked reference sheet
With the above options you can make a powerful flowchart or organisation chart. It can be very useful for creating charts with links directly into reports or graphs.

Learning these techniques will be valuable in many roles and can potentially save your place of business money in not having to purchase additional software.
5
Author:Rartemass
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free