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

Hello,

what difficulties do you have exactly? If you can select the rows 1 to 18 and create a chart, just select the rows 1 to 19 and create the chart.  It will not look nice, because the total will be a very tall column in a column chart and it will be hard to see the smaller values in the chart. 

More important, I think, is what you want to achieve with the chart? What questions should the chart answer?  With the data changes on a daily (?) basis, maybe a Waterfall chart would be a better representation of the positive and negative values over time and how they affect the total. 

There are several tutorials on the web on how to create waterfall charts.

Chandoo: http://chandoo.org/wp/2009/08/10/excel-waterfall-charts/

Contextures: http://www.contextures.com/excelwaterfallchart.html

Peltiertech: http://peltiertech.com/WordPress/excel-waterfall-charts-bridge-charts/

Answer 3

Thanks for responding, Teylyn.

Anyhow, I'd like to show monthly changes in inventory turn and the make up of the inventory value in the last 12 months. The inventory values include different categories of raw materials, finished goods, and reserves, which are negatives. All make up total inventory balance that I also want to bar chart. Waterfall chart might not work for me as I need to show the monthly rolling balance.

Anyhow, after doing some research, I found this example http://www.mediafire.com/file/kdmmmnmjymu/03_10_10.xlsx, which would work well for my purposes. However, I have no clude how it was done. If you could shed some light on it, that'd be great.

Thanks,

Phongski

Answer 4

Wow, this is nice and works well for me. Thank you very much for your help, Teylyn. I appreciate it.

Answer 5

Hi Teylyn,

I'm thinking about charting an ITO line on the bar chart that you built for me. Is it possible?

In the data from your example, there will be inventory turnover (ITO) information of 3, 4, 3, 2 for Feb, Mar, Arp, and May respectively. With this additional line, people will see the relationship between on hand inventory balances and inventory turns.

I thought that the ITO line could be charted on a secondary axis but the bar charts already used both axes, so I'm stuck. If you could help me with this using your magic, that'd be great.

Thank you for your time,

Phongski