Excel Simple Flowchart Creator

In this post we see how to create a flowchart in Excel with data from a selected range. We can do that with just one click using VBA macros. It is a simple example to add and manipulate shapes in Excel programmatically. The macro loops through the cells in the selected range, and adds a shape for each cell and connectors between the shapes to show the process flow. This flowchart creator in Excel can easily be leveraged to change the orientation of the process flow or add other flows to the chart. An example of such is the Excel Org-Chart Creator app published earlier in this blog, which creates a chart from a range of data organized by tier and reporting levels. It is ideal to display the team or the organization structure.

 

There are many tools available to create charts and diagrams such as Visio and some other Microsoft products. However, creating and updating such charts takes some time, especially when there is a change to the chart and you need to add, delete or rearrange boxes. It is also possible to create process charts with smart-art in Excel (or any other office app). But those charts are limited and difficult to customize, and also require some time to setup. Using macros to create your own flowcharts allows to customize the diagram as you need, and automatically add items or processes to the flowchart.

Source Data

The source data to create a flowchart in Excel is just a list of processes or tasks as values in the cells of a selected range. Ideally, the data should lay within a column or a row, so that the list follows some kind of order. The example below (also available in the downloadable file) shows the waterfall project phases within a column.

 

Each cell corresponds to a box in the flowchart. The macro loops through the cells and keeps adding shapes accordingly. The boxes are added one after another horizontally, but that can be adapted as needed. It may make sense to add shapes vertically if the range of data spans over several rows, and let it be horizontal when the range spans over several columns. That’s something that can easily be changed in the code (see later).

 

Adding Shapes

We need to work with the VBA Shape object to create a flowchart in Excel. In this example, we specifically add rectangular shapes with the ‘AddShape’ method followed by attributes for the position and size of the shape. Here’s how to add a rectangular shape to the position and with the dimensions specified with the corresponding variables. 

 

  Set shp = flowchartWS.Shapes.AddShape(msoShapeRectangle, _

  shpLeft, shpTop, shpWidth, shpHeight)

 

 

Where shpLeft and shpTop are the x and y coordinates in the worksheet, and shpWidth and shpHeight are the width and height of the shape. The value of clLeft keeps changing while looping through the process, so that each shape is added with a certain separation from the previous one.

Then we use the TextFrame.Characters properties of the Shape object to add text, color, or any other formatting of the box shape. In this example, we have the name of the process in the text frame preceded with the process step number, and align the contents to the center and change the font size.

   

  With shp

      .Name = "shp" & i

      With .TextFrame

          .Characters.Text = i & ". " & cell.Value

          .HorizontalAlignment = xlCenter

          .VerticalAlignment = xlCenter

          .Characters.Font.Size = 12

      End With

  End With

 

 

Note that we also change the name of each shape (shp1, shp2, etc.) using a variable that increments by one in every loop (i=i+1). This is important in the next step, where we add the connectors between the shapes.

 

Adding Connectors

Once all shapes have been added, another loop is used to add straight connecting lines with triangle arrow head between the shapes. The loop runs as many times as the number of shapes minus one (i-1). Here’s how we add the connector between two shapes.

 

  For n = 1 To i - 1

      fromBox = "shp" & n

      toBox = "shp" & n + 1

      flowchartWS.Shapes.AddConnector(msoConnectorStraight, 10, 10, 10, 10).Select

      Selection.ShapeRange.Line.EndArrowheadStyle = msoArrowheadTriangle

      Selection.ShapeRange.ConnectorFormat.BeginConnect flowchartWS.Shapes(fromBox), 4

      Selection.ShapeRange.ConnectorFormat.EndConnect flowchartWS.Shapes(toBox), 2

  Next n

 

 

Where fromBox and toBox are the name of the two boxes to be connected. The connector begins at the right side of the fromBox (position 4) and ends at the left side of toBox (position 2). That can be changed to 1 and 3 if the orientation is vertical, thus connecting the bottom and top of the box shapes. The picture below shows the reference position of the connector for each side of the rectangular shape.

 

The coordinates of the connector shape (always 10 in this example) are not relevant as the selected connector is moved and resized when linking the two rectangular shapes.

 

Check also this other post to see another example of adding shapes and connectors with macros to create diagrams in Excel automatically. In this other example we create an organizational chart in Excel using the same technique: Excel Org Chart Creator

 

Click Here to download Excel Flowchart Creator.