Show Performance against a Target Range in Your Excel Dashboards - dummies

Show Performance against a Target Range in Your Excel Dashboards

By Michael Alexander

Sometimes you need target ranges in your Excel dashboards and reports. In some businesses, a target isn’t one value — it’s a range of values. That is to say, the goal is to stay within a defined target range.

Imagine you manage a small business selling boxes of meat. Part of your job is to keep your inventory stocked between 25 and 35 boxes in a month. If you have too many boxes of meat, the meat will go bad. If you have too few boxes, you’ll lose money.

To track how well you do at keeping your inventory of meat between 25 and 35 boxes, you need a performance component that displays on-hand boxes against a target range. This figure illustrates a component you can build to track performance against a target range. The gray band represents the target range you must stay within each month. The line represents the trend of on-hand meat.

image0.jpg

Obviously, the trick to this type of component is to set up the band that represents the target range. Here’s how you do it:

  1. Set up a limit table in which you can define and adjust the upper and lower limits of your target range.

    Cells B2 and B3 in this figure serve as the place to define the limits for the range.

    image1.jpg

  2. Build a chart feeder that’s used to plot the data points for the target range.

    This feeder consists of the formulas revealed in cells B8 and B9 in the previous figure. The idea is to copy these formulas across all the data. The values you see for Feb, Mar, and Apr are the results of these formulas.

  3. Add a row for the actual performance values, as shown in this figure.

    image2.jpg

    These data points create the performance trend line.

  4. Select the entire chart feeder table and plot the data on a stacked area chart.

  5. Right-click the Values series and choose Change Series Chart Type. Use the Change Chart Type dialog box to change the Values series to a Line and to place it on the secondary axis, as shown in this figure. After confirming your change, the Values series appears on the chart as a line.

    image3.jpg

  6. Go back to your chart and delete the new vertical axis that was added; it’s the vertical axis to the right of the chart.

  7. Right-click the Lower Limit data series and choose Format Data Series.

  8. In the Format Data Series dialog, click the Fill icon. Choose the No Fill option under Fill, and the No Line option under Border, as shown in this figure.

    image4.jpg

  9. Right-click the Upper Limit series and select Format Data Series.

  10. In the Format Series dialog box, adjust the Gap Width property to 0%.

    That’s it. All that’s left to do is apply the minor adjustments to colors, labels, and other formatting.