How to Make a Hierarchy Chart in Excel

Building a hierarchy chart in Excel to show reporting structures or project breakdowns doesn't have to feel like a complex puzzle. With the right tools, you can move from a simple list of names to a clean, professional-looking chart in just a few minutes. This guide will walk you through two effective methods for creating hierarchy charts right within your spreadsheet.

What is a Hierarchy Chart and When Should You Use One?

A hierarchy chart is a diagram that shows the relationships and structure within an organization or system, flowing from a single origin point at the top down to its subordinate elements. Think of it as an information tree. The most common example is a company's organizational chart (org chart), but their use extends far beyond that.

They are incredibly useful for visualizing any kind of top-down system. You might use one to:

• Map out a company structure: Clearly show who reports to whom, from the CEO down to individual teams.
• Break down a project: Create a Work Breakdown Structure (WBS) to see major deliverables and the smaller tasks they comprise.
• Visualize a family tree: Trace lineage through generations.
• Explain a complex topic: Break down a broad subject into smaller, more manageable subcategories.
• Outline a website's architecture: Plan a sitemap showing how pages link together from the homepage.

Ready to build one? Let's get started.

Method 1: Building a Classic Hierarchy Chart with SmartArt

For most people looking to create a classic org chart, Excel's SmartArt feature is the fastest and most straightforward option. It's designed specifically for this purpose and handles the layout and connections for you.

Step 1: Get Your Data Ready

Before you even click "Insert," the secret to a stress-free chart is getting your data set up logically. All you need is a simple, two-column list that defines the relationships. One column should have the name of the person or item, and the second column should list who or what they report to.

Your data should look something like this:

Employee Name | Reports To Sarah Chen (CEO) | (leave blank for the top position) James Rodriguez (VP Marketing) | Sarah Chen Maria Garcia (VP Sales) | Sarah Chen Ben Carter (Marketing Manager) | James Rodriguez Olivia White (Content Lead) | Ben Carter Leo Kim (Sales Manager) | Maria Garcia Aisha Khan (Account Executive) | Leo Kim

This simple structure contains all the information Excel needs to understand the hierarchy.

Step 2: Insert the SmartArt Graphic

With your spreadsheet open, navigate to the ribbon at the top of the Excel window.

1. Go to the Insert tab.
2. In the "Illustrations" section, click on SmartArt.
3. A new window will pop up with a list of SmartArt categories on the left. Click on Hierarchy.
4. You'll see several layout options. The most common choice is the first one, labeled "Organization Chart." You can also choose layouts that include spaces for pictures or job titles. Click the option you want and hit "OK."

Excel will place a default SmartArt graphic onto your worksheet.

Step 3: Add Your Data Using the Text Pane

While you can click into each shape and type directly, the most efficient way to add your data is through the Text Pane. If it isn't already visible to the left of your chart, click the small arrow icon on the left edge of the SmartArt frame to open it.

The Text Pane works like a bulleted list. Each bullet point represents a box in the chart. To create the hierarchy, you’ll use the Tab key to "demote" an item (making it a subordinate) and Shift + Tab to "promote" it (moving it up a level).

Let's use our earlier data example:

1. Delete the default text in the Text Pane.
2. Type your top-level person at the first bullet point: Sarah Chen (CEO).
3. Press Enter to create a new bullet.
4. Type the next person who reports to Sarah: James Rodriguez (VP Marketing). Now, press the Tab key to indent this line. You'll instantly see the chart update, showing James reporting to Sarah.
5. Press Enter. The new line will keep the same indentation. Type Maria Garcia (VP Sales).
6. To add someone who reports to James, press Enter to create a new line, type Ben Carter (Marketing Manager), and then press Tab one more time to push him to the next level down.
7. Continue this process for all your data, using Tab to move down a level and Shift + Tab (or your backspace/delete key at the start of a line) to move up a level.

This demote/promote method gives you full control over the chart's structure directly from your keyboard.

Step 4: Customize Your Hierarchy Chart

Once your data is in, you can fine-tune the chart's appearance. When your SmartArt is selected, two new tabs will appear on the Excel ribbon: SmartArt Design and Format.

• SmartArt Design Tab: This is where you can make broad changes.
• Format Tab: This tab gives you granular control over individual shapes. You can change the color of a single box, adjust its outline, or modify the text font and color inside it.

Method 2: Visualizing Quantitative Hierarchies with Treemap and Sunburst Charts

What if your hierarchy isn't just about structure but also about data - like sales figures for different regions or website traffic from various countries? In this case, Excel's built-in Treemap and Sunburst charts are better options. They visualize hierarchical data where the size of each element represents its quantitative value.

Preparing Your Data for Treemap/Sunburst Charts

The data setup for these charts is a bit different. You need columns that represent each level of your hierarchy, plus a final column for the numerical value.

Imagine you want to chart book sales by genre and then by author. Your data would look like this:

Genre | Author | Sales Sci-Fi | Philip K. Dick | 25000 Sci-Fi | Isaac Asimov | 45000 Fantasy | J.R.R. Tolkien | 75000 Fantasy | Brandon Sanderson | 50000 Mystery | Agatha Christie | 60000 Mystery | Tana French | 20000

Here, "Genre" is the top level of the hierarchy, and "Author" is the subordinate level, with "Sales" being the value that determines the size of each shape.

Creating a Treemap Chart

A Treemap chart shows your hierarchical data as a set of nested rectangles. The top-level categories are the large rectangles, and the subcategories are smaller rectangles inside them. The area of each rectangle is proportional to its value.

1. Select your entire data range, including the headers.
2. Go to the Insert tab.
3. In the "Charts" section, find the small icon that looks like a blue-and-green set of boxes. This is the "Insert Hierarchy Chart" menu.
4. Click on the icon and select Treemap.

Excel will instantly generate a treemap chart. You’ll be able to see at a glance that "Fantasy" is the biggest sales category and that "J.R.R Tolkien" is the major contributor within it.

Creating a Sunburst Chart

A Sunburst chart displays hierarchical data as a series of rings. The central circle represents the top-level category, with subcategories shown as arcs in the outer rings.

1. Select your data range, same as before.
2. Go to the Insert > Hierarchy Chart menu.
3. This time, select Sunburst.

The resulting chart gives a "donut" view of your data, making it easy to see how the whole breaks down into its component parts at each level.

Tips for Making Your Hierarchy Chart Clear and Effective

No matter which method you use, a few best practices will help make your chart easy to understand.

• Keep It Simple: Avoid cluttering each box with too much text. Use names and titles, and save the detailed job descriptions for another document.
• Use Consistent Colors and Shapes: If you customize, try to use color to denote levels or departments. For example, make all manager-level boxes one color and all department-head boxes another.
• Choose the Right Chart for the Job: Use SmartArt for purely organizational, structural views. Use Treemap or Sunburst when you have supporting numbers you need to display visually.
• Use Clear Labels: Ensure all your titles and data labels are readable. Avoid acronyms or jargon that might confuse viewers.

Final Thoughts

From mapping out your team structure with SmartArt to visualizing sales data with a Treemap, Excel provides more than one way to create effective hierarchy charts. The key is to start with well-structured data, which frees you up to focus on making the visual output clear, easy to read, and useful for your audience.

While Excel is great for these one-off charts, updating them and pulling new data for your weekly reports can easily become a time-consuming manual task. We built Graphed to cut out that repetitive work. You connect your data sources just once, and instead of wrestling with chart settings, you can ask in plain English for what you need - like, "Show me a dashboard of team KPIs from HubSpot" or "generate a year-over-year sales report from Shopify." It's all designed to give you instant, real-time insights without the busywork of manual reporting.