✦Template Printable Ideas Insights

How to Create an Org Chart from Excel Data

By

Are you a manager or HR professional responsible for monitoring your company's organizational structure? Creating an org chart with Excel data can be a game-changer in visualizing and understanding your team's hierarchy and departments. Here's a step-by-step guide on how to create an organizational chart using Excel, making it easier to navigate and manage your team.

Before we dive in, ensure you have the latest version of Microsoft Excel installed on your computer. You'll also need a dataset containing employee information like ID, name, title, department, and manager's ID to connect the hierarchical relationships.

Preparing Your Excel Data

Start by opening your employee dataset in Excel. The first two columns should contain the employee's ID and name. The third column should have the employee's direct manager's ID. This will help us create relationships between employees.

Create Org Chart
Create Org Chart
How to Make an Org Chart in Excel (+ video tutorial)
How to Make an Org Chart in Excel (+ video tutorial)
How to Create an Organizational Chart Linked to Data in Excel (Easy & Dynamic)
How to Create an Organizational Chart Linked to Data in Excel (Easy & Dynamic)

For instance, if John Doe (ID: 1) is the manager of Jane Smith (ID: 101), the 'Manager ID' column for Jane's row should contain '1'. This will form the connection between John and Jane in the org chart.

Optimizing Your Data Structure

Using a structured dataset is crucial for creating a clean and accurate org chart. Ensure our data has the following columns:

How to Create the Organizational Chart You Know Your Business Needs | Process Street | Compliance Operations Platform
How to Create the Organizational Chart You Know Your Business Needs | Process Street | Compliance Operations Platform
a computer screen with an arrow pointing to the text boss how did you make this org chart?
a computer screen with an arrow pointing to the text boss how did you make this org chart?
How to Create Org Charts in Excel — The Step-by-Step Guide | EdrawMax
How to Create Org Charts in Excel — The Step-by-Step Guide | EdrawMax
  • Employee ID
  • Employee Name
  • Manager ID
  • Title/Position
  • Department

You can add or remove columns as needed, but these are the essentials for creating a comprehensive org chart.

Formatting Your Data

Next, format your data table. You can apply filters, sort, and use conditional formatting to highlight certain information, like hierarchy levels or departments.

For example, apply conditional formatting based on the 'Manager ID' column to color-code employees within the same reporting line, making it easier to visualize the hierarchy.

How to Make an Organizational Chart in Excel - Tutorial
How to Make an Organizational Chart in Excel - Tutorial
How to Make an Organizational Chart in Excel - Tutorial
How to Make an Organizational Chart in Excel - Tutorial
How to Create Org. Charts in Excel linked to your Data
How to Create Org. Charts in Excel linked to your Data
Organogram Template Excel If you manage a team employee or busy household, you can easily man...
Organogram Template Excel If you manage a team employee or busy household, you can easily man...
an excel chart with two smiley faces and the word excel in green on top of it
an excel chart with two smiley faces and the word excel in green on top of it
the most useful excel chart info sheet
the most useful excel chart info sheet

Creating the Organizational Chart

Now that your data is structured and formatted, it's time to create the org chart. Excel 2013 and later versions have a built-in 'Organization Chart' feature that makes this process intuitive and efficient.

For earlier versions of Excel, you can use the 'SmartArt' feature or add-on tools like 'Office Tools' or 'Add-ink' to create an org chart.

Using the Organization Chart Feature

With your data ready, click on the 'Insert' tab in the Excel ribbon, then 'Organization Chart' in the 'Illustrations' group.

Create Organization Chart in Excel
Create Organization Chart in Excel
an info sheet with many different types of boats in it
an info sheet with many different types of boats in it
How To Create Charts and Graphs in Excel
How To Create Charts and Graphs in Excel

Choose the first layout (e.g., 'Organization Chart'), then click OK. This will open the 'Select Table or Range' dialog box. Highlight your data range (including headers) and click OK.

Formatting and Finalizing Your Org Chart

The org chart will now be created using your Excel data. You can add or remove shapes, change colors, and adjust layout options from the 'Design' tab in the 'Org Chart' group. This tab also provides options to show/hide connectors and display/hide layouts.

The final step is to update the org chart whenever you make changes to the underlying data. Right-click in the diagram area, select 'Update Chart' to reflect any new or modified information.

Congratulations! You've successfully created an organizational chart using Excel data. This visually appealing and informative tool will help you manage your team hierarchy more effectively. Keep your org chart up-to-date, and use it as a strategic resource for planning and decision-making within your organization.