<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Use Excel to create Flowcharts

Published on
13,148 Points
6,648 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
Comment
Author:Rartemass
7 Comments
LVL 1

Expert Comment

by:m1m1k
Use freemind for Flowcharts, much easier and faster than what you describe above.
http://freemind.sourceforge.net/wiki/index.php/Download
0
LVL 19

Author Comment

by:Rartemass
Thanks for the link m1m1k, however that may not suit all people. Some IT departments don't allow staff to install other software or won't allow freeware on the network. In these cases Excel is a good solution as Office is usually installed by default.
0
LVL 19

Author Comment

by:Rartemass
Also if you want to share the work with others they are more likely to have Excel installed and thus can view and edit the flowchart. Plus you have the power of Excel to have spreadsheets on the same page as flowcharts and graphs.
0
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

Expert Comment

by:ITMAGINATION
Still, there are many online flowchart editors, both free and paid, some with great collaborative functions. All of them easier to use are much more effective than MS Excel.
0
LVL 19

Author Comment

by:Rartemass
Thanks for your feedback ITMAGINATION however I think you may have missed the point about some IT departments not allowing the installation of free software.
Where I work no unauthorised software is allowed, and no free software of any kind is ever authorised. In workplaces with these restrictions it is impossible to get one of the many free alternatives.
This article is for people that are in that situation, or simply want to combine their flowcharts with their spreadsheets. This is something many people at my workplace do everyday.
0

Expert Comment

by:Webduino
Freemind is more of a mindmap software, and not really for flowcharts.

I think the article was great, as I didn't even know it was possible to make flowcharts with Excel.

1. You may just want to include a small flowchart inside an excel file.
2. You may want to send a flowchart to someone and not require them to download anything to view it.

I appreciate the article, please ignore the haters.
0
LVL 19

Author Comment

by:Rartemass
Thanks for the feedback Webduino, glad it helped.
0

Featured Post

HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

Join & Write a Comment

This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month