Create Dynamic Target Line in Excel Bar Charts

If you are in the sales department and your life is all about targets, this charting technique is for you. And if you are not, read it anyway to learn some cool excel charting tricks.

In this blog post, I will show you a super way to create a dynamic target line in Excel chart, that can help you track your performance over the months. Something as shown below: Dynamic Target Line in Excel Chart

The target line is controlled by the scroll bar, and as if the target is met (or exceeded) in any of the months, it gets highlighted in green.

Creating a Dynamic Target Line in Excel Bar Chart

There are 3 parts to this chart:

  1. The bar chart
  2. The target line (horizontal dotted line)
  3. The scroll bar (to control the target value)

The Bar Chart

I have data as shown below:

Dynamic Target Line in Excel - Data Set

Cells B2:B13 has all the values while C2:C13 only shows a value if it exceeds the target value (in cell F2). If the value is lower than the target value, it shows #N/A. Now we need to plot these values in a cluster chart

  1. Select the entire data set (A1:C13)
  2. Go to Insert –> Charts –> Clustered Column Chart
    Dynamic Target Line in Excel - Clustered Column Chart
  3. Select any of the bars for ‘Above Target' values and right click and select Format Data Series
    Dynamic Target Line in Excel - Select Format Series
  4. In the Series Option section, change Series Overlap value to 100%
    Dynamic Target Line in Excel - 100 Percent Overlap in Chart Excel
  5. This creates a chart, where all the values that exceed the target are highlighted in a different color (you can check this by changing the target value)
    Dynamic Target Line in Excel - Target Values Highlighted in Different Color

 The Target Line

Here let me show you a smart way to create a target line using error bars

  1. Select the chart and go to Design –> Select Data
    Dynamic Target Line in Excel - Excel Chart Select Data
  2. In the Select Data Source dialog box, Click Add
  3. In the Edit Series box, Type Series Name as ‘Target Line' and in Series Value select your Target Value cell
    Dynamic Target Line in Excel - Adding a target line bar in Excel
  4. This will insert a bar chart only for the first data point (January)
    Dynamic Target Line in Excel - Adding a target line bar in Excel Chart
  5. Select this data bar and right click and select Change Series Chart Type
    Dynamic Target Line in Excel - Change Series Chart Type in Excel Chart
  6. Change its Chart Type to Scatter Chart. This will change the bar into a single dot in January
    Dynamic Target Line in Excel - Bar changed to scatter in Excel
  7. Select the data point and go to Design –> Chart Layouts –> Add Chart Element –> Error Bars –> More Error Bars Options
    • For Excel 2010, select the data point and go to Layout –> Analysis –> Error Bars –> More Error Bars Options
      Dynamic Target Line in Excel - Error Bars in Excel
  8. You will notice horizontal error bar lines on both sides of the scatter point. Select that horizontal error bar and then in the Error Bar Options section, select Custom and click on Specify Value
    Dynamic Target Line in Excel - Using Error Bars in Excel
  9. Give positive value as 11 and negative value as 0 (you can use hit and trial to see what value look good for your chart)
    Dynamic Target Line in Excel - Setting Values for Error Bars in Excel
  10. Select the Scatter data point and right click and select Format Series Data. Go to Marker Options and select Marker Type as none. This removes the data point and you only have the error bar (which is your target line)
  11. Note that your error bar would change whenever you change the target value. Just format it to make it a dotted line and change its color to make it look better

The Scroll Bar

  1. Create a scroll bar and align it with the chart. Fit the scroll bar along the chart. Click here to learn how to create a scroll bar in Excel.
  2. Make the maximum value of scroll bar equal to the maximum value in your chart, and link the scroll bar value to any cell (I have used G2)
  3. In the cell that has the target value, use the formula =500-G2 (500 is the maximum value in the chart)
  4. This is to make sure that your target value now moves with the scroll bar

That's it!! Now when you move the scroll bar and change the target values, the bars that meet the target will automatically get highlighted in a different color.

Try it yourself.. Download the file
Download File Pic

 

You May Also Like the Following Excel Tutorials:

Online Excel Tips Tutorials Training and Videos

DOWNLOAD FREE EXCEL EBOOK

51 Excel Tips to Save Time & Increase Productivity

(22,000+ Enthusiasts have Already Downloaded)