How to Make a Stacked Column Chart in Excel

A stacked column chart is a fantastic way to show not only how a total value changes over time but also how its individual components contribute to that total. Instead of wrestling with a dozen separate charts, this one visual consolidates everything, making it easier to spot trends and compare categories at a glance. This guide will walk you through exactly how to prepare your data, create a stacked column chart in Excel, and customize it to be clear and professional.

What is a Stacked Column Chart? (And When to Use It)

Imagine a standard column chart where each column represents a total - like total monthly sales. A stacked column chart takes this a step further by dividing each of those columns into segments. Each segment represents a different category that makes up the total. If a column shows total sales for January, the segments within that column might show sales from different product lines (e.g., phones, laptops, accessories).

Each segment is "stacked" on top of the one below it, so the total height of the column is the sum of all its parts.

Use a stacked column chart when you want to:

• Compare parts of a whole across different periods or categories.
• Show how the composition of a total changes over time.
• Analyze the cumulative contribution of multiple data series.

This chart type is a favorite for visualizing things like:

• Quarterly revenue broken down by region.
• Monthly website traffic split by source (Organic, Social, Direct, Paid).
• Project budgets allocated across different departments.

The key here is that you're interested in both the overall total and the breakdown of that total. If you only cared about the totals, a regular column chart would work. For proportions, you might choose a pie chart.

How to Organize Your Data in Excel

Before you click a single button to create a chart, the most important step is setting up your data correctly. A clear, well-structured table is the foundation for a great chart. Messy data will almost always lead to a confusing chart, or one that requires a lot of manual reconfiguring.

Follow this format for best results:

1. Your first column should contain the categories you want to compare along the horizontal axis (X-axis). These are typically time periods (like months, quarters, or years) or distinct groups (like product lines or sales regions). Let's call this your Category Column.
2. Each subsequent column should contain the numerical data for the individual series you want to stack. These are the different smaller values that will become segments inside each larger column.
3. Your column headers should precisely describe what each series represents (e.g., "Organic Traffic," "Paid Ads," "Email").

Let’s look at an example. Imagine we want to visualize a company’s quarterly revenue, broken down by three product categories: Software, Hardware, and Services.

Here’s how you would structure the data in your worksheet:

A1: Quarter | B1: Software Sales | C1: Hardware Sales | D1: Services Sales A2: Q1 2024 | B2: $50,000 | C2: $30,000 | D2: $15,000 A3: Q2 2024 | B3: $55,000 | C3: $25,000 | D3: $18,000 A4: Q3 2024 | B4: $60,000 | C4: $35,000 | D4: $20,000 A5: Q4 2024 | B5: $70,000 | C5: $40,000 | D5: $22,000

In this layout, "Quarter" is our category on the X-axis. "Software Sales," "Hardware Sales," and "Services Sales" are the individual series that will be stacked on top of each other to show the total quarterly revenue.

Creating a Stacked Column Chart: Step-by-Step

Once your data is neatly arranged, creating the chart is a simple process. Let's use the quarterly sales data from our example.

Step 1: Select Your Data

Click and drag your mouse to highlight the entire data range you want to include in the chart. In our example, that would be cells A1 through D5. Make sure you include the headers for both the rows and columns - Excel will use these to automatically generate the axis labels and legend.

Pro tip: Just click any single cell inside your data table and press Ctrl + A (or Cmd + A on a Mac) to quickly select the entire contiguous range.

Step 2: Insert the Chart

With your data selected, navigate to the Insert tab on Excel's top ribbon.

In the "Charts" group, find the icon that looks like a column chart (it will show several vertical bars). Click this icon to open the column chart dropdown menu.

Step 3: Choose the "Stacked Column" Chart Type

In the dropdown menu, you'll see several options under the "2-D Column" heading. Hover over the second option. Excel will label it as "Stacked Column." The icon itself will look like columns with different colored segments stacked up. Click on it.

That's it! Excel will instantly generate a basic stacked column chart and place it on your worksheet. It will show four columns (one for each quarter), and each column will be segmented by the sales amounts for Software, Hardware, and Services.

Customizing Your Chart for Clarity and Impact

The default chart is a great starting point, but taking a few minutes to customize it can turn it from a decent visual into a powerful reporting tool. When you click on your new chart, two new tabs will appear on the ribbon: Chart Design and Format.

Adding a Clear Chart Title

A good chart needs no explanation. A busy manager (or client) wants the key takeaway at a glance, so ditch the generic "Chart Title" default.

Double-click the default "Chart Title" text box at the top of your chart. Delete the placeholder text and type in a descriptive title that tells the viewer exactly what they're looking at. Something like "Quarterly Revenue by Product Category - 2024" is much more informative.

Adding and Formatting Data Labels

While the viewer can estimate the value of each segment using the vertical axis, adding data labels makes it crystal clear. This removes any guesswork.

1. Click once on the chart to select it.
2. Click the green plus sign ("+") icon that appears on the top-right corner of the chart. This opens the "Chart Elements" menu.
3. Check the box next to Data Labels. Labels will immediately appear on each segment of your chart.
4. For better readability, you might want to change the font color of the labels. Right-click on one of the data labels to select all labels for that series, then use the home ribbon (or the right-click menu and choose font) to change the color — white often works best against darker backgrounds. Repeat for the other data stacks as well.

Adjusting The Legend

Excel already creates a comprehensive list to distinguish between various hues. However, you can manipulate what is shown on the chart by clicking anywhere on the legend and making changes. You can also right-click it for more options or place it at the top, left, or any position convenient for you.

Changing Colors and Chart Style

The default Excel colors can be a bit bland. You can easily change them to match your brand or to make certain data series stand out.

• With the chart selected, go to the Chart Design tab.
• Click the Change Colors button to choose from a variety of tasteful, pre-built color palettes.
• If some sections are small, consider using more contrasting colors to highlight each element clearly, improving visualization.
• In the "Chart Styles" gallery on the same tab, hover over different design presets which change not only colors but also background shades, text styles, and other elements.

Axis Adjustments

Excel sets up the axes quite well, but sometimes you may want to refine them.

• Vertical (Value) Axis: To alter how values are incremented or for better readability, right-click the axis and select "Format Axis." Here, you can adjust intervals, apply Excel's currency format for a better look, and enhance understanding among users.
• Horizontal (Category) Axis: Right-click any category-axis choice, select "Format Axis" to modify its properties. You can also adjust data labels by position and hide lines using gridlines options.

Bonus: Meet the 100% Stacked Column Chart

If you're more interested in the share of individual components rather than the total, consider using the “100% Stacked Column Chart.”

While a regular stacked chart shows different parts of a sum, a 100% stacked chart visualizes how each segment contributes to the entire total by using percent values. For example, you can quickly see proportional changes in each color section across all categories, even though the column height remains at 100%.

To create this chart in Excel, follow the steps to insert a chart as previously discussed. Instead of the typical 2-D Column chart options, select the 100% Stacked Column style.

Is This Process Painfully Slow?

Creating graphs in Excel can be challenging, especially for those not familiar with the platform. It requires time and patience to master. However, with practice, you can make effective use of Excel's powerful tools to create clear and informative charts.

Final Thoughts

The stacked column chart is an easy-to-use tool that makes showing data breakdowns over different periods straightforward and transparent. By organizing your data properly, you will have a clear interpretation of key points, making it indispensable for reporting.

Manual processes like exporting data, setting up tables, and customizing charts in Excel can consume significant time. To streamline this, consider solutions like Graphed. It connects data across platforms and can automate visualization tasks, saving time and effort. With Graphed, you can receive real-time and interactive visuals without complex manual processes inside Excel charts.