How to Create a Pyramid Chart in Excel

Creating a pyramid chart in Excel isn’t as simple as clicking a button, because there isn't a direct option for it. But with a clever workaround, you can build a customized, data-driven pyramid chart perfectly suited for your reports and dashboards. This article will guide you through the two best methods for making a pyramid chart in Excel: the detailed, data-driven approach and a quick alternative for conceptual visuals.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What is a Pyramid Chart (and When Should You Use One)?

A pyramid chart, also known as a triangle chart or triangle diagram, is used to visualize data in a hierarchy. The chart is divided into horizontal segments, with the largest segment at the bottom and the smallest at the top, forming the shape of a pyramid. Each segment represents a different category or stage, and its size (width) is proportional to its value in the dataset.

This structure is highly effective for showing foundational concepts or depicting a process with progressive stages. It instantly communicates relationships of size, quantity, and order.

You’ll commonly see a pyramid chart used to illustrate concepts like:

• Sales and Marketing Funnels: Showcasing the customer journey from initial awareness at the wide base to conversion at the narrow tip. Stages might include website visitors, leads, qualified leads, and finally, customers.
• Organizational Structures: Representing the hierarchy of a company, from a large base of junior employees to senior management and executives at the top.
• Market Segmentation: Visualizing a target market, starting with the total addressable market (TAM) at the bottom and narrowing down to your specific niche customer segment.
• Maslow's Hierarchy of Needs: A classic psychology example where physiological needs form the base and self-actualization sits at the apex.

Method 1: The Stacked Bar Chart Trick (For Data-Driven Pyramids)

This is the most accurate method for creating a pyramid chart based on specific data values. It involves a clever trick where we create a stacked bar chart and format it to look like a pyramid. It might sound unusual, but it gives you complete control over the final visual.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 1: Prepare Your Data Structure

The key to making this work is structuring your data correctly. A regular list of values won't be enough. You need to create a helper column that will center the pyramid segments.

Let's use a simple sales funnel as our example. Imagine you have the following data:

• Website Visitors: 20,000
• Leads Generated: 5,000
• Qualified Leads: 1,000
• New Customers: 250

To prepare this for our chart, set up a table with three columns:

1. Spacer (Helper Column): This column will contain a formula to create blank space on the left side of our chart, which centers the visible bars.
2. Value: This is a copy of your actual data values. You'll need this for later adding data labels.
3. Stage (Your Category): The names of each stage in your pyramid. It is best practice to arrange them as they appear in the funnel from top to bottom.

To calculate the Spacer value, we use a simple formula. Find the maximum value in your dataset (in our case, 20,000). The formula for each spacer cell is: =(MaxValue - CurrentValue) / 2.

The "Invisible Spacer" values push the visible "Value" segments toward the center, creating the aligned pyramid effect we need. It's also important to arrange your stages in reverse order of how you want them to appear in the pyramid (e.g., from smallest value to largest value).

Step 2: Insert a Stacked Bar Chart

Now that your data is ready, you can create the initial chart.

1. Highlight your data, but only include the "Stage," "Invisible Spacer," and the main "Value" columns.
2. Go to the Insert tab on the ribbon.
3. Click on Insert Column or Bar Chart.
4. Select the 2-D Stacked Bar Chart.

You’ll get a chart that looks something like this. Far from a pyramid, but we’re on the right track!

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 3: Transform the Bars into a Pyramid

Here’s where the magic happens. We’ll make a series of adjustments to make the chart look like a pyramid.

1. Format the 'Spacer' Segments to Be Invisible

First, we need to hide the blue parts of the bars (our "Invisible Spacer" data).

• Click on any of the blue bar segments to select the entire series.
• Right-click and choose Format Data Series.
• In the Format Data Series pane that appears on the right, click the Fill & Line icon (the paint bucket).
• Under Fill, select No fill.
• Under Border, select No line.

Now, the spacer segments are transparent, which should give an illusion of floating, but aligned segments. You can delete the "Invisible Spacer" series from the legend by clicking "Invisible Spacer" in the legend and pressing the DELETE key on your keyboard.

2. Change the Shape of Each Series to Full Pyramid

Next, you'll convert each bar to the shape of a "Full Pyramid". Repeat these steps for each series in the chart.

• Click any orange bar in the data set and right-click to open the Format Data Series… menu again.
• Click Series Options — the icon with three green vertical bars.
• Click Series Options drop-down menu and choose one of the series — e.g., New Customers in the orange color.
• Expand the Series Options menu and select a Full Pyramid for the column shape.
• Repeat the same for each of the series (bar colors). This important step is because, currently, each category for stages of the funnel is a separate series.

By the time you are done, each bar color will become a 2-D rendering of a pyramid. However, the bars will still be horizontal.

The last step is to make the Series Overlap 100% and Gap Width 0%. This will still be in the Series Options.

Simply move each slider to achieve the desired look.

Step 4: Finishing Touches and Cleanup

Finally, let’s clean up the chart to make it professional and easy to read.

1. Remove the Legend: Click on the legend (the box that says "Value" and "Invisible Spacer") and press Delete.
2. Delete the Top Axis: Click on the horizontal axis labels at the top of the chart and press Delete.
3. Add data labels for categories from your stages list on the left side, for a cleaner and intuitive way to read this pyramid chart.
4. Add a Title: Click on "Chart Title" and give your pyramid chart a clear, descriptive name like "Quarterly Sales Funnel."

Method 2: SmartArt Graphics (For Quick Conceptual Pyramids)

What if you don’t need your pyramid to be proportional to specific data? Perhaps you just want to illustrate a concept, like a business hierarchy, without attaching hard numbers. In this case, Excel’s SmartArt is a much faster option.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

When to use this method

If the pyramid chart you want to create is for a presentation and you need it quickly to illustrate different stages or a user journey without detailed statistics, SmartArt can be an effective alternative. It provides various chart styles, including a pyramid chart, for conceptual hierarchy visualizations without detailed data. Here’s how to add a SmartArt pyramid chart:

1. Open an Excel sheet. Click Insert on the menu, then go to the illustrations menu. Choose SmartArt.
2. The SmartArt Graphic library will open. On the left panel, pick Pyramid from the options.
3. From the options, select the Basic Pyramid for simple conceptual hierarchy visuals.

Final Thoughts

While Microsoft Excel doesn't offer a ready-made option, creating a custom pyramid chart is completely achievable. By using the stacked bar chart technique, you can build precise, data-driven visuals, or you can opt for the faster SmartArt method when illustrating a concept is more important than granular data accuracy. Both approaches give you the tools to better represent hierarchical data.

Manually wrangling data and building charts like this in Excel can often feel slow and repetitive, especially when your data lives in multiple places like Google Analytics, Shopify, and your CRM. We built Graphed to eliminate that friction. Instead of spending hours exporting CSVs and formatting charts, you can connect your data sources in seconds and simply ask for the visuals you need in plain English. Graphed automatically builds real-time, interactive dashboards, so you can stop wrestling with chart settings and get back to finding insights that grow your business.