• How to Create Organization chart in Excel?

    Do you want to do add Org Chart thru this menu option?

    Click on Excel Menu -> Insert -> Smart Art. Choose Hierarchy charts in pop up menu.

    Select the Org charts of your choice & enter each level of chart. There is more detailed explanation for manual creation of Org chart in this page.

    Or like this …

    Automatic: What if you want a customized automatic table to understand the flow of control?

    Also Read: VBA Macro - Choose Outlook Email Account - To Send Mass Mail

    Excel VBA Code – Build Org Chart – Hierarchy Flow

    Do you already have a Organization Reporting structure as in Table-1?

    Then create Org chart or Hierarchy Tree using these steps.

    Table-1: 

    Employee 1Employee 2
    Employee 1Employee 3
    Employee 2Employee 4
    Employee 3Employee 7
    Employee 2Employee 8
    Employee 2Employee 9
    Employee 4Employee 5
    Employee 4Employee 6
    Employee 5Employee 10
    • Create a empty blank workbook with 3 sheets. Copy paste the Org reporting details in Sheet1 like this:
    • Press Alt + F11. Insert a new module.
    • Copy paste the VBA code in this page to the VBA project.

    Press F5 to get the Hierarchy Table in Sheet3.

    VBA Code to build Hierarchy Table

    Here is the code that is worth a shot.

    It does not create any graphical representation.

    Instead it splits the table-1 as per the level of control & present a table format that is almost like a horizontal organizational hierarchy chart.


    This code creates this Org table in 2 steps.

    • In first Step it decides the ranking for each employee & building a ranking table.
    • Then based on that ranking table, the position of each Employee is decided.

    The resulting table structure will look almost like a tree, easy to understand the flow on control & who is reporting to whom.

    Also, this can be improvised further to color the background cells of nodes as per their position or control ranking.


    Previous Post:
    Next Post: