How to Create Stacked Bar Chart with Negative Values in Excel?

Sharing is caring!

Visualizing positive and negative data together in Excel can be tricky, especially when you need to compare categories side by side. One of the best ways to do this is by using a stacked bar chart with negative values. This chart type is especially helpful for financial analysis, sales comparison, or profit and loss statements where both gains and losses need to be clearly displayed.

In this guide, you’ll learn step-by-step how to create a stacked bar chart with negative values in Excel, along with formatting tips to make it clear and professional.

Why Use a Stacked Bar Chart with Negative Values

A stacked bar chart helps display multiple data series for each category, showing the composition of each total. When negative values are included, it allows you to compare contrasting metrics, such as:

• Revenue vs. Expense
• Profit vs. Loss
• Positive vs. Negative growth

Using this chart type helps to visualize both sides of performance (above and below zero), making data interpretation much easier.

Step 1: Prepare Your Data

Before creating the chart, your Excel dataset needs to be structured correctly. Each column should represent a data series, and each row should represent a category or time period.

Here’s an example of how your data should look:

In this table:

• Product A shows positive values (e.g., profit or sales).
• Product B shows negative values (e.g., losses or expenses).

Make sure there are no blank cells and that the data is properly labeled, as Excel uses the headers to generate chart legends.

Step 2: Insert a Stacked Bar Chart

Once your data is ready, follow these steps:

1. Select the data range, including the headers.
2. Go to the Insert tab on the Excel Ribbon.
3. In the Charts group, click on Insert Column or Bar Chart.
4. Choose Stacked Bar Chart (not 100% stacked).

Excel will generate a basic stacked bar chart with positive and negative values, but you’ll need to format it to make the visualization clearer.

Step 3: Adjust the Axis for Negative Values

When your chart contains both positive and negative values, Excel automatically places the horizontal axis (X-axis) at the zero point. However, sometimes the bars may not align clearly, or the zero line may be hard to see.

To adjust it:

1. Right-click the horizontal axis and select Format Axis.
2. Under Axis Options, make sure the Horizontal Axis Crosses at Automatic or Zero is selected.
3. Check the Reverse Categories box if your categories appear upside-down.

This ensures that negative bars extend to the left and positive bars to the right of the zero axis.

Step 4: Format Positive and Negative Bars

To make the chart visually clear, format the bars so that positive and negative values have different colors.

1. Click once on a positive series bar (e.g., Product A).
2. Right-click and choose Format Data Series.
3. Under Fill & Line, choose a solid color like blue for positive values.
4. Repeat for the negative series (e.g., Product B), using a contrasting color like red.

This color coding helps readers instantly identify gains and losses.

Tip: You can also use gradient fills or pattern fills to further distinguish between data sets.

Step 5: Add Data Labels for Clarity

Adding data labels helps users quickly understand the magnitude of each bar, especially when negative values are involved.

To add labels:

1. Click on any bar in the chart.
2. Right-click and select Add Data Labels.
3. Format the labels by clicking Format Data Labels, then check Value and Inside Base (or Outside End) for placement.

You can also change the label color to white or black depending on your bar colors.

Step 6: Improve Chart Readability

To make your stacked bar chart look professional and easy to read:

• Remove unnecessary chart elements like the gridlines and background.
• Add axis titles to clarify what each bar represents.
• Add a descriptive chart title, such as “Profit and Loss by Product – Q1 2025.”
• Adjust bar spacing (gap width) by right-clicking on a bar → Format Data Series → Gap Width (set between 50%–80% for balanced spacing).
• Use consistent fonts and alignment for all chart elements.

These small formatting touches can significantly improve your chart’s readability and presentation.

Step 7: Handle Multiple Data Series (Optional)

If your chart has more than two data series, Excel stacks them automatically. For example, if you want to show Profit, Expense, and Tax values together, your dataset might look like this:

To visualize this:

• Follow the same steps as above.
• Assign distinct colors to each series.
• Make sure the legend clearly differentiates between positive and negative components.

This approach is useful for comparing multiple financial metrics across categories or time periods.

Step 8: Add Zero Line for Better Visual Balance

When negative values are present, a zero line makes the chart easier to interpret.

To add one:

1. Click anywhere inside the chart.
2. Go to Chart Elements (the + icon) → Gridlines → Select Primary Major Horizontal.
3. This creates a line at zero, dividing positive and negative values visually.

You can format the line color and thickness by right-clicking and selecting Format Gridlines.

Step 9: Customize Axis Labels and Numbers

You can make your chart more professional by formatting the axis values:

• Right-click the axis → Format Axis.
• Under Number, choose the appropriate format, such as Currency, Percentage, or Number with thousand separators.
• Adjust the minimum and maximum bounds to ensure all data is visible.

For instance, if your data ranges from -4000 to +6000, you might set:

• Minimum bound = -5000
• Maximum bound = 7000

This ensures enough space around the bars for visual clarity.

Step 10: Use Conditional Formatting in the Data Table (Optional)

If you want your Excel worksheet to match the color scheme of your chart:

1. Select the data table.
2. Go to Home → Conditional Formatting → New Rule → Format Only Cells That Contain.
3. Set one rule for greater than 0 (apply a green or blue color) and another for less than 0 (apply a red color).

This helps maintain consistency between the chart and data table, improving presentation quality.

Step 11: Make It Interactive with Dynamic Ranges (Advanced)

If you frequently update your data, you can make your chart dynamic using Excel Tables or Named Ranges.

1. Select your dataset and press Ctrl + T to create an Excel Table.
2. Excel automatically expands the chart as new data is added.

Alternatively, use the OFFSET function to create dynamic named ranges, ensuring your stacked bar chart updates automatically whenever new data is entered.

Common Use Cases for Stacked Bar Charts with Negative Values

This chart type is highly useful wherever comparative analysis between gains and losses is essential.

Final Formatting Tips

• Use contrasting colors for positive and negative values.
• Keep your axis titles short and clear.
• Include legends for better interpretation.
• Avoid excessive 3D effects — they can distort perception.
• Save your custom formatting as a chart template for future use.

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.