For business users of Microsoft Excel Free guides and templates

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, 2005-2014
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.

A formatted line chart with a path defined by the standard error of the estimate.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. 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 table of data and calculations for the line chart.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

The chart you'll create by following the information below.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.

The preliminary version of the chart.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 standard-error path, follow the link.

 

Free Excel Dashboards

Click to see testimonials from readers

Charley's SwipeFile charts

Click to see who uses Excel dashboards.