Exceljet

Quick, clean, and to the point

Column chart with target line

Example Excel combo chart - daily sales versus ads

Combo charts combine more than one Excel chart type in the same chart. One way you can use a combo chart is to show actual values in columns together with a line that shows a goal or target value. In the chart shown in this example, daily sales are plotted in columns, and a line shows target sales of $500 per day.

This example uses a combo chart based on a column chart to plot daily sales and an XY scatter chart to plot the target. The trick is to plot just one point in the XY scatter chart, then use error bars to create a continuous line that extends across the entire plot area.

Data

The data used to build this chart is shown below:

Data for column chart with target line

How to create this chart

  1. Select the sales data and insert a Column chart
    Select sales data, insert column chart  
  2. Select target line data and copy. Then select chart > paste special:
    Add series with copy + paste special  
  3. Column chart after pasting target line data:
    After paste special to add second data series  
  4. Right-click chart, then change chart type to Combo Chart:
    Right-click and change to combo chart  
  5. Make Target Series an XY Scatter Chart
    Combo chart settings - make target data xy scatter  
  6. Select target data point, then add error bars to chart
    Add error bars  
  7. Select X (horizontal) error bar; set Fixed value = 1.
    Enable minimum value for horizontal error bar  
  8. Select Y error bars, then press delete to remove:
    Select Y error bars and delete  
  9. Current chart after adjusting X error bars and removing Y error bars:
    After removing Y error bars  
  10. Select secondary value axis, then set Maximum bounds = 1, and Label Position = "None":
    Adjust secondary axis to hide  
  11. Select and delete secondary vertical axis.
  12. Select horizontal error bar and set end style = "No Cap":
    Set horizontal error bar to No Cap  
  13. Set Target data series marker to "None":
    Set Target data series marker to "None"  
  14. Current chart with sales in columns and target as edge-to-edge line :
    After removing error bar marker and end cap

From this point, you have the basic column chart with edge-to-edge target line.

You can now format the chart as you like: add a title, set color and width for the target sales line, add data labels, etc.

Note: I learned the error bar approach from Excel chart guru Jon Peltier.

Chart type 
Author 
Dave Bruns