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 Excel¹ 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.

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.

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.

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.

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.

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.

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.

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

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.

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

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 ↩︎