Excel will do this, but it takes several helper columns. My example makes use of an Excel Table to simplify dealing with the formulas, but you could use just formulas. Generally, you'll be using a standard Area Chart, with the series split into the individual bands you need, so most of the work is done in getting the formulas correct. For this sample, I've made a four-strip horizon chart.
- Create a Table from your Data,
Insert>Table
- Assuming:
- Category (X-axis) column
- Value (Y-axis column),
- Add the following additional columns:
- Band1Value = value you want for your first break (should be same for entire column).
- Band2Value = value you want for your second break (should be same for entire column).
- AbsValue
=ABS([@Value])
, to help display negative values above the X axis.
- Band1Data
=IF([@AbsValue]>[@Band1Value],[@Band1Value],[@AbsValue])
.
- Band2Data
=IF([@AbsValue]>[@Band2Value],[@Band1Value],IF([@AbsValue]-[Band1Value]<0,0,[AbsValue]-[Band1Value]))
.
- NegBand1Data
=IF([@Value]<0,[@Band1Data],NA())
- NegBand2Data
=IF([@Value]<0,[@Band2Data],NA())
- Create a basic (not stacked) Area Chart.
- Add the four series: Band1Data, Band2Data, NegBand1Data, NegBand2Data, in that order.
- Format to taste.
Here's how the chart construction looks as it develops.
