Bar Chart where numbers less than 1 treated as negative

[coriforia]

I am working on an academic manuscript and making a bar chart figure for the article. I'm using risk ratios so numbers greater than 1 are a positive association and numbers less than 1 are a negative association. I know excel will do bars going down for negative numbers, but is it possible to make the axis 1 and have numbers less than 1 have bars going down? I have figured out how to make the axis 1, but I can't figure out how to make 0.80, for instance, go downward while 1.2 will go upward. Any help would be greatly appreciated!

 

[JustynaMK]

Hi coriforia,

You can probably do the following:

Column A - dates (01/01/2010, 02/01/2010, 03/01/2010 ...)
Column B - original values (1.1, 1.2, 0.9, 0.8, 1, 1.5 ...)
Column C - column B minus one (0.1, 0.2, -0.1, -0.2, 0, 0.5 ...)

Select all three columns and create a Column Chart. Select Series 2 (source: Column C) and move it to "Secondary Axis" (both bars will be now stacked). Select Series 1 (source: Column B) and remove the Fill color. Select second Y-Axis and remove the Axis Labels.

Let me know if it works for you.