✦Template Printable Ideas Insights

Automate Excel Org Charts: Turning Data into Hierarchy

By

Are you looking to create an organizational chart in Excel from your data? You're in the right place. This step-by-step guide will walk you through the process, ensuring you have a clear and well-structured org chart that translates your data into an easy-to-understand visual representation.

Before we dive into the process, ensure your data is clean and properly formatted. You should have columns for at least 'Name', 'Position', and 'Direct Reports' (those who report directly to them). This will make creating the org chart a breeze.

Preparing Your Data

Starting with a well-organized spreadsheet is crucial. If your data is a mess, take some time to clean it up. Remove any duplicate entries, ensure names are consistent (John Doe, John J. Doe, and Jonathan Doe should all be John Doe if that's the case), and confirm that all columns are filled accurately.

Create Org Chart
Create Org Chart
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 Make an Org Chart in Excel (+ video tutorial)
How to Make an Org Chart in Excel (+ video tutorial)

For creating the org chart, you'll primarily use the 'Name' and 'Direct Reports' columns. The 'Position' column can be used for further customization and filtering in the chart later on.

Formatting Your Data for the OEUF Tool

Excel has a built-in organizational chart feature called Organization Chart (OEUF). To use it effectively, we need to format your data in a specific way. In the 'Direct Reports' column, instead of listing names, list RNAs (RelativeIDs in Excel). These are unique numerical IDs assigned to each name in the chart. You can find these by right-clicking the cell and selecting "Insert Offset" from the dropdown.

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)
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
41 Organizational Chart Templates (Word, Excel, PowerPoint, PSD)
41 Organizational Chart Templates (Word, Excel, PowerPoint, PSD)

Once you have the RNA for each person, replace the names in the 'Direct Reports' column with these RNAs. This will allow Excel to connect people hierarchically, creating your organizational chart.

Formatting Your Data for the Reptile Tool

If you're looking for a more flexible alternative, consider the Reptile add-in for Excel. It offers more customization options but requires additional setup. After installing Reptile, you'll need to create a hierarchy list from your data using a specific format: '- Name - Position - RNA'. Each line represents a new node in your org chart.

Like the OEUF method, you'll need to use RNAs instead of names to establish the hierarchy. Once your hierarchy list is ready, you can paste it into Reptile to create your org chart.

the organizational chart for an organization
the organizational chart for an organization
How to Make an Organizational Chart in Excel - Tutorial
How to Make an Organizational Chart in Excel - Tutorial
an info sheet with many different types of boats in it
an info sheet with many different types of boats in it
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...
7 Types of organizational structures
7 Types of organizational structures
5+ Organization Chart Template Excel Sample | Template Business PSD, Excel, Word, PDF
5+ Organization Chart Template Excel Sample | Template Business PSD, Excel, Word, PDF

Creating the Organizational Chart

Now that your data is prepared, it's time to create the org chart. We'll cover both the OEUF and Reptile methods here.

**Using Excel's Organization Chart (OEUF)**

  1. Click on an empty cell in your data. Go to the 'Insert' tab, then click on 'Organization Chart'.
  2. In the 'Choose a layout' section, select 'Create a new organization chart'. Click 'OK'.
  3. In the 'Select a table or range' dialog box, highlight your data and click 'OK'.
  4. Excel will create a basic org chart. You can customize this further using the 'Design' tab.

**Using the Reptile Add-in**

5+ Organization Chart Template Excel Sample
5+ Organization Chart Template Excel Sample
How to Make an Organizational Chart in Excel - Tutorial
How to Make an Organizational Chart in Excel - Tutorial
Plantilla de infografía de organigrama | Vector gratuito
Plantilla de infografía de organigrama | Vector gratuito
  1. After installing Reptile, you'll find a new tab called 'Reptile' in your Excel ribbon. Click on it.
  2. In the 'Define Reptile Structure' section, click on 'Choose File', select your hierarchy list, and click 'Open'.
  3. Next, choose a chart type (Tree or Fishbone), then click 'Create Map'.
  4. Finally, click 'Create Picture' to generate your org chart.

Customizing Your Organizational Chart

Now that you have your basic org chart, it's time to make it look as good as it functions. You can add shapes, change colors, adjust text styles, and more. Excel offers a lot of flexibility for customization.

To save your chart for future use, be sure to group all the shapes together (right-click and select 'Group'), then save the org chart as a separate Excel file or as a picture.

Creating an org chart from data in Excel might seem like a daunting task, but with the right preparation and tools, it's surprisingly straightforward. By following this guide, you'll have a professional-looking org chart in no time. Happy charting!