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


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.

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

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 Make a Stacked Bar Chart 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

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

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 the 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


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

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: Excel Stacked Bar Chart with Subcategories


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: How to Create Stacked Bar Chart for Multiple Series in Excel


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.


Download Practice Workbook

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


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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mohammad Shah Miran
Mohammad Shah Miran

Mohammad Shah Miran has a professional background spanning over a year at Softeko. Initially starting as an Excel and VBA writer, he authored more than 50 articles for the ExcelDemy project. Currently, Miran is engaged in the LinuxSimply project as a Linux content developer, completed over 40 articles. His analytical approach extends across various domains, including Excel, VBA, Bash scripting, Linux, data analysis, and Python programming. In his leisure time, Miran enjoys watching movies and series or listening... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo