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.

up vote 1 down vote accepted

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.

enter image description here

  • 1
    Thank you so much Dav! It's pretty embarrassing to admit how much time I've spent trying to create this on my own. Your explanation worked perfectly- keep up the good work! – Steve May 3 '14 at 21:00
  • It would be interesting to see a worksheet with sample data and formulas! – mg1075 Mar 4 '17 at 17:43

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)

enter image description here

EDIT: Simplified by several steps

  • Thanks Madball- this worked as well. I can't believe how easy this actually was.. ha – Steve May 3 '14 at 21:10

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/

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.