Represent Forecasts in Your Trending Components in 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, maybe you need to mix actual data with forecasts in your charting component.

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

It’s common to be asked to show both actual data and forecast data as a single trending component. When you do show the two together, you want to ensure that your audience can clearly distinguish where actual data ends and where forecasting begins. Take a look at this figure.

image0.jpg

The best way to achieve this effect is to start with a data structure similar to the one shown in the following figure. As you can see, sales and forecasts are in separate columns so that when charted, you get two distinct data series.

Also note the value in cell B14 is actually a formula referencing C14. This value serves to ensure a continuous trend line (with no gaps) when the two data series are charted together.

image1.jpg

When you have the appropriately structured dataset, you can create a line chart. At this point, you can apply special formatting to the 2013 Forecast data series. Follow these steps:

  1. Click the data series that represents 2013 Forecast.

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

  2. Right-click and select Format Data Series.

    This opens the Format Data Series dialog box.

  3. In this dialog box, you can adjust the properties to format the series color, thickness, and style.