How to Create Stacked Bar Chart with Negative Values in Excel

Creating a Stacked Bar Chart is an easy way to depict your data and compare them concisely. This article can help you if you’re trying to figure out how to make an Excel Stacked Bar Chart with negative values. Following our objective, we will describe three different ways to accomplish the task.

Excel Stacked Bar Chart with Negative Values: 3 Methods

Let’s assume we have a dataset, namely “Forbes Listed Top Company in 21st Century”. You can use any dataset suitable for you.

Here, we have used the Microsoft Excel 365 version; you may use any other version according to your convenience.

1. Create a 2-D Stacked Bar Chart with Negative Values

The first method we are going to use to create a Stacked Bar Chart is the 2-D Stacked Bar feature. Follow the instructions that we are going to articulate below.

Step 1: Insert Stacked Bar Chart

Select the range of the dataset from C5:F10, then go to the Insert tab >> Charts group >> Insert Column or Bar Chart group >> 2-D Stacked Bar

After that, you will get the following chart where you can see the positive numbers are stacked on the right side of $0.00 and the negative values are left side.

Step 2: Format the Stacked Bar Chart

Click on the Legend of your Bar Chart, then move on to the Chart Filters option.

After that, click on the Select Data option.

Thus, you will have a Data Source Wizard.
Click on Series1 and press the Edit button to change your Legend name.

Subsequently, another dialog box will appear.
Select D4 as your Series name and press OK afterward.

Following that, your first Legend will be changed to Revenue (Billions).

Do the same process to change the other two. Thus, you will have three Legend names as follows.
Press the OK button when you are done.

After changing your Chart Title named “Stacked Bar Chart”, you will get a chart as below. However, you can give title to your Chart according to your preference.

2. Construct a 100% Stacked Bar Chart with Negative Values

Another way to do the same task is by using the 100% Stacked Chart feature.

Step 1: Generate a 100% Stacked Bar Chart

Like as before, select the range of the dataset from C5:F10, then go to the Insert tab >> Charts group >> Insert Column or Bar Chart group >> 2-D 100% Stacked Bar.

Thus, you will have a raw chart as given below. Notice that the values in the X-axis are given in percentage scale.

Step 2: Format the 100% Stacked Bar Chart

Now do the same process as we did in the first method to format the chart. Both processes are the same, so we will now ignore that part to avoid redundancy.

The final output you will get is as follows.

3. Build a 3-D Stacked Bar Chart with Negative Values

Now, if you want to give your Stacked Bar Chart a 3D shape, you can go for this method.

Step 1: Generate 3D Stacked Bar Chart

Select the range of the dataset from C5:F10, then go to the Insert tab >> Charts group >> Insert Column or Bar Chart group >> 3-D Stacked Bar

Thus you will have a 3D Stacked Bar Chart as follows.

Step 2: Format the 100% Stacked Bar Chart

Following the formatting steps as we did in the first method, you will get an output chart as given below.

Now you can also use categories in reverse order to bring your X-axis data to the top. To do so, follow the instructions we are going to provide below.

Click on the Y-axis data of your chart, then Right-click on your mouse.
Select the Format Axis option afterward.

Now on the right side of your Excel interface, a wizard will appear.
Select Categories in reverse order as your Axis position.

Now see the output as given below.

How to Create Excel Chart Showing Negative Values as Positive

We may display negative numbers as positive using a straightforward custom format code. Follow the method as demonstrated below.

Select your range of data, D5:F10 for instance.

Do Right-click on your mouse and click the Format Cells option.

Subsequently, a Format Cells window will appear.

Select Custom from the Number. Then copy and paste the following format code to the Type: box.

#,###;(#,###)

Next, click OK.

Now you will get a list of data where negative values are enclosed by the first bracket.

To create your chart, select the range of the dataset from D5:F10, then go to the Insert tab >> Charts group >> Insert Column or Bar Chart group >> Stacked Column.

After doing the necessary formatting ritual, you will get an output as given below.

Things to Remember

When your dataset is considerably large, try to avoid the Stacked Bar Chart, as it can create confusion among the audience.
If you want to switch your row and column, make sure they all are selected before making any changes.

Practice Section

We have provided a Practice section on the right side of a sheet so you can practice yourself. Please make sure to do it yourself.

Download Practice Workbook

You can download and practice the dataset that we have used to prepare this article.

Stacked Bar Chart with Negative Values.xlsx

Conclusion

In this article, we have discussed how to make an Excel Stacked Bar Chart with negative values. As you have already understood, there are plenty of ways to do this task. So before going through a specific method, ensure the method you choose aligns with your work. Further, If you have any queries, feel free to comment below and we will get back to you soon.

<< Go Back to Stacked Bar Chart in Excel | Excel Bar Chart | Excel Charts | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!