Charting positive, negative and net values

Question

Hi,

I'd like to bar chart a data range that is similar to the one listed below. While charting the details from 1 to 18 is simple, I am having difficulties in including the total net value of $60,141,291.31 in the same chart. Could someone please advise if charting the net value along with the positive and negative details is possible? If so, may I have step by step instruction please? FYI, I'm using Excel 2010 and the real data has multiple months.

Thank you in advance for your time and help,

Phongski

Categories	Feb-14
1	21,454,216.91
2	18,082.99
3	55,813.88
4	0
5	290,809.70
6	756,303.35
7	-5,690,413.58
8	13,079,319.23
9	18,041,171.37
10	416,457.92
11	1,275,247.69
12	25,897,985.37
13	-36,305.57
14	434,975.11
15	7,436,085.47
16	335,979.37
17	-26,330,527.92
18	2,706,090.02
Total Net Value	60,141,291.31

PS: After doing some research, I found this example http://www.mediafire.com/file/kdmmmnmjymu/03_10_10.xlsx, which would work perfectly for me. However, I have no idea how it was done and need guidance. Any help on this is much appreciated.

Answer 1

Ha! that's very clever. It is a stacked column chart with a fixed image as a background. If you move the chart to Sheet1, you will see that only the stacks appear. The chart sheet still has the background with the big orange blocks. 

But if that's the chart effect you want to achieve, it can be done, even without a fixed image. 

Here is a description of the steps, assuming the first column of data (Feb-2014) is in column B and the label (Feb-2014)  is in row 1.

First some data preparations. We'll use a chart with a primary and a secondary axis and we need to make sure that both Y axes use the same max and min value.

Below the Total Net Value add a row for "Padding above net value" and use this formula in B21

=SUMIF(B2:B19,">0")-B20

In row 22 add a calculation for "padding below zero" and use this formula in B22

=SUMIF(B2:B19,"<0")

Assuming there is data for March, April, May in columns C, D and E, copy the formulas across to column E. 

Select cells A20 to E22 (The labels and values for Total net value and the two "padding" rows, and their values in columns B to E) and create a stacked column chart.  Select the chart, click "Select Data" and then the "Edit" button in the "Horizontal Category Axis Labels" section. Select cells B1:E1 as the axis labels. 

You should now see a stacked column chart with three series. One series extends below zero, the other ones are above zero. Format the two "padding" series to have no fill and no border and decrease the gap width to 70%. 

Next we will add the Category 1 to 18 series to the chart and plot them on the secondary axis. We'll start with just one series, add it to the chart and send it to the secondary axis. Then we can add the other series all at once, because they will be plotted on the same axis as the previously added series. 

So, select the Category 1 row of data, i.e. cells A2:E2 and copy them.  Click the chart and use Paste Special. In that dialogue, tick the box for "Series Names in First Column" and hit OK.

Now that new series is stacked upon the invisible padding column. Select that Category 1 series and open its formatting dialogue. Tick the "Secondary Axis" option and increase the gap width to 120%. The chart looks a mess now, but bear with me. 

In the source data, select all data and labels for the categories 2 to 18, i.e. cells A3:E19 and copy the selection. Select the chart, use Paste Special, tick the box for "Series Names in First Column" and hit OK.

The rest is formatting.  Play around with settings for gap width, colours, axis labels. You can hide the secondary Y axis, set the X axis labels to "Low" so they appear below the chart, delete the "padding" labels from the legend.  

There are a few too many data points for my taste, and I'm not quite decided whether to keep the legend, which requires the eye to flick back and forth to interpret the colors, or whether to use data labels in the series, which can look ugly when labels overlap.  I'll leave that decision to you. 

Here is a link to the sample file that I used to take the screnshots: https://onedrive.live.com/redir?resid=FE479525195477F7!2099&authkey=!AGfIIeRapp8LpZg&ithint=file%2c.xlsx

Let me know if that works for you.

Answer 2

This will be complicated.

Both axes are already in use. You could create a separate chart with a transparent background and drag it on top of the column chart. 

Aligning the 0 point of the Y axis to the column chart's zero point will be a challenge, though. The zero position of the column chart will shift and you will need to adjust the overlaying line chart.

Answer 3

Works fine for me. Thank you very much for your time and help, Teylyn. I appreciate it.

Phongski