Generate organization chart in excel

here is a good way to generate organization charts in excel using SmartArt and little bit of VBA.

well i am no expert and what ever i do is all hit and trail with lots of reference from help contents.

so logic of creating organization charts using smart art is as follows.

but before that few facts for easy understanding at least this helped me..
- Every Smart art has a Layout
By default excel has many default smart art layouts, we will use layout number 92 in office 2010 as it is one of the organization layout, feel free to increase or decrease the number to change layout.
- SmartArt has nodes
- All nodes can be referred as nodes
- We can count the number of nodes
- Nodes can be promoted or demoted
- Nodes have levels. like level 1,2,3 etc for each horizontal representation.

Now enough mentioned, time of logic.

logic is to use vba macro to insert a SmartArt organization chart layout, then delete all its default nodes, then add required number of nodes, promote all the nodes to one level and the use loops to promote and demote each node on the desired level.
so we need 2 things primarily Name + designation & Level, lets use the following as reference data. (fake Data)

 

its a good idea to combine Name and Designation using the concatenation function so that Name is in the first line and Designation in the subsequent line. eg: 

Steve

Director

i used the Ascii code for 'enter key' to achieve the same with concatenation function.

=Concatenate(B2,char(10),C2) 

on mac pc the code is char(13). so use accordingly. now here is my updated table, i have emerged, column B and C in to one column, also i have removed the first row, the header row.

 now simply click the record macro button and record and macro, i gave it a name Org and shortcut key as ctrl +j. then click stop, we only wanted to record a blank macro

 now got to view macro and then edit macro, this will open VBA editor, now all you need to do is to replace the existing function with the one mentioned below.
 ----------------------------------------------------------------------------------
Sub org()
'
' org Macro
' Macro to generate organization chart
'
' Keyboard Shortcut: Ctrl+j
'
    Dim ogSALayout As SmartArtLayout
    Dim QNode As SmartArtNode
    Dim QNodes As SmartArtNodes
    Dim t As Integer
    Set ogSALayout = Application.SmartArtLayouts(92) 'reference to organization chart
    Set ogShp = ActiveWorkbook.ActiveSheet.Shapes.AddSmartArt(ogSALayout)
    Set QNodes = ogShp.SmartArt.AllNodes
    t = QNodes.Count
  
    While QNodes.Count < t
    QNodes(QNodes.Count).Delete
    Wend
  
    While QNodes.Count < Range("A1").End(xlDown).Row
    QNodes.Add.Promote
    Wend
  
    For i = 1 To Range("A1").End(xlDown).Row
    'Promote and demote nodes to put them at the proper level.

    While QNodes(Range("A" & i)).Level < Range("C" & i).Value
        QNodes(Range("A" & i)).Demote
    Wend
 
    'Copy the cell text to the node.
    QNodes(Range("A" & i)).TextFrame2.TextRange.Text = Range("B" & i)
    Next i
End Sub


 ---------------------------------------------------------------------------------------------------
now simply run the macro and all will done automatically, here is the output form my code.

that's it i can attach the file as well however that's a .xlsm file which might get blocked due to macros. give it a try and let me know if this doesn't work. i will be glad to help.