How to Insert a Treemap Chart in Excel

Treemap charts are a powerful way to visualize hierarchical data, but many people don't realize they're built right into modern versions of Excel. If you've ever tried to show how different parts contribute to a whole - like product sales by category or website traffic by source - a treemap can be more intuitive than a traditional pie or bar chart. This tutorial will walk you through exactly how to prepare your data, create a treemap chart in Excel, and customize it to tell a clear and compelling story.

What Exactly Is a Treemap Chart?

A treemap chart displays hierarchical data as a set of nested rectangles. Think of it like a diagram of a tree's branches and leaves, but with rectangles instead of lines. Here’s how it breaks down:

• Parent Groups: The largest rectangles, or "branches," represent the main categories of your data.
• Sub-categories: Inside each main rectangle are smaller rectangles, or "leaves." These represent the sub-categories that belong to that main parent group.
• Value and Proportion: The size of each rectangle - both parent and child - is proportional to its value. A sub-category with high sales will take up more space than one with low sales. This makes it instantly clear which components are the most significant.
• Color Grouping: The color of the rectangles is used to distinguish the main parent categories. All sub-categories within a parent group will share the same color.

For example, you could track store sales. The parent categories might be "Electronics," "Apparel," and "Home Goods." Inside the "Electronics" rectangle, you’d see smaller rectangles for "Laptops," "Phones," and "Headphones," with the size of each determined by its sales figures. All rectangles in the "Electronics" group would be blue, while all "Apparel" rectangles would be green, and so on.

When Should You Use a Treemap Chart?

Treemaps excel at showing "part-of-a-whole" relationships within a data structure that has at least two levels. They are perfect for answering questions like, "What are our best-selling product lines, and which specific products drive their success?"

Common Uses for Treemap Charts:

• Sales Performance: Visualizing sales by region, then by salesperson, then by product category. This helps managers easily spot top performers and lagging areas.
• Website Analytics: Breaking down pageviews by channel (e.g., Organic, Social, Direct) and then by specific landing page within each channel.
• Budget Allocation: Showing how a company's budget is distributed across departments and then into individual projects or expense types within those departments.
• Inventory Analysis: Grouping products by supplier and visualizing the stock levels for each item to identify which suppliers are holding the most inventory value.

Pros and Cons of Using Treemaps

While powerful, treemaps aren't a one-size-fits-all solution. Here’s a quick rundown of their strengths and weaknesses:

Pros:

• Efficient Use of Space: They pack a lot of information into a compact area, making them ideal for dashboards.
• Great for Spotting Patterns: It’s easy to see the largest and smallest contributors at a glance without reading any numbers.
• Handles Large Datasets: Treemaps can effectively display hierarchies with thousands of sub-categories, something that would be impossible with a pie chart.

Cons:

• Not Great for Precise Comparisons: It can be difficult for the human eye to accurately compare the sizes of non-adjacent rectangles, especially if they are similarly sized. Bar charts are better for precise comparisons.
• Can Become Cluttered: If you have data with very small values, their corresponding rectangles can become too tiny to be useful or even visible.

How to Prepare Your Data for a Treemap in Excel

Success with treemaps starts with properly structured data. You can't just throw any table at Excel and expect it to work. Your data must be organized in columns that represent the hierarchy levels.

To create a two-level hierarchy, you need at least three columns:

1. Parent Category: The highest-level grouping (e.g., Department).
2. Sub-category: The items within each parent group (e.g., Expense Type).
3. Value: The numerical data that will determine the size of the rectangles (e.g., Budget Amount).

Let's use a business expense report as an example. Here’s how you would format the data:

Key Data Formatting Tips:

• Use Headers: Make sure each column has a clear header.
• Group Items Logically: While not strictly required, grouping all sub-categories for a single parent category together in your table makes it easier to read and manage.
• No Blank Rows or Columns: Ensure there are no empty rows or columns within your data range, as this can cause Excel to misinterpret the data selection.

Step-by-Step Guide: Inserting a Treemap Chart in Excel

Once your data is properly structured, creating the chart takes just a few clicks. It's a feature available in Excel 2016 and all later versions, including Microsoft 365.

Step 1: Select Your Data

Click and drag your cursor to select the entire data range you want to visualize, including all headers. Using our example above, you would select cells A1 through C10.

Step 2: Navigate to the Insert Tab

At the top of the Excel window, click on the Insert tab in the main ribbon.

Step 3: Locate the Charts Group and Find Hierarchy Charts

In the "Charts" section of the ribbon, look for a small icon called "Insert Hierarchy Chart." It looks like a set of nested rectangles, representing a treemap. Click this icon.

Step 4: Choose the Treemap Chart

A dropdown menu will appear with two options: "Treemap" and "Sunburst." Click on Treemap.

Excel will instantly generate a treemap chart from your selected data and place it on your worksheet. It will automatically assign colors to your parent categories (Marketing, Sales, Operations) and calculate the size of each rectangle based on the "Amount" column.

Customizing Your Excel Treemap Chart for a Polished Look

The default treemap is functional, but a little customization can make it much more effective and professional.

Changing the Chart Title and Colors

First, give your chart a descriptive title. Simply click on the default "Chart Title" text box at the top of the chart and type a new one, like "Quarterly Expenses by Department."

To change the color scheme, click on the chart to select it, which will reveal two new tabs in the ribbon: Chart Design and Format. On the Chart Design tab, click "Change Colors" to browse different color palettes that Excel provides. You can also experiment with different "Chart Styles" to add effects like borders or soft shadows.

Fine-Tuning Data Labels

Your treemap's labels have a big impact on readability. Right-click on one of the rectangles in your chart and select "Format Data Series..." from the menu. This opens a panel on the right side of your screen. Click the icon that looks like a bar chart (Series Options) and expand the "Label Options" dropdown.

Here you’ll find a few settings for how the category headers are displayed:

• Banner: This is the default. It places the parent category name ("Marketing," "Sales," etc.) at the top of its group in a distinct shaded "banner" area. This is great for making the primary categories stand out.
• Overlapping: If a rectangle is large enough, its name will be displayed directly on top of it. This can be useful for reducing visual clutter if the banners feel too heavy.
• None: This hides the headers completely, which is useful if you only want to show the proportional shapes.

You can also control what's shown inside the smaller sub-category boxes. Right-click on a rectangle and choose "Format Data Labels...". You'll see checkboxes to display the Series Name, Category Name, and Value. You can choose to show any combination, such as displaying the expense type along with its budgeted amount.

Common Treemap Issues and Quick Fixes

Sometimes your chart might not look quite right on the first try. Here are a few common issues and how to solve them.

Problem: The Treemap Isn't Grouping My Data Correctly.

This is almost always a data structure issue. Double-check that your first column is the parent category and the second is the sub-category. If you have them reversed, Excel won’t know how to create the hierarchy.

Problem: Some Labels are Hidden or Unreadable.

Excel will automatically hide labels for rectangles that are too small to contain them. If important labels are missing, you have a few options:

• Try switching the "Label Options" from Banner to Overlapping.
• Manually decrease the font size of the data labels in the "Format Data Labels" pane.
• Consider if your dataset might be too granular for a treemap. If you have dozens of micro-categories with tiny values, they will inevitably get lost. In that case, you might need to combine smaller categories into a larger "Other" group.

Problem: I Can't Find the Treemap Option in My Excel.

The treemap chart type was introduced in Excel 2016. If you're using Excel 2013 or an earlier version, this feature will not be available. You’ll need to upgrade to a more recent version of Office or use Microsoft 365 to access it.

Final Thoughts

You've now seen how to master treemaps in Excel, from structuring your data correctly to customizing the chart for clarity and impact. They are a fantastic tool for simplifying complex hierarchical data, providing a quick, visual snapshot of what matters most in your reports without overwhelming your audience with numbers.

While creating charts in Excel is a vital skill, we know that getting insights from your business data shouldn't always involve manual data prep and wrestling with customization menus. At Graphed, we connect directly to your live data sources like Google Analytics, Shopify, and Salesforce. Instead of building charts click by click, you can create entire real-time dashboards just by asking questions in plain English - turning a full afternoon of reporting work into a 30-second conversation.