Adding a horizontal line to Excel charts (Target value)

Useful when creating excel dashboards and you want to include a threshold line/target value for your indicators.

Overview:

This step-by-step guide will show you how to add target values to your excel charts

Attached is an excel file to accompany the guide (you will see the various steps outlined in the excel chart)

Adding_horizontal_line.xlsx

Step-by-step guide

This confluence page is based off the following website: http://peltiertech.com/Excel/Charts/DummySeries.html#AddLine



Key steps

  1. Prepare your data and create your chart

  2. Add a second series that contains your target value (this can appear on the same excel sheet or a separate one)

    1. The X value range for the second series is from 0 to 1 and the Y value corresponds to your target value (150 in the example file)

  3. Select and copy the second series

  4. Right click your chart of interest, select paste and then paste special

  5. You will see the following dialog box:

 

 

 

   6. Select to add cells as "New Series" 

   7. Select "Series Names in first row" 

      You have now added the second series to your chart

   8. Right click the new series (Data points or the legend) and select "Chart type" from the pop up menu

 

 

 9. Choose the XY scatter type, scatter with smooth lines. Secondary X and Y axes should now appear

 

10. Right click the secondary X axes and select format axis

         a. Change the maximum bound from 1.2 to 1.0

         b. Click on Tick marks and change major and minor type to None

         c. Click on labels and change label position to None

11. Right click the secondary Y-axis and delete it. The target value should now move to its appropriate place in the chart

 

 

12. For more information on adding lines to charts, please see the following website 

 http://peltiertech.com/Excel/Charts/DummySeries.html#AddLine