Excel graph, Single line to have both a negative and positive componant.

[DominicJ]

Sorry the title is useless.


I have an accounts download with a lot of items, some debits, some credits, that are improperly hiding each other.
I have a graph based on a table based on a pivot, current out put shown below.
Orginaly, the graph was showing three columns, one of 50m one of 150 and one of 500, which isnt that hepful
I added a Cr/Dr Column, so now I get 6 columns, of -100, 150, -50, 200, 0 and 500
Which although an improvement, isnt really that clear either.

What I want, is a single column stretching from -100 to 150, another from -50 to 200, and a third from 0 to 500.
And if your method can easily replicated for the rest of the age buckets (Current, 0-30, 31-60 ect), I will be very greatful

Industry	Cr/Dr	Future
101	Cr	-100
101	Dr	150
102	Cr	-50
102	Dr	200
103	Cr	0
103	Dr	500

<TBODY>
</TBODY>

 

[DominicJ]

False Alarm, I tried selecting the second column chart type, whihc works when I just have one set of data,

So just 101 DR and 101 Cr
But it combines
102 DR/CR and 103 Dr/Cr in to it as well, which wasnt what I was after

 

[Jon Peltier]

Arrange the data like this:

Industry     CR      DR
  101      -100     150
  102       -50     200
  103         0     500

Make a line chart.
Add up/down bars (using the plus icon next to the chart in Excel 2013 or the item on the Chart Tools > Layout tab in 2001-2010), which will add vertical bars that go from the CR point to the DR point for each Industry.
Format the CR and DR chart series so they use no lines and no markers.