Flowcharting in Microsoft Excel

There are many courses on how to use Excel. In contrast the instructor focuses on workbook design so that workbooks are:

· Easy to understand for both the developer and the user.

· Error-free

· Automated

This logical and structured approach is called ‘Excel Engineering’. The first step in the Excel Engineering approach is to create a flowchart (or flowsheet).

Flowcharts are commonly used in process modelling, engineering, simulation and software development. All well-designed Excel workbooks will have flowcharts visualising dataflow.

Once an effective flowchart is created, both the user and developer gain clarity in understanding the dataflow within a workbook. The course introduces concepts such as hyperlinking to and from the flowchart to the Excel data (enabling easy navigation, and eliminating the need for provided Excel functionality such as trace precedents and dependents).

The course also confronts some of the limitations of flowcharting in front-end Excel and provides ways to increase the Excel functionality of Excel Flowcharting by providing VBA addins that are freely available to course participants.

The course is divided into 4 sections: 

• Introduction ( a general explanation about flowcharting and relevant flowcharting concepts)

• Hands-on flowcharting in Excel  (focus on shape and connectors, and putting these into a canvas)

• Creating a flowchart of a workbook ( a simple example of how to create a flowchart for a defined workbook)

• Using flowcharts software provided by instructor (Excel flowchart capability is limited so addins provided by the instructor are provided to enhance flowchart capability, A fundamental addin is that which automates hyperlinking to and from the flowchart  and the workbook data rendering navigation much easier).

The course contains 8 lectures.

Lecture 1 Overview of visualisation and flowcharting

The lecture provides: an overview of the course and a brief explanation of what a flowchart is, and the importance of visual representation of a process using a flowchart.

You will learn:

· The advantages of visualisation of a process

· The purpose of a flowchart

· What will be covered in the course.

Lecture 2 Flowcharting in Excel – concepts

You will learn the historic use of flowcharts such as:

· Software design,

· Engineering

· Business process modelling.

For comparative purposes you will learn available formula tracing methods.

You will learn the advantages of using flowcharts to:

· Improve inter-workbook navigation

· Improve workbook navigation

· Improve worksheet navigation

· Improve formula tracing by using flowcharts

· Reduce errors

· Improve workbook structure

· Improve worksheet structure

· Improve inter-workbook structure

You will also learn that flowcharts need to be consistent with Excel structure.

Lecture 3 Flowcharting in Excel – design concepts

This lecture focuses on the consistent design of flowcharts:

· The importance of consistent shape selection and formatting.

· The role and simplification of flowcharts using templates.

Lecture 4 Creating a canvas

Canvases are commonly uses in Microsoft applications such as Word.

A canvas does not strictly exist in Excel – but there are ways to still create one. A canvas is used to collate shapes into a distinct diagram (flowchart).

You will learn how to:

· Add Shapes

· Name a shape

· Insert connectors

· Insert Canvas

· Name canvas

· Formatting the canvas (borders)

· Select many shapes using shift left click

· Select many shapes using Ctrl A

· Move many shapes into canvas by cut and paste

· Insert shapes directly into canvas

Lecture 5 Manipulating shapes

You will learn how to:

· Change shapes outside of canvas

· Change shapes inside canvas

· Change many shapes simultaneously

· Add connection points to a shape

· Changing a shape after connectors are added

· Flipping shapes

· Rotating shapes.

Lecture 6 Using pictures

You will learn:

· How to create a picture

· The difference between an icon and a picture

· How to insert pictures and icons

· The pros and cons of pictures compared to shapes

Lecture 7 Creating a flowchart of a workbook

You will learn:

· How to create a flowchart for simple workbook

· Create templates

· How to create a flowchart that uses templates

· How to create manual hyperlinks.

Lecture 8 Using flowchart software provided by instructor

The first part of the lecture focuses on addin installation and you will learn:

· What is an Excel addin?

· How to set Excel to use external addins

· How to install the required addins

· How to load the required addins

Once the addins are ready to use, you will learn how to:

· Put all pictures in a library

· Create a canvas for existing shapes

· Resize a canvas without moving or resizing shapes

· Name shapes inside a canvas

· Automate hyperlinking.

Who this course is for:

• People who use Excel professionally. They either need to understand Excel workbooks that they work on or create workbooks that others use. Alternatively they create workbooks which may have a series of data flows or calculations.