Line charts that show trends in performance are the most useful type of chart that management reports can contain.
All managers want good performance to improve and bad performance to decline. Line charts are the best tool you can use to make that progress quick and easy to see.
However, line charts do have one problem: They make it easy for readers to give too much meaning to short-term trends.
Here, for example, the light-blue path shows that the trend is down. But twice during the 13 months shown, performance jumped quickly to the top of that path.
Each time, it would have been easy for readers to view those results as a the sign of a turnaround. But the path indicates that performance merely bounced upward because of random events during a slow decline.
That is, including a path like this to the chart gives your readers the ability to differentiate between typical results and exceptional results.
You can add a path like this to your own charts, as you’ll see below.
The Standard Error and the STEYX Function
The standard error of the estimate—also known as the standard error of regression—is a measure of the degree of variability around a trend line. (The TREND line is the “estimate” for which we calculate the standard error.)
Essentially, the standard error is the standard deviation around the trend line. But as illustrated in the chart, you don’t actually need to display the trend line in a chart.
We use the STEYX function to calculate the standard error. This is easy to do, as the following example illustrates. In the chart above, the blue path displays two standard errors, one on each side of the trend line.
Set Up Your Data
The first step is to set up your data, as shown here.
To make the formulas easier to understand, I’ve named each column with the titles shown in row 1. I’ve also assigned the name StdError to cell B15.
The following descriptions provide the formulas or numbers for each column in the table. Unless otherwise explained, enter the formulas as shown and then copy them to the rest of their column within the table.
A14: =CurPd
Cell A14 displays the report date, which I entered on a different worksheet, in a cell named CurPd. I typically name that other sheet Control. To get started, you could name any nearby cell CurPd, then enter a date for any month and year, using the first day of the month.
A13: =DATE(YEAR(A14),MONTH(A14)-1,1)
This formula returns the date serial number one month prior to the date in cell A14. Copy this formula to the remainder of dates in column A to return date serial numbers for the preceding 12 months.
B2: =TEXT(A2,”mm”)&IF(MONTH(A2)=MONTH(CurPd),CHAR(13)&”‘”&TEXT(A2,”yy”),””)
The formula in cell B2 relies on the TEXT function to return dates as text for use in the chart. If the month in cell A2 is the same as the month of the CurPd, the formula includes the year. The “CHAR(13)” in the formula causes Excel charts to generate a carriage return character, which wraps the year to the second row in the X axis, as shown in the chart. (This one of the few ways I ever use the CHAR function, by the way.)
C2: 196
The numbers in column C are the values to be charted. Enter your sales or other data.
D2: =TREND(Sales,DateSer,A2)
The formulas using the TREND function in column D draw a trend line for the Sales data.
B15: =STEYX(Sales,DateSer)
Cell B15 calculates the standard error, where Sales contains the Y values and the DateSer column contains the X values. (Think of the STEYX function as “return the standard error (STE) of Y given X.)
E2: =D2-StdError
This formula subtracts the StdError from the value for the trend in the current row.
F2: =StdError*2
This formula returns twice the value of the StdError. It represents the constant height of the the blue band shown in the chart.
Set Up the Line Chart with a Path
Here are the steps for setting up this chart:
1. Select the range B2:C14 in the table above.
2. To create the chart with the line, choose Insert, Charts, Line, Line.
3. To begin to add the path, copy the range E2:E14; select the chart object, then press Ctrl+V to paste it to the chart. The new plot will appear as a straight line.
4. Select the new line, then choose Chart Tools, Design, Change Chart Type. In the dialog, choose Stacked Area for the Series 2 series, then choose OK.
5. Copy the range F2:F14; select the chart object, then press Ctrl+V to paste it in the chart. The new plot also will appear as a straight line.
6. Select the new line. Choose Chart Tools, Design, Change Chart Type. In the dialog, choose Stacked area for the Series 3 series, then choose OK. After you do so, your chart should look something like this.
7. We don’t need the legends or chart title, if shown. So select and delete them as appropriate.
8. We need the red area plot to support the green area. But we don’t want to see it. Therefore, select the red area, press Ctrl+1 to access the Fill option in Excel’s panel or dialog, then choose No Fill.
9. Select the green area plot; press Ctrl+1 to access the Solid Fill option; change the color to a medium blue; then set the Transparency to 50%.
10. If the Y axis has a border, remove it. To do so, select the Y axis, press Ctrl+1 to launch the format dialog; then set the Line Color to No Line.
11. Select the X axis; press Ctrl+1 to launch the dialog or panel that gives you access to the Axis Options; then choose the Position Axis setting from Between Tick Marks to On Tick Marks.
After you change the size of your modified chart, it should look something like the version at the beginning of this section.
