How to Create Stacked Area Chart with Negative Values in Excel

If you are looking for ways to create a stacked area chart with negative values in Excel, then this article will serve this purpose. Generally, it is difficult to show the negative values in a stacked area chart, but after following this article you will be able to do it easily for 3 different cases.


Download Workbook


3 Examples to Create Stacked Area Chart with Negative Values in Excel

Here, we have the three following datasets containing the record of profits for different years.
Using these datasets we will illustrate the following 3 examples.
The first dataset with a single column of profit values for Apple will be used in the first example,

excel stacked area chart negative values

the second dataset with two columns of profit values for Kiwi and Apple will be used in the second example,

excel stacked area chart negative values

and finally using the following dataset we will demonstrate the way to use three columns of profit values in a stacked area graph.

excel stacked area chart negative values


Example-1: Creating a Stacked Area Chart for a Single Column Containing Positive and Negative Values

In this example, we have the positive and negative profit values (the values in red color) mixed together in the Apple column. Using this dataset we can easily plot a stacked area chart showing the negative values.

excel stacked area chart negative values


Step-01: Inserting Stacked Area Chart

  • Select the Apple column, then go to the Insert tab >> Charts group >> Insert Line or Area Chart group >> 2-D Stacked Area

single column

After that, you will get the following chart where you can see the negative values easily in the chart but on the horizontal axis, we have to put the years.

single column

  • Right-Click on the chart area and then select the option Select Data.

single column

After that, you will have the Select Data Source wizard.

  • Click on the Edit option under the Horizontal (Category) Axis Labels

excel stacked area chart negative values

Then, the Axis Labels wizard will open up.

  • Select the Year column as the Axis label range and then press OK.

single column

Afterward, you will be taken to the Select Data Source dialog box again.

  • Press OK

single column

In this way, the years will appear in the chart.

excel stacked area chart negative values


Step-02: Formatting Chart

Now, we will change the color of the positive areas into green and negative areas into the red to identify the differences in their values.

  • Right-click on the series and then select the Format Data Series

excel stacked area chart negative values

Afterward, the Format Data Series wizard will pop up on the right side of your worksheet.

  • Select the Gradient fill option under the Fill

single column

Then, you will see the Gradient stops option where you can control the color of your chart.

single column

  • Select the first stop on the left side and then choose green as the fill color if you want green for the positive areas.

excel stacked area chart negative values

  • Later, choose red as the fill color for the last three stop points for the negative areas.

single column

After applying the colors, you will visualize the following changes in your chart.

excel stacked area chart negative values

  • Now, move the green point from left to right and the red point from right to left and balance the points according to the necessary to bring the following colors to your chart.

excel stacked area chart negative values

Finally, you will have the following chart representing the positive and negative profits of apples for different years.

single column


Example-2: Creating a Stacked Area Chart for Two Columns Containing Positive and Negative Values in Excel

Here, we have negative profit values for Kiwi and positive profits for Apple for different years. To apply this example the negative series should be in the first place and that’s why the Kiwi column is prior to the Apple column.

excel stacked area chart negative values

Steps:

  • Select the Kiwi and Apple columns, then go to the Insert tab >> Charts group >> Insert Line or Area Chart group >> 2-D Stacked Area

two columns

After that, the following chart will appear where we will give input of the year values on the horizontal axis.

two columns

  • Right-Click on the chart area and then select the option Select Data.

excel stacked area chart negative values

After that, you will have the Select Data Source wizard.

  • Click on the Edit option under the Horizontal (Category) Axis Labels

two columns

Then, the Axis Labels wizard will open up.

  • Select the Year column as the Axis label range and then press OK.

two columns

Afterward, you will be taken to the Select Data Source dialog box again.

  • Press OK

excel stacked area chart negative values

In this way, the years will appear in the chart.

two columns

Now, to modify the chart Right-click on the Y-axis.

two columns

  • Select the Format Axis

excel stacked area chart negative values

Afterward, you will have the Format Axis dialog box on the right side of your worksheet.

two columns

  • Go to the Axis Options tab and give -300.0 (the minimum limit for negative profits of the Kiwi column) as the Minimum Bounds value and Axis value where the Horizontal axis crosses.

two columns

Then, you will get the final result of the area chart where the negative values of the Kiwi column are shown on the negative Y-axis and the positive values of the Apple column are shown also with respect to this negative axis. As the baseline has been shifted to -300 here.

excel stacked area chart negative values


Example-3: Creating a Stacked Area Chart for Three Columns Containing Positive and Negative Values

In this section, we will deal with two columns with positive profits and one column with negative profits and by following this example you will be able to plot the values in a stacked chart showing the negative values.
For this purpose, we will need two extra columns Total and Apple+Kiwi.

excel stacked area chart negative values


Step-01: Using Formulas to Calculate Values

  • In cell F4 of the Total column type the following formula
=SUM(C4:E4)

Here, the SUM function will add up the profit values of Apple, Orange, and Kiwi.

  • Drag down the Fill Handle tool to copy this formula for the rest of the cells.

three columns

In this way, you will get the total profits of all of the three products in the Total column.

three columns

  • Write down the following simple formula in cell G4 of the Apple+Kiwi
=C4+E4

Here, the profits of the Apple and Kiwi will be added.

  • Drag down the Fill Handle tool to copy this formula for the rest of the cells.

excel stacked area chart negative values


Step-02: Inserting Stacked Area Chart

After, calculating all of the values we will use the following indicated 3 columns to plot our stacked area chart.

three columns

  • Select the Kiwi, Total and Apple+Kiwi columns, then go to the Insert tab >> Charts group >> Insert Line or Area Chart group >> 2-D Stacked Area

excel stacked area chart negative values

After that, the following chart will appear where we will give input of the year values on the horizontal axis.

three columns

  • Right-Click on the chart area and then select the option Select Data.

three columns

Afterward, you will have the Select Data Source wizard.

  • Click on the Edit option under the Horizontal (Category) Axis Labels

three columns

Then, the Axis Labels wizard will open up.

  • Select the Year column as the Axis label range and then press OK.

excel stacked area chart negative values

Afterward, you will be taken to the Select Data Source dialog box again.

  • Press OK

three columns

In this way, the years will appear in the chart.

excel stacked area chart negative values


Practice Section

For doing practice by yourself we have provided a Practice section like below in each sheet on the right side. Please do it by yourself.

practice


Conclusion

In this article, we tried to create a stacked area chart with negative values in Excel. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo