Organization Structure in Excel

To create an organization structure in Excel separate two steps that are often mixed up: 1. organize the data, and 2. visually generate the structure from that data. This article will show how to clean up organization data in Microsoft Excel1 so that you can create an org chart automatically with Lexchart (for free).

Organization structure

Organization structure has various meanings depending on context. There are generally three definitions of organization structure:

  1. Company ownership (legal) structure,
  2. Management reporting relationships,
  3. Governance (Board of Directors) responsibilities.

These three types of organization structure share a common trait. The structure is a hierarchy: a company owns another company, an employee reports to a manager, a committee reports to a board.

This article will show how to organize Excel data for an organization chart and how to generate the chart automatically from that data.

Organize Excel data for structure

The first step to generate organizational structure from Excel is to clean up the Excel employee data.

Avoid Excel data mistakes

The single biggest mistake in using Excel data for organization structure is using Excel to visualize the data.

Excel organization data with visual grouping

This spreadsheet mixes visual presentation with the raw data (this data is not real, by the way).

There are two questions to ask about a spreadsheet of organization structure data:

  1. Is the Excel grid used to layout or group the data for presentation?
  2. Are colors used in the Excel spreadsheet to communicate organization data?

Color to improve readability is fine; color to communicate hierarchy is not.

Focus Excel data on the relationships

In this version of the same Excel data, the spreadsheet is organized into single rows. Notice the effective use of color for readability. Color reinforces the data that is clear from the columns.

Excel organization data: employee and manager

There is one row for each employee. The second column shows the manager for each employee.

Since managers have more than one employee their names appear in column B multiple times. There is still only one row for each employee.

Notice how each manager is listed in column A one time. Anna Mann, for example, is listed in column A as an employee reporting to Juan Moreno. She appears three times in column B as the manager of: Kenneth Matthews, Kendra Perez, and Anna Olsen.

Dual roles in Excel organization data

As a general rule, an employee has one manager. There are exceptions.

An employee might have a dotted line reporting relationship to another manager. In a matrix organization, an employee might have an explicit secondary reporting relationship.

How should an employee with more than one reporting relationship appear in Excel? Answer: in a new row.

Excel organization data with multiple managers

This is bad form from a database perspective, but Excel is no substitute for a database. Wait for the end. These two rows will show up seamlessly.

Another exception is Chris Adams, who has no manager in this data. Why this exception? Chris Adams is the President & CEO. For this org chart, we want Chris at the top with no one above so the manager column is blank for Chris.

Add employee data as Excel columns

Managing employee data with Excel is simple with this approach. The reporting relationship is a piece of information, no different from any other employee data.

Your employee spreadsheet might contain many columns. This example shows: title, department, birthday, date started, and employee number.

Excel spreadsheet with employee information

Note that Anna Mann is a Vice President in Finance, with a birth date, start date, and employee number. Each employee has the same type of information.

Focusing on data, not presentation, makes the task of organizing employee information simple. With the data cleaned up, we can turn our attention to presentation.

Create organization structure from Excel

➡️

Import Excel data for organization chart

To make an organization chart from this Excel data, we can import it into Lexchart after saving as .CSV file format.

Lexchart creates organization charts automatically. There are free trial and a free account options for Lexchart.

Map Excel data to organization chart

Use the Import button to map the Excel data for the org chart.

Import Excel organization data

Mapping data to the organization chart involves three questions:

  1. Which column has the "child" data (is below on the chart)?
  2. Which column has the "parent" data (is above on the chart)?
  3. For every other column, does it apply to the parent, child or the link between the two?

In our example, column A (Staff) is the child data, column B (Manager) is the parent data, and the other columns are all "child data," meaning that they apply to the employees.

Map Excel data to org chart

The result is an organization chart laid out correctly and ready for design.

Kendra Perez Title Director Department Tax Started 8/23/17 Number 20236635 Kenneth Matthews Title Manager Department Accounting Started 3/23/17 Number 20891293 Elizabeth Smith Title Executive Vice President Department Marketing Started 2/16/08 Number 75813181 Jennifer Zhao Title Assoc. General Counsel Department Contracts Started 12/27/13 Number 78409501 Yao Tung Title Exec. Vice President Department Sales & Marketing Started 2/2/22 Number 48927582 Aaron Roberts Title Assoc. General Counsel Department Licensing Started 10/14/02 Number 53572411 Johnny Clay Title Vice President Department Eastern Region Started 5/28/11 Number 45907409 Joshua Hudson Title Manager Department Communications Started 1/19/18 Number 16146083 Anna Mann Title Vice President Department Finance Started 12/12/14 Number 79130795 Raven Garcia Title Director Department Policy Started 1/31/16 Number 90222489 Walter Smith Title Director Department Product Management Started 9/29/11 Number 24637520 Dalton Washington Title Asst. General Counsel Department Intellectual Property Started 4/7/22 Number 13779622 Anita Perez Title Vice President Department Northern Region Started 3/14/04 Number 70028570 Nicole Wyatt Title Manager Department Manufacturing Started 10/25/12 Number 20241776 Selena Morales Title Vice President Department Social Media Started 1/16/22 Number 41951734 Hannah Matthews Title Sr. Vice President Department Operations Started 12/16/12 Number 53043706 Anna Olsen Title Director Department Corporate Development Started 6/27/06 Number 62631734 Brian Martinez Title Manager Department Supplier Management Started 4/2/09 Number 74191750 Nichole Hinton Title Accounts Payable Department Accounting Started 1/15/04 Number 86604477 Samir Frangieh Title Director Department Advertising Started 12/26/14 Number 57213674 Chad Adams Title Vice President Department Human Resources Started 5/1/05 Number 17190720 Tiffany Suarez Title Manager Department Benefits Started 7/5/07 Number 80246137 Kelly Li Title Project Manager Department Facilities Started 10/30/05 Number 26322534 Jessica Baker Title Senior Counsel Department Litigation Started 11/2/09 Number 54257068 Anthony Davis Title Manager Department Security Started 1/22/16 Number 34726670 Seth Booth Title Director Department Distribution Started 12/1/04 Number 95758779 Shelley Hernandez Title Director Department Information Technology Started 10/2/16 Number 22353388 Pamela Sanchez Title Manager Department Design Started 5/22/16 Number 14126891 Michael Johnson Title Vice President Department Southern Region Started 9/20/08 Number 20916070 Monica Jones Title Vice President Department Western Region Started 2/10/02 Number 83643118 Patrick Pena Title Manager Department Payroll Started 8/24/01 Number 76083602 Chris James Title President & CEO Department Office of the President Started 8/31/10 Number 51031348 Travis McCormick Title Sr. Vice President Department Sales Started 3/16/21 Number 90027538 Cynthia Moore Title Accounts Receivable Department Accounting Started 7/11/04 Number 83614611 Wayne Gallagher Title Manager Department Networks Started 11/9/21 Number 82353722 Boaz Cohen Title Manager Department Quality Assurance Started 6/11/16 Number 71796590 Tiffany Johnston Title Audit Department Accounting Started 5/28/19 Number 15984194 Megan Medina Title Vice President Department Research & Development Started 9/2/11 Number 42219861 Brandi Phillips Title Analyst Department On Boarding Started 11/28/11 Number 25540093 Gina Smith Title Executive Assistant Department Office of the President Started 12/5/15 Number 83996210 Juan Moreno Title General Counsel Department Legal Started 4/5/12 Number 53512520 John Scott Title Analyst Department Logistics Started 6/3/16 Number 91200294 Kevin Perry Title Director Department Production Started 8/18/19 Number 60292961

Take a closer look at the two exceptions from earlier: Chris Adams and Monica Jones.

Even though Chris had no manager in the Excel data, Lexchart creates the CEO card at the top.

Organization chart with CEO atop the hierarchy

Using curved, instead of elbow, lines shows Monica's two managers.

Employee with two managers in org chart

You can design the chart to highlight whatever parts of the organizational structure you need.

Conclusion

Let Excel do what Excel does well: compile and standardize your organizational data. Use Lexchart to present that data as an organizational structure.


Microsoft Excel ↩︎