How to Create a Stacked Bar Chart in Excel

Showing how parts contribute to a whole is one of the most common tasks in data reporting. A stacked bar chart is the perfect tool for exactly that, letting you compare totals across categories while also seeing the breakdown of each total. This guide will walk you through exactly how to create, customize, and interpret stacked bar charts in Microsoft Excel.

What Exactly is a Stacked Bar Chart?

Think of it like a regular bar chart, but with an extra layer of information. Instead of just one bar representing a single value for each category, a stacked bar chart divides that bar into segments. Each segment represents a different sub-category, and the total length of the bar represents the grand total for that main category.

This makes them incredibly useful for answering questions like:

What are our total sales per quarter, broken down by product line?
Which marketing channels drove the most web traffic each month?
How have our regional revenue numbers changed over the last year, and which regions are contributing the most?

The goal is to compare the overall totals while also understanding the composition of each total. It’s a powerful way to see the big picture and the details at the same time.

How to Organize Your Data for a Stacked Chart

Before you click a single button in Excel, getting your data structure right is the most important step. A poorly organized table will lead to a confusing or incorrect chart. Excel expects your data in a simple, grid-like format.

Your table should have:

Categories on the Y-Axis: The items you want to compare (e.g., months, quarters, regions, product names) should be in the first column.
Sub-categories (Series) on the X-Axis: The parts that make up the whole (e.g., product types, traffic sources, sales reps) should be in the subsequent columns, each with its own clear header.
Values in the Cells: The intersections of your rows and columns should contain the numerical data that will determine the size of each segment.

Example Data Setup

Imagine you're a marketing manager for an online store, and you want to visualize your quarterly sales broken down by product category. Your data table in Excel should look something like this:

Notice how clear this is. ‘Quarter’ is our main category in the first column, and the product lines (‘Electronics’, ‘Home Goods’, ‘Apparel’) are our sub-categories in the headers. Excel can now easily understand how to build the chart.

How to Create a Stacked Bar Chart: A Step-by-Step Guide

With your data properly organized, creating the chart itself takes less than a minute. Let’s use the sample data from above.

Step 1: Select Your Data

Click and drag your mouse to highlight the entire data range you want to chart, including all the headers and the first column of categories. In our example, that would be from cell A1 to D5.

Step 2: Go to the Insert Tab

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

Step 3: Choose the Stacked Bar Chart

In the "Charts" section, click the icon that looks like a bar chart, which is labeled Insert Column or Bar Chart. A dropdown menu will appear. Look for the "2-D Bar" section and select the second option, which is the Stacked Bar. Hovering over it will show you a preview.

Congratulations! Excel will immediately generate a stacked bar chart and place it on your worksheet. It will look something like this:

It's functionally correct, but it’s not very polished. Now comes the fun part: making it clear, readable, and professional.

Making Your Stacked Bar Chart Shine: Customization Tips

A default Excel chart gets the job done, but taking a few extra minutes to customize it can dramatically improve its clarity and impact.

Polishing Your Chart Elements

You can add, remove, or modify chart elements by clicking the green "+" icon that appears on the top-right side of the chart when you have it selected.

Chart Title: The default title is usually just "Chart Title." Double-click it and write something descriptive, like "Quarterly Sales by Product Category." Be specific so your audience knows exactly what they’re looking at.
Axis Titles: It's good practice to label your axes. Check the "Axis Titles" box from the Chart Elements menu. Then, you can edit the title for the horizontal axis (e.g., "Total Sales in USD") and the vertical axis (e.g., "Quarter").
Data Labels: To show the exact value of each segment, check the "Data Labels" box. This adds the numbers directly onto the bars, which can save your audience from having to estimate values from the axis.
Legend: The legend tells you what each color represents. You can use the options next to "Legend" in the Chart Elements menu to move it to the top, left, bottom, or right to find a position that fits your chart's layout best.

Adjusting Colors and Style

You’re not stuck with Excel's default blue, orange, and gray color scheme.

Quick Styles & Colors: With the chart selected, click on the Chart Design tab that appears in the top ribbon. You can use the Change Colors dropdown to pick a new color palette or select one of the pre-designed chart styles for a quick professional look.
Manual Color Changes: For full control, you can change the color of each data series individually. Click once on any segment in a series (e.g., any of the blue "Electronics" bars) to select all segments in that series. Then, right-click, select Format Data Series, and use the Paint Can icon in the side panel to choose a new fill color. This is perfect for aligning your charts with your company's brand colors.

A Quick Look at the 100% Stacked Bar Chart

In the same chart menu, you might have noticed an option for a "100% Stacked Bar" chart. What's the difference?

A standard stacked bar chart shows absolute values - the length of the bar corresponds to the sum of its parts. A 100% stacked bar chart, however, makes all the bars the same length (representing 100%) and shows the percentage contribution of each segment.

This chart type isn’t for comparing totals. Instead, it’s designed to compare the proportional mix between categories. Looking at the chart above, you can instantly see that Electronics made up a much larger percentage of sales in Q4 compared to Q1, even if you don't know the exact dollar-amount totals.

When to use it: Choose the 100% stacked bar chart when you care more about the relative contribution of each part than the grand total.

Best Practices and Common Mistakes to Avoid

To make your stacked bar charts as effective as possible, keep these tips in mind:

Don't Overcrowd the Bars: A stacked bar chart works best with just a few segments per bar (ideally 3-5). If you have ten different product lines, the chart will become a cluttered rainbow that’s impossible to read. In that case, consider grouping smaller categories into an "Other" category.
Sort Your Data: For a cleaner look, sort your main categories (the rows) based on their total value, either in ascending or descending order. This creates a more visually organized pattern that’s easier for the eye to follow. You can also strategically order the segments (the columns) so the most important or largest category is at the bottom.
Mind the Baseline: Remember that only the bottom segment of each bar shares a common baseline (starting at zero). This makes it easy to compare the bottom segment's value across all the bars. However, comparing the other segments is much harder because they start at different points. If comparing individual segments is your main goal, a clustered bar chart might be a better choice.
Use Color Thoughtfully: Don't rely on color alone to convey meaning. Use it to distinguish categories, and consider using your brand’s colors or a neutral palette with one highlighted color to draw attention to a key data point.

Final Thoughts

Ultimately, a stacked bar chart is a go-to tool in Excel for showing part-to-whole relationships across different categories. Once you have a handle on structuring your data and a few key customization options, you can move from basic data tables to insightful, professional-looking visualizations in minutes.

While mastering Excel is a valuable skill, the process of preparing data, selecting chart options, and applying custom formatting is often manual and repetitive, especially when your data lives in different apps like Shopify, Google Analytics, or Salesforce. At Graphed, we’ve built our platform to do that heavy lifting for you. Rather than building reports by hand, you can simply connect your data sources and ask questions in plain English, like "Show me a stacked bar chart of Shopify sales by product type for this quarter," and get a live, interactive dashboard created in seconds.