# Stacked Bar Chart with Negative and Positive Values

#### CHBC1

##### New Member
Hi Everyone,

I am working on a business case where I am trying to analyze the impacts of several different scenarios. I'm plotting the impacts in a bar chart, like the one in the screenshot below. Intuitively you can probably see, but the blue represents the base case, the orange represents the additional impact of scenario #1, and the grey dotted lines represent the even further additional impact of scenario #2.

The current case I'm working on now has some negative impacts. When I attempt to make the same plot, the negative impacts are plotted starting at 0 and they run down the negative X axis, instead of overlapping the blue bars. For example, if the blue bar = 10, and my orange impact bar is -2 (I would unfill the bar and make it dotted, so you can see both the base case and impact bars when they overlap), I would like to show a dotted bar from 10 to 8, signifying the -2 impact to the base case. Is there a way I can do this? I have tried a few different methods, but I've mostly been spinning my wheels. Hopefully this explanation makes sense!

Any help is appreciated!

Thanks!

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### pgc01

##### MrExcel MVP
Hi

You can split the impact into 2, the negative impacts and the positive impacts. Adjust the base for the negative impacts.

Then it's a simple stacked chart. Format the adjusted base with blue, the negative impacts with no fill and a dotted line and the positive impacts with orange.

This is an example, insert the chart based on the values in F:H

#### Jon Peltier

##### MrExcel MVP
It's not an easy trick to stack positive and negative items and clearly show what is going on.

Below is an illustration. The top data set has all positive numbers and a stacked chart. This clearly shows the base case and the impacts of two additional factors.

The bottom set has some negative values. Excel plots negative value bars below the axis: there is no reduction of the earlier totals, and it's impossible to see the ending values.

PGC's approach tries to show the negative values eating into the earlier subtotals, but this does not clearly show all the details, since the bars appear to overlap and you have to guess what's happening. Only the part of the base case that hasn't been bitten away is visible: does that stack then represent a small blue bar with a positive impact, or a larger blue bar with a negative impact?

What you can do is stagger the bars, so they don't overlap, creating mini-waterfalls for each category. This requires spreading out the data, inserting formulas for the blank columns on which the visible columns float (again in a stacked column chart).

Here is the setup for a chart with all positive values. The blue shaded cells in the Blank data column have a formula that computes the MIN of the Base or the Base+Impact1, while the yellow shaded cells have a formula that computes the MIN of the Base+Impact1 or the Base+Impact1+Impact2. In the top chart, the Blank series is shown with a light transparent yellow fill; in the bottom the Blank is hidden by formatting with no fill.

Dots are added to the first data column to make sure Excel properly plots the data. You see the dots in the blank category cells along the horizontal axis in the top chart; the dots have been deleted from the first data column, resulting in the clean axis of the bottom chart.

Here is the same setup for the data set with a few negatives thrown in. As long as the Base+Impact1 subtotal and the Base+Impact1+Impact2 total never go below zero, this approach is fine. If these may go negative, you'll need lots more formulas. I've added two columns, which contain the absolute values of the Impacts; these are plotted instead of the unchanged impact values to keep the bars floating in positive territory.

The top chart shows Blank as transparent yellow and still has the dots along the X axis. The bottom chart hides the dots and the yellow Blank series, and also uses a striped pattern to help differentiate the decreases from the increases.

#### CHBC1

##### New Member
Hmm...I can see that would work, but the problem is that I'm going to be plotting about 30 different items across the X axis, so I'm trying to make it one single stacked bar for each item. I am getting closer, but I can't quite figured out if it's possible

#### Jon Peltier

##### MrExcel MVP

If you have bars representing mixed sign numbers, a single stacked bar will not show the information clearly.

#### rsiddiqi

##### New Member
Hmm...I can see that would work, but the problem is that I'm going to be plotting about 30 different items across the X axis, so I'm trying to make it one single stacked bar for each item. I am getting closer, but I can't quite figured out if it's possible

Did you figure it out? I'm running into the same problem. I am comparing performance per unit for previous month with current month. I am showing a stacked increase on a bar chart where the performance gets better, but need a dotted empty box showing a decrease in performance. Outside of putting a dotted line manually on each decrease, I havent found a way yet.

Thanks.

#### Dunners

##### New Member
Hi. I've kind of done this. In your data have a column for each stackable category - in my case just 'Sales' (positive) and 'Returns' (negative). Then add the Total 'net' column. Create a normal stacked bar chart. You can then change the Series type of the Total field to Scatter plot (keep axis the same, of course). Now you have a marker you can format as you wish. One of the default markers is a horizontal line which, if you scale it up enough, is just the ticket!?

 Branch Values Return Sale Net A TY -164,801 2,250,554 2,085,753 A LY -79,742 1,689,549 1,609,807 C TY -1,033,809 5,362,752 4,328,943 C LY -457,061 4,121,619 3,664,558 D TY -281,184 693,503 412,319 D LY -129,610 475,656 346,046 E TY -223,181 1,625,156 1,401,975 E LY -103,129 1,176,527 1,073,398 N TY -97,112 1,126,452 1,029,339 N LY -43,802 624,352 580,550 P TY -98,259 309,879 211,620 P LY -45,783 285,000 239,217 U TY -526,252 1,750,561 1,224,309 U LY -176,110 1,529,992 1,353,882 W TY -5,957,761 26,499,940 20,542,179 W LY -3,082,236 22,106,116 19,023,880

Replies
3
Views
202
Replies
3
Views
220
Replies
1
Views
350
Replies
0
Views
786
Replies
0
Views
42

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,569
Messages
5,770,907
Members
425,651
Latest member
Mark Cashin

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back