How to create a Horizon Graph in Excel?

Question

Does anyone know how to create a Horizon Graph in Excel (preferably without needing to download an Add-In)? I believe these were originally called, "two-tone pseudo charts" developed by Saito and progressed by Hannes Reijner (http://www.stonesc.com/Vis08_Workshop/DVD/Reijner_submission.pdf).

I've been looking for an Excel example online but the only results I've found thus far require add-ins or a special software program.

Answer 1

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.

1. Create a Table from your Data, Insert>Table
2. Assuming:
   • Category (X-axis) column
   • Value (Y-axis column),
3. 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())
4. Create a basic (not stacked) Area Chart.
5. Add the four series: Band1Data, Band2Data, NegBand1Data, NegBand2Data, in that order.
6. Format to taste.

Here's how the chart construction looks as it develops.

Answer 2

In order to "Mirror" the series (i.e. negative values in a different color and flipped to be above the line:

1. Add the series normally
2. Create a new column/series which is the first series *-1 and add it to the graph
3. Manually set the Primary Vertical Axis to have a minimum of 0 to show the positive values only
4. Set your gradiants for the two series, setting the stops on top of each other to have the transition be immediate (banded)

EDIT: Simplified by several steps

Answer 3

You could try using conditional formatting ... I have found an article that explains how to do it on line graphs.

http://peltiertech.com/WordPress/conditional-formatting-of-excel-charts/