Creating a hierarchy chart in Excel can be a powerful way to organize and present information, making complex data more understandable. This step-by-step guide will walk you through the process, from creating the chart to customizing it to fit your needs.
Before we dive in, ensure you have a version of Excel that supports the Recommended Charts feature. This guide uses Excel 365 for demonstration, but the process is similar in other versions.
Preparing Your Data
Before creating the chart, organize your data in a table format. Hierarchy charts work best with data that has a clear hierarchical structure, such as departments, sub-departments, and employees.
For this example, let's use a simple dataset with columns for 'Level 1', 'Level 2', and 'Level 3' categories:
| Level 1 | Level 2 | Level 3 |
|---|---|---|
| Sales | East | New York |
| Sales | East | Boston |
| Sales | West | Los Angeles |
| Marketing | Online | Social Media |
Creating the Hierarchy Chart
With your data ready, follow these steps to create the hierarchy chart:
1. Select your data, including the headers. Click on the 'Insert' tab in the ribbon, then click on 'Recommended Charts'. Excel will suggest several chart types based on your data.
2. In the 'Insert Chart' dialog box, look for the 'All Charts' tab. Here, you'll find the 'Hierarchy' chart type. Click on it, then click 'OK'. Excel will insert a basic hierarchy chart using your data.
Customizing the Hierarchy Chart
Now that you have a basic chart, it's time to customize it to fit your needs. Here are some common customizations:
1. **Change Chart Title**: Click on the chart title, then type in your desired title. You can also change the font, size, and color using the 'Home' tab in the ribbon.
2. **Change Series Colors**: Select the series you want to change (in this case, the levels of hierarchy), then click on the 'Design' tab in the ribbon. Here, you can change the fill color, border color, and more.
![[FREE] Determine Correlation of Data Sets Using Bubble Chart in Excel](https://i.pinimg.com/originals/8a/a3/d6/8aa3d68afb6786211b534738eaa90914.jpg)
![[FREE] TOP 61 Excel Charts You Need to Know](https://i.pinimg.com/originals/dc/10/1b/dc101b9ebf862bd18bff43c364436b06.jpg)
3. **Add Data Labels**: To add data labels, click on the 'Add Chart Element' dropdown in the 'Design' tab, then click on 'Data Labels'. This will add labels to each level in your hierarchy chart.
Working with Hierarchy Chart Data
One of the benefits of using a hierarchy chart is the ability to drill down into the data. Here's how to do it:
1. **Expand/Collapse Levels**: Click on the '+' or '-' icons next to a level to expand or collapse it. This allows you to show or hide lower levels in the hierarchy.
2. **Filter Data**: Right-click on a level, then click on 'Filter'. This will allow you to filter the data in that level, showing only the data you want to see.
Updating Your Hierarchy Chart
To update your hierarchy chart with new data, simply add or remove rows from your data table. The chart will automatically update to reflect the changes.
If you want to add a new level to your hierarchy, you can do so by adding a new column to your data table. The chart will then display the new level when you update it.
Creating and customizing a hierarchy chart in Excel can help you present complex data in a clear and understandable way. With a little practice, you'll be able to create powerful hierarchy charts that engage your audience and communicate your message effectively.