Chart Techniques
Highlight Normal Results in Line Charts
to Make Exceptional Results Stand Out
If you highlight the standard error on each side
of the trend, as
calculated by Excel's STEYX worksheet function, you'll help to
make exceptional results in a line chart stand out.
by Charley Kyd, MBA Microsoft Excel MVP, 20052014
The Father of Spreadsheet Dashboard Reports

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 shortterm trends.
Here, for example, the lightblue 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. I outline the method below. You also can
get this chart
right now, and use it as a pattern for your own work.
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: =D2StdError
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
These
instructions will show you how to set up the chart shown here.
You can get a version of the
fully formatted
chart with additional features at the link.
Here are the steps...
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.
Again, these instructions are for the basic elements of the
chart shown at the beginning of this section. To get a working version of
the formatted
line chart with
a standarderror path, follow the link.
