Graphed LogoGraphed
Login

How to Add Sum of Stacked Bar Chart in Excel

Cody Schneider•

Showing the total of each bar on a stacked chart in Excel is an effective way to communicate both the part-to-whole relationship and the overall sum in a single glance. Unfortunately, Excel doesn't offer a simple checkbox for this. This article will guide you through the best methods to add these crucial totals, transforming your standard charts into clear, insightful data stories.

Why Add a Total to a Stacked Bar Chart?

Picture a stacked bar chart showing monthly sales, with each stack representing a different product category. While it's great for seeing which products contribute most each month, your audience still has to mentally add up the segments to gauge total monthly performance. Placing the sum at the end of each bar eliminates this guesswork.

Here’s why it’s so powerful:

  • Immediate Context: Readers can instantly see the total value for each category (like a specific quarter or month) without needing to refer to the axis or perform mental math.

  • Clear Comparison: It becomes much easier to compare the overall performance between different bars. You can quickly see that Q3 total sales were $150,000, while Q4 sales were an even better $175,000.

  • Improved Readability: It streamlines your chart, making the key takeaway - the total value - crystal clear. A cleaner chart is always a more effective chart.

In short, you’re providing an extra layer of helpful information without cluttering the visual.

Getting Started: Prepare Your Data Table

Before you build your chart, a little data preparation will make the process much smoother. The best method for adding totals requires you to create a helper column that calculates the sum for each category row.

Let's say you have a table of quarterly sales by region:

We want to create a stacked bar chart where each bar represents a Quarter, and the stacks are the sales from each Region. To get the totals to show up, we need to add a "Total" column.

In cell F1, type "Total." In cell F2, enter the following formula:

Then, click the small square (the fill handle) at the bottom-right corner of cell F2 and drag it down to apply the formula to the rest of the rows. Your table should now look like this:

With this helper column in place, you're ready to create the chart.

Method 1: The Combo Chart Technique (The Best Approach)

This method is the most robust and professional way to add a sum to a stacked bar chart in Excel. It feels like a bit of a trick, but the result is a dynamic, clean-looking chart that automatically updates when your data changes. You'll convert your "Total" series into an invisible line chart that's only there to hold your data labels.

Follow these steps carefully.

Step 1: Insert a Basic Stacked Bar Chart

First, start by creating a standard stacked bar chart that includes your new Total column. Don’t worry, we’ll fix it in the next steps.

  1. Highlight your entire data range, including the row and column headers and your newly created "Total" column (in our example, that's range A1:F5).

  2. Navigate to the Insert tab on the Ribbon.

  3. In the Charts group, click the Insert Column or Bar Chart dropdown.

  4. Select the Stacked Bar chart option.

Excel will create a chart, but the "Total" will appear as another - very large - colored segment on each bar. This is what we're going to fix.

Step 2: Change to a Combination Chart

Now, we'll tell Excel to treat the Total series differently from the others.

  1. Right-click on your chart and select Change Chart Type... from the menu.

  2. In the "Change Chart Type" window, select the Combo category at the bottom of the list on the left.

  3. You'll now see a list of all your data series (North, South, East, West, Total). By default, they are all set to "Stacked Bar." This is where the magic happens.

Step 3: Configure Your Combo Chart Settings

In this menu, you'll specify how each data series should look.

  1. Keep the chart type for your original data series (North, South, East, West) as Stacked Bar.

  2. For your Total series, click the dropdown menu and change its chart type to Line with Markers.

  3. Crucially, for the Total series, check the box under the Secondary Axis column on the right. This temporarily moves the line series onto its own axis, making it easier to work with.

Here is how your configuration should look:

Click OK. Your chart should now show a stacked bar with a line chart overlaid, and a new vertical axis will appear on the right side.

Step 4: Add Data Labels to the Line

Now we’ll add the total values as labels to that line.

  1. Click once on the line in your chart to select the "Total" series. You’ll see small circles or ‘markers’ at each data point.

  2. Right-click on any of the markers on the line and choose Add Data Labels.

  3. The sum for each stack will now appear on the chart, though they might not be in the final position you want just yet.

  4. Right-click on the new data labels and select Format Data Labels. In the Format pane that opens, you can adjust the "Label Position" to Center, Right, or Left to place the value exactly where you want it. Setting it to Center or Right often works well.

Step 5: Make the Line and Markers Invisible

You have the labels, so you no longer need the line or markers that are holding them in place. Let’s make them disappear.

  1. Click on the line to select it again.

  2. In the Format Data Series pane on the right (if it's not open, right-click the line and choose "Format Data Series"), click on the Fill & Line (paint bucket) icon.

  3. Under the "Line" section, select the No line option.

  4. Still in this pane, switch to the Marker section. Under "Marker Options," choose None.

The line and its markers will vanish, but the labels remain exactly where they were, floating perfectly positioned at the end of each stacked bar.

Step 6: Final Cleanup

Your chart is almost ready. You just need to remove a few distracting elements that Excel added during the process.

  1. Delete the Secondary Axis: Since the line is gone, the secondary vertical axis on the right side of the chart is meaningless. Click on it to select it, and press the Delete key on your keyboard.

  2. Remove the Legend Entry: Your chart's legend still includes an entry for "Total." Click the legend to select it, then click again on just the word "Total." Press the Delete key.

  3. Add a Title: Give your chart a clear, descriptive title.

After these steps, you are left with a professional, clean, and dynamic stacked bar chart that clearly displays the sum of each stack.

Method 2: Manually Adding Text Boxes

If you need to create a chart quickly for a one-time presentation or report and don't expect the data to change, the text box method can be a decent shortcut. However, it's not dynamic, meaning if your underlying data changes, the text box values will not update automatically unless you link them directly to a cell.

Step-by-step instructions:

  1. Create a Standard Stacked Bar Chart: Select only your primary data series (A1:E5 in our example, excluding the "Total" column) and insert a regular stacked bar chart.

  2. Insert a Text Box: Go to the Insert tab, click Text, and select Text Box. Your cursor will turn into a small crosshair, draw a box on your chart near the end of the first bar.

  3. Link the Text Box to Your Total Cell: This is a crucial step to avoid typos and make positioning updates easier.

    • Click to select the text box you just created. Do not click inside it to type.

    • Now, click directly in the Formula Bar at the top of the Excel window.

    • Type an equals sign (=).

    • With your mouse, click on the cell that contains the total for the first bar (in our example, F2). Press Enter. The value from that cell now appears in the box.

  4. Format and Place the Box: Drag the text box so it sits neatly at the end of the bar. Right-click the box, choose Format Shape, and set both the Fill and the Line to "No Fill" and "No Line" to make the border invisible.

  5. Repeat for All Bars: Repeat steps 2-4 for each of the remaining bars on your chart, linking each new text box to the corresponding total cells (F3, F4, etc.).

Pros & Cons of This Method:

  • Pro: It is conceptually simple and requires no chart-type manipulation.

  • Con: It is highly manual. The text boxes do not anchor to the bars, so if your chart resizes or data changes the bar lengths, you must manually reposition every single box.

For these reasons, the Combo Chart method is almost always the better choice.

Final Thoughts

Adding totals directly onto your stacked bar charts transforms them from good to great, giving your audience the complete story at a glance. While Excel requires a few smart workarounds, the combination chart method is a reliable and dynamic solution that leaves you with a professional-looking visual that updates right along with your data.

Creating custom charts like this in Excel is powerful, but it often marks the start of a long, manual reporting process. We feel this pain, especially when you need to combine data from tools like Google Analytics, Shopify, and various ad platforms. We built Graphed to slash that time. Instead of wrangling with chart configurations, you can simply request a "quarterly-by-region stacked chart showing sums at the end of each bar." Graphed handles the rest - connecting to your live data and automatically building the dashboard, giving you insights instantly.