The PC HelpSite
www.officehelp.biz
Office Tutorials & Software Solutions
Specializing in Advanced Spreadsheet & Office Macro Solutions.
Organization Chart Maker for Microsoft® Excel®

Application Microsoft Excel Version 2.10
Author OfficeHelp
Tested on versions: 2003 (11.0), 2007 (12.0), 2010 (14.0), 2013, 2016 - should work on any version from Office 2003

Quickly and easily generate your own organization charts from a plain list of data with Organization Chart Maker for Microsoft® Excel®. Apply colors and formats. Integrate external data from ERP or HR management systems like or Microsoft® Dynamics Navision, SAP®, PeopleSoft®, Oracle®, etc. Chart Stock & Portfolio listings, team members planning & company ownership charts. Runs in Excel, no installation required. Really EASY - if you know Excel, you know this!

Contact Us for Support Main Page FREE DEMO

HELP Index


FAQ - Frequently Asked Questions

Why do I get a Macro security warning when opening the macro? 

Macros are a very useful tool to automate tasks in Microsoft® Office. Unfortunately, like another programming language, it can be misused. There are Macro Virus, so Microsoft® Office can be configured to request permission to run macros. Only run macros from known sources. OfficeHelp.Biz macros are Virus Free and can be open with confidence. 

Click here for more information on how to setup Excel macros

OFFICE 2007 / 2010 Detection & Support 

This macro will detect Office 2007 and will use the Extra columns available (Excel versions previous to 2007 are limited to 256 columns). With Office 2007 / 2010, it is possible to create very wide Organizational Charts. 

How do I get Support ? 

Use the Support Contact Us page on this website. No registration is necessary. Clearly describe your problem and contact details.


How to create your Organizational Charts (INPUT Sheet)

The macro is supplied with sample data and ready to use. To try it, just click the Run button on the INPUT sheet. A sample Org Chart will be created. 

If NOTHING HAPPENS when you click the Run button, then macros are disabled. Please use the link below to set the macro permissions: 

Click here for more information on how to setup Excel macros

Once the sample Org. Charts have been created, it should be simple to relate them with the original data on the "Organization Chart Member List". 

How to use your own data to create Organizational Charts?

To replace our sample data with your won data, follow these steps on the INPUT SHEET: 

  1. Use the Reset List button to erase all existing data. This will demand a double confirmation; 
  2. Enter your own data into the "Organization Chart Member List" starting on row 20 and column B; 
  3. Click the Run button to see the results; 
  4. Adjust the data as appropriated and Run again until satisfied with the results;

How to input the Members List for the Org. Chart?

Insert data for each Member on the appropriate column of the member list starting on row 20: 

  • ID: Unique identifier for a member. Must be unique on the whole list and must be a positive whole number. 
    • IDs must be unique but do not have to be in order. The ID order is also not related to drawing order. 
         
  • Parent ID: ID number of the member parent. If you have a subordinate or assistant, then its the boss ID. 
    • The ID for the TOP member should be 0, as it has no Parent. 
    • If the ID for the Parent doesn't exist on the Member List, this member will not be included on the Org Chart (as it has no one to descend from). 
       
  • Name: Member name. On an organization chart, it should be the person name, but it can be changed for any text (company name, etc). 
    • The name if the TOP of the 3 content rows each member has. 
        
  • Function: Member Function. On an organization chart, it should be the person function, but it can be changed for any text (department, number, etc). 
    • The FUNCTION if the MIDDLE of the 3 content rows each member has.
        
  • Assistant: Enter YES if this member is an Assistant to its Parent member. Leave empty if not. 
    • Assistants are drawn in a different position that subordinates - between the Parent and its Subordinates. 
       
  • Color: Either enter a color directly (by coloring the cell background) on enter a color name. The macro will use the cell background color. 
    • For a color name to work, it is necessary to use the "Set Colors Text Equivalence" on the CONFIG sheet (ON by default). 
    • Set each color name and equivalent color on the CONFIG sheet table on cell C33. The macro will locate each member color name and if found on the table, will automatically apply it to the member list cell. If not found, then the cell background will be left without changes. 
       
  • Note: Member Note. Free text field for each member. Use it to display any additional text not included on name or Function.
    • The NOTE if the BOTTOM of the 3 content rows each member has. 

Is data validated before running the macro?

Yes, your data will be validated by the Run button and any invalid data will be identified for correction. 
The Excel cells themselves have built-in validations that won't let you enter invalid values like negative IDs. 

How does the macro knows when the Members List end?

The macro will stop loading the member list on the first row with no data on the ID field. The actual Org. Chart will stop once all children of the selected start member are included, even if more members exist. 

What are the 4 option fields near the Run button? How to use them?

Near the Run button, there are 4 fields that can affect the output files - the Organization Charts created by the macro:

  • Title Field - The name of the worksheet on the generated chart. This name must be a valid Excel worksheet name and will be validate by the RUN button. In case an invalid name was used, an error message will be shown (which explains the invalid characters) and the macro will stop before creating the Org. Charts. 
      
  • Start on Row - Defined the TOP element when drawing the Org. Chart. By default, it is the first row on the Member list but can be any row on the list. This allows a single Member List to create multiple charts, and to create sub-charts (example: the full company organization starts on row 20, the Legal Department on row 35, the Marketing Department on 50. If you start with row 20, the whole company will be charted. If row 35 is selected, only the Legal department is charted. 
      
  • Levels to Include - Define the number of vertical levels to include. By default is "ALL", meaning all available levels, but it may be set to a specific number. Example: On a Department with 4 reporting levels, only the first 2 may be necessary on a chart (Manager and Direct Reports). 
        
  • Chart Format - Defines the Org. Chart Format. There are 2 formats and 3 options available: 
       
    • All - Default option. Creates 2 Excel workbooks, each with one of the 2 formats (Wide and Narrow) on a single run. 
    • Wide - The last level of the chart is drawn with all members split horizontally over the same row. Produces wider (more columns) but shorter (less rows) charts. 
    • Narrow - The last level of the chart is drawn with all members split vertically on the same column. Produces narrower (less columns) but longer more rows) charts. 

Generic Macro Options

Can I stop the macro while it's running? 

Yes. Press the ESC key and it will stop with a warning message that it was interrupted before finishing generating the Org. Chart. 

Can I manually change the resulting Org. Charts (output Excel files)? 

Of course. They're an Excel Workbooks like any other. There is nothing special once it's generated, only colored cells and data. 

Can I save the resulting Org. Charts (output Excel files)? Can I have several copies? 

Again, of course. It's a regular Excel file. There are NO MACROS on the resulting plan, only on the generator (this) file. 

What is the "Reset List" button? 

The Reset list button will delete ALL members on the Members List. Therefore, you have to confirm TWICE the action. All existing Members will be PERMANENTLY ERASED. 

Can I personalize the macro POP-UP messages? Can I have a Corporate (branded) version? 

YES by special request. Use the contact from us on our website to request a custom version (costs will apply). 


Advanced Customization (CONFIG Sheet) 

The CONFIG sheets allows for the customization of the Outputs - Generated Org. Charts - by setting values and samples that will be used when drawing the chart. Below, a description of each CONFIG sheet options group. 

Set Dimensions 

Sets the Column widths and row eights for specific cells on the Org. Charts. 

  • Column Width: width of all content columns of the Org. Chart (excluding the first and last, narrower for framing). 
  • Org Box Row Height: Height of the rows where there are boxes (members) drawn. 
  • Splitter Row Height: Height of the rows between boxes (members). The horizontal splitter rows between levels. 

Set FONTS (Size, Color, Bolds, etc) 

Sets the Font name, size and formats (bold, etc) by Example. Format the font on each cell, its formats will be copied to the corresponding destination on the Org. Chart. 

  • Main Title: Font sample for the Org. Chart Title. 
  • Name: Font sample for the "NAME" of the member (TOP cell on each box). 
  • Function: Font sample for the "Function" of the member (MIDDLE cell on each box). 
  • Note: Font sample for the "Note" of the member (BOTTOM cell on each box). 

Copy the "INPUT" sheet Header & Footer (printer) to the Output (Org. Charts): 

If YES, the printer Header & Footer defined for the INPUT sheet will be copied to the new output Org Chart. 

Set Colors Text Equivalence 

Apply color from table if a color label is found on the "Organization Chart Members List"

If active, any color label on the "Organization Chart Members List" (column H) will be converted to the appropriated color as defined on this table. This feature allows this software to be used with lists created on external software that cannot export Excel colored cells but can export text files. 

Create a report on the source software (ex: ERP or RH software) that exports a text file with the same columns as the "Organization Chart Members List" and load it into Excel. This feature will apply the colors to the H column cells based on the color label. 

For a color name to work, it is necessary to use the "Set Colors Text Equivalence" on the CONFIG sheet (ON by default). 


Contact Us for Support Main Page FREE DEMO

www.OfficeHelp.Biz

(c) 2004 - 2024 OfficeHelp.Biz