Excel Dashboards and Reports: Show Performance against Organizational Trends

By Michael Alexander

The target you use to measure performance for your Excel dashboards and reports doesn’t necessarily have to be set by management or organizational policy. In fact, some of the things you measure may never have a formal target or goal set for them. In situations in which you don’t have a target to measure against, it’s often helpful to measure performance against some organizational statistic.

For example, the component in this figure measures the sales performance for each division against the median sales for all the divisions. You can see that divisions 1, 3, and 6 fall well below the median for the group.

image0.jpg

Here’s how you’d create a median line similar to the one you see in the previous figure:

  1. Start a new column next to your data and type the simple MEDIAN formula, as shown in this figure.

    image1.jpg

    Note that this formula can be any mathematical or statistical operation that works for the data you’re representing. Just make sure that the values returned are the same for the entire column. This gives you a straight line.

  2. Copy the formula down to fill the table.

    Again, all the numbers in the newly created column should be the same.

  3. Plot the table into a column chart.

  4. Right-click the Median data series and choose Change Series Chart Type.

  5. Change the chart type to a line chart.