stacked charts with positive and negative data

[lalbatros]

Context

I often had to present stacked area or stacked columns charts
based on data containing positive as well as negative contributions.

Such graphics are of course a difficulty and Excel offers no standard solution to chart such kind of data.

One example of such data is monthly stock variations:

- you can have several stock inputs
- you have several stock outputs
- and you have an initial stock each month

It is easy to chart the stacked input data or the stacked output data.
However, the most interresting thing to present is the stock evolution together with its positive and negative contributions.

Another example is monthly cash-flows with incomes and spendings.

In the past I have imagined different ways to chart such data.
However, it had always been specific solutions.
The charted data had to be prepared and tranformed to allow a final display showing the + and - contributions and their total.
Typically I calculated series that stacked separately the + and - terms, as well as the total.
Then I managed to chart side-by-side columns of these stacked + and - data and the total.
For one month, this was feasible, but I even don't see how to do that for several months.

This is not an easily re-usable solution.

My question

Do you know of different ways to chart stacked data containing both positive and negative items?
Eventually, is there a way to do that simply in Excel?
Or would there be some tool for that?
Or do you know some wonderful macro?

 

[eliW]

It would be of great help if you give few rows of your data and explain on real numbers how do you like your chart to be!

 

[tusharm]

Waterfall chart
http://www.tushar-mehta.com/excel/charts/waterfall/index.htm

Also, check Jon Peltier's http://peltiertech.com/Excel/Charts/Waterfall.html

Context

I often had to present stacked area or stacked columns charts
based on data containing positive as well as negative contributions.

Such graphics are of course a difficulty and Excel offers no standard solution to chart such kind of data.

One example of such data is monthly stock variations:

- you can have several stock inputs
- you have several stock outputs
- and you have an initial stock each month

It is easy to chart the stacked input data or the stacked output data.
However, the most interresting thing to present is the stock evolution together with its positive and negative contributions.

Another example is monthly cash-flows with incomes and spendings.

In the past I have imagined different ways to chart such data.
However, it had always been specific solutions.
The charted data had to be prepared and tranformed to allow a final display showing the + and - contributions and their total.
Typically I calculated series that stacked separately the + and - terms, as well as the total.
Then I managed to chart side-by-side columns of these stacked + and - data and the total.
For one month, this was feasible, but I even don't see how to do that for several months.

This is not an easily re-usable solution.

My question

Do you know of different ways to chart stacked data containing both positive and negative items?
Eventually, is there a way to do that simply in Excel?
Or would there be some tool for that?
Or do you know some wonderful macro?

 

[lalbatros]

eliW,

Here is an example based on one set of data (total for a project):

Indeed it looks like the "waterfall" chart.
My problem is that:

1) I may have such data for each month over a long period
2) making this graphic is not so straighforward (I split positive and negative data in two columns ...)

My question is about the possibilities to create such graphics routinely in a more easy way and for time-dependent data eventually.

Thanks for your suggestions.

 

[eliW]

Hi
I made this with background and fill technique described by Peltier in his site :

 

[lalbatros]

Interresting, Eli.
I guess you can do that without splitting the data in different columns.
Therefore, it may work for time-dependent data.
In addition, you don't need to prepare any formulas on the datasheet.

 

[eliW]

That's right.

Once you made it you can change numbers and get the result instantly.

I didn't try it for more than one point (time) -this may be more complicated. Consalt the 2 guru's of charts: TusharM and Peltier. Also maybe you can adopt other solutions though it may cost some efforts.

Tell me if you managed with the background fill one

---Yes now I realize that this can be done for more than 1 point: ...downloading... be patient!