✦Template Printable Ideas Insights

"Create Hierarchical Charts in Excel: A Step-by-Step Guide"

By

Creating a hierarchical chart in Excel, also known as an organizational chart or a family tree, can help you visualize complex relationships and structures. This guide will walk you through the process using Merge and Sort features to create an efficient and user-friendly chart.

Before we dive in, make sure you have Microsoft Excel installed on your computer. For this guide, we'll use Excel 2016, but the steps are similar in other versions. Now, let's get started!

Preparing Your Data

To create a hierarchical chart, you first need to organize your data in a specific format. Let's assume you want to create a family tree. You'll need three columns: 'Name', 'Level', and 'Manager'. The 'Level' column indicates the hierarchy level, and the 'Manager' column lists the direct superior of each person.

How to Make an Org Chart in Excel (+ video tutorial)
How to Make an Org Chart in Excel (+ video tutorial)
How To Create Charts and Graphs in Excel
How To Create Charts and Graphs in Excel
How to Make an Organizational Chart in Excel - Tutorial
How to Make an Organizational Chart in Excel - Tutorial

Here's a sample dataset: | Name | Level | Manager | |-------|-------|---------| | John | 1 | | | Jane | 2 | John | | Bob | 2 | John | | Alice | 3 | Jane | | Chris | 3 | Bob |

Sorting Your Data

After organizing your data, sort it by the 'Level' column in ascending order. This will arrange your data from the top of the hierarchy to the bottom.

How to make a male_female ratio chart in Excel
How to make a male_female ratio chart in Excel
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 a Chart in Excel
How to Make a Chart in Excel

To sort your data: 1. Select the entire dataset. 2. Click the 'Sort & Filter' button in the 'Home' tab. 3. Choose 'Sort by...' > 'Level' > 'Smallest to Largest' (or 'Ascending').

Merging Duplicate Names

Now that your data is sorted, merge the duplicate names in the 'Name' column to create a single, condensated list of names. This will make it easier to create a hierarchical chart.

To merge duplicate names: 1. Select the 'Name' column. 2. Click the 'Merge & Center' button in the 'Home' tab's 'Alignment' group. 3. Choose 'Merge & Center' from the dropdown menu.

How to Make an Organizational Chart in Excel - Tutorial
How to Make an Organizational Chart in Excel - Tutorial
the advanced excel chart sheet is shown in green and has instructions on how to use it
the advanced excel chart sheet is shown in green and has instructions on how to use it
Creating AREA Chart in Excel (Easy Steps)
Creating AREA Chart in Excel (Easy Steps)
How to create a progress chart.#excel #microsoft #microsoftexcel #office #word #o #powerpoint.
How to create a progress chart.#excel #microsoft #microsoftexcel #office #word #o #powerpoint.
Master Excel Charts for Stunning Travel Flyers
Master Excel Charts for Stunning Travel Flyers
How to Make Charts and Graphs in Excel | Smartsheet
How to Make Charts and Graphs in Excel | Smartsheet

Creating the Hierarchical Chart

With your data prepared, it's time to create the hierarchical chart using the 'Sort & Filter' feature.

To create the chart: 1. Select the entire dataset. 2. Click the 'Sort & Filter' button in the 'Home' tab. 3. Choose 'Sort by...' > 'Level' > 'Largest to Smallest' (or 'Descending'). This will sort the data based on the 'Level' column in descending order, separating the levels and creating a hierarchical structure.

.Customizing the Chart

Once you've created the chart, you can customize it to make it more visually appealing and easier to understand. Add formatting, adjust the font, apply shading, or use color to differentiate between levels.

Actual vs Target Charts in Excel: How to make variance charts in Excel with floating markers or bars
Actual vs Target Charts in Excel: How to make variance charts in Excel with floating markers or bars
How to Create Multi-Category Chart in Excel
How to Create Multi-Category Chart in Excel
Create a Chart Fast in Excel with Alt + F1
Create a Chart Fast in Excel with Alt + F1
the info sheet shows how to use excel dashboards for your business plan and workflow
the info sheet shows how to use excel dashboards for your business plan and workflow

To customize your chart: 1. Select the entire chart. 2. Use the 'Fill' and 'Font' options in the 'Home' tab to apply formatting. 3. Adjust the column widths and row heights to enhance readability. 4. Consider adding a header row or total row to provide additional context.

Adding Subordinates

If you need to add subordinates to an existing chart, simply update the 'Manager' column with the new superior's name and repeat the sorting process to create the new hierarchy.

To add subordinates: 1. Update the 'Manager' column with the new superior's name. 2. Select the entire dataset. 3. Click the 'Sort & Filter' button in the 'Home' tab. 4. Choose 'Sort by...' > 'Level' > 'Largest to Smallest' (or 'Descending') to recreate the hierarchical chart with the new subordinate.

Creating a hierarchical chart in Excel allows you to visualize complex relationships and structures in a user-friendly format. With the techniques outlined in this guide, you can create efficient and customizable charts to suit your specific needs. Happy charting!