Forum Discussion
Graph Secondary Axis align to zero of primary axis
If data is changing from time to time that's the same approach as Riny_van_Eekelen suggested but by VBA coding.
SergeiBaklan create a new line in the chart, with four cells: e.g.
b2 =0
b3 =0
c2 =-max(g3:g20)
c3 =-min(g3:g20)
Change g3:g20 to whatever the range is with the non-symmetrical data
Then add another line to your chart, x b2:b3 , y c2:c3
It will show up in the chart, and force the zero to the middle. You can then select that new data, select 'no line' and 'marker none', and if you select the legend entry, you can delete it from the list, so that it will be totally invisible, yet dynamically keep the charts centered. Do the same for a secondary axis to keep them both centered.
I guess it shall be bit more complex formula instead of =-MAX(range). Assume we have range with number from -200 to +10.
More important, putting zero into the middle is not always suitable. For example, we have values from -10 to 100 on primary and from 10 to 120 on secondary axis. The only we need is to have zero for both axis on the same line which is near the bottom of the chart.