Format Specific Trending Periods in Your Excel Dashboards

By Michael Alexander

One of the most common concepts used in Excel dashboards and reports is trending. Some of your trending components may contain certain periods in which a special event occurred, causing an anomaly in the trending pattern. For instance, you may have an unusually large spike or dip in the trend caused by some occurrence in your organization.

In such cases, it could be helpful to emphasize specific periods in your trending with special formatting.

Imagine you just created the chart component illustrated in this figure and you want to explain the spike in October. You could, of course, use a footnote somewhere, but that would force your audience to look for an explanation elsewhere on your dashboard. Calling attention to an anomaly directly on the chart helps give your audience context without the need to look away from the chart.

image0.jpg

A simple solution is to format the data point for October to appear in a different color and then add a simple text box that explains the spike.

To format a single data point:

  1. Click the data point once.

    This places dots on all the data points in the series.

  2. Click the data point again to ensure Excel knows you’re formatting only that one data point.

    The dots disappear from all but the target data point.

  3. Right-click and select Format Data Point.

    This opens the Format Data Point dialog box, as shown in this figure. The idea is to adjust the formatting properties of the data point as you see fit.

    image1.jpg

    The dialog box shown in the previous figure is for a column chart. Different chart types have different options in the Format Data Point dialog box. Nevertheless, the idea remains the same in that you can adjust the properties in the Format Data Point dialog to change the formatting of a single data point.

    After changing the fill color of the October data point and adding a text box with some context, the chart nicely explains the spike, as shown in this figure.

    image2.jpg

To add a text box to a chart, click the Insert tab on the Ribbon and select the Text Box icon. Then click inside the chart to create an empty text box, which you can fill with your words.