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.
How to Create Stacked Area Chart with Negative Values in Excel (3 Examples)
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,
the second dataset with two columns of profit values for Kiwi and Apple will be used in the second example,
and finally using the following dataset we will demonstrate the way to use three columns of profit values in a stacked area graph.
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.
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
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.
- Right-click on the chart area and then select the option Select Data.
After that, you will have the Select Data Source wizard.
- Click on the Edit option under the Horizontal (Category) Axis Labels
Then, the Axis Labels wizard will open up.
- Select the Year column as the Axis label range and then press OK.
Afterward, you will be taken to the Select Data Source dialog box again.
- Press OK
In this way, the years will appear in the chart.
Read More: Stacked Area Chart with Line in Excel
Step-02: Formatting Chart
Now, we will change the color of the positive areas into green and the negative areas into red to identify the differences in their values.
- Right-click on the series and then select the Format Data Series
Afterward, the Format Data Series wizard will pop up on the right side of your worksheet.
- Select the Gradient fill option under the Fill
Then, you will see the Gradient stops option where you can control the color of your chart.
- Select the first stop on the left side and then choose green as the fill color if you want green for the positive areas.
- Later, choose red as the fill color for the last three stop points for the negative areas.
After applying the colors, you will visualize the following changes in your chart.
- 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.
Finally, you will have the following chart representing the positive and negative profits of apples for different years.
Read More: How to Create an Area Chart in Excel
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.
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
After that, the following chart will appear where we will give input of the year values on the horizontal axis.
- Right-Click on the chart area and then select the option Select Data.
After that, you will have the Select Data Source wizard.
- Click on the Edit option under the Horizontal (Category) Axis Labels
Then, the Axis Labels wizard will open up.
- Select the Year column as the Axis label range and then press OK.
Afterward, you will be taken to the Select Data Source dialog box again.
- Press OK
In this way, the years will appear in the chart.
Now, to modify the chart Right-click on the Y-axis.
- Select the Format Axis
Afterward, you will have the Format Axis dialog box on the right side of your worksheet.
- 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.
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.
Read More: Excel Area Chart Data Label Position
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.
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.
In this way, you will get the total profits of all of the three products in the Total column.
- Write down the following simple formula in cell G4 of the Apple+Kiwi
=C4+E4Here, 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.
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.
- 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
After that, the following chart will appear where we will give input of the year values on the horizontal axis.
- Right-click on the chart area and then select the option Select Data.
Afterward, you will have the Select Data Source wizard.
- Click on the Edit option under the Horizontal (Category) Axis Labels
Then, the Axis Labels wizard will open up.
- Select the Year column as the Axis label range and then press OK.
Afterward, you will be taken to the Select Data Source dialog box again.
- Press OK
In this way, the years will appear in the chart.
Read More: How to Shade an Area of a Graph in Excel
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.
Download Practice Workbook
You can download and practice this Workbook.
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.
