How to Create Stacked Bar Chart with Negative Values in Excel

Get FREE Advanced Excel Exercises with Solutions!

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


Download Practice Workbook

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


3 Methods to Create Stacked Bar Chart with Negative Values in Excel

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

How to Create Stacked Bar Chart with Negative Values in Excel

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 Stacked Bar Chart is 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

Select the 2-D stacked bar to Create Stacked Bar Chart with Negative Values in Excel

  • 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.

Raw 2-D stacked bar

Step 2: Format the Stacked Bar Chart

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

Formatting the Stacked Bar Chart

  • After that, click on the Select Data option.

Select Data option for Formatting the Stacked Bar Chart

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

Change the legend for Formatting the Stacked Bar Chart

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

write the series name for Formatting the Stacked Bar Chart

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

write the series name for Formatting the Stacked Bar Chart

  • 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.

write the series name for Formatting the Stacked Bar Chart

  • 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-D stacked bar chart in excel

Read More: How to Create Stacked Bar Chart for Multiple Series in Excel


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

  • As like 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.

using 100% stacked bar chart feature to Create Stacked Bar Chart with Negative Values in Excel

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

100% stacked bar chart with Negative Values in Excel

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.

100% Stacked Bar Chart with Negative Values in Excel

Read More: How to Make a 100 Percent Stacked Bar Chart in Excel (with Easy Steps)


Similar Readings


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

using 3-d stacked bar chart feature to Create Stacked Bar Chart with Negative Values in Excel

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

3-d Stacked Bar Chart with Negative Values in Excel

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.

excel stacked bar chart negative values

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

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

using format axis feature

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

select categories in reverse order to create Stacked Bar Chart with Negative Values in Excel

  • Now see the output as given below.

3d Stacked Bar Chart with Negative Values in Excel

Read More: How to Create Stacked and Clustered Bar Chart in Excel (with Easy Steps)


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.

select the range of data

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

Select the format cells

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.

Enter the custom code

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

List of negative data showing as a positive value.

  • 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.

Create Excel Chart Showing Negative Values as Positive

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

Excel Chart Showing Negative Values as Positive

Read More: Reverse Legend Order of Stacked Bar Chart in Excel (With Quick Steps)


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.

Do it yourself.


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.


Related Articles

Mohammad Shah Miran

Mohammad Shah Miran

Miran is a highly motivated individual with a strong educational background in engineering. He is interested in technology and passionate about creating engaging and informative content. After graduation, Miran decided to pursue a career in content development and has been working in the field for some time. He is eager to continue learning and growing as a professional.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo