Chart Examples

This section contains a variety of chart examples that you may find useful or informative.

Charting Progress toward a Goal

You're probably familiar with a thermometer-type display that shows the percentage of a task that's completed. Creating such a display in Excel is very easy. The trick involves creating a chart that uses a single cell (which holds a percentage value) as a data series.

Figure 17-4 shows a worksheet set up to track daily progress toward a goal: 1,000 new customers in a 15-day period. Cell B18 contains the goal value, and cell B19 sums the values in column B. Cell B21 contains a simple formula that calculates the percent of goal:

 =B19/B18 

Figure 17-4: This chart displays progress toward a goal.

As you enter new data in column B, the formulas display the current results.

To make the thermometer chart, select cell B21 and create a column chart from that single cell. Notice the blank cell above cell B21. Without this blank cell, Excel uses the entire data block for the chart, not just the single cell. Because B21 is isolated from the other data, the data series consists of a single cell.

Other changes required are

• Select the horizontal category axis and press Delete to remove the category axis from the chart.

• Add a chart title. (I formatted it to display at an angle and then moved it to the bottom of the chart.)

• Remove the legend.

• Add data labels to the chart to display the percent accomplished.

• In the Format Data Series dialog box (Series Options tab), set the Gap width to 0, which makes the column occupy the entire width of the plot area.

• Select the Value Axis and display the Format Value Axis dialog box. In the Axis Options tab, set the Minimum to 0 and the Maximum to 1.

You can make other cosmetic changes as you like. For example, you may want to change the chart's width to make it look more like a thermometer, as well as adjust fonts, colors, and so on.

On the CD 

This example, named  thermometer chart.xlsx, is available on the companion CD-ROM.

Creating a Gauge Chart

Figure 17-5 shows another chart based on a single cell. It's a pie chart set up to resemble a gauge. Although this chart displays only one value (entered in cell B1), it actually uses three data points (in A4:A6).

Figure 17-5: This chart resembles a speedometer gauge and displays a value between 0 and 100 percent.

On the CD 

A workbook with this example is available on the companion CD-ROM. The filename is  gauge chart.xlsx.

One slice of the pie-the slice at the bottom-always consists of 50 percent, and that slice is hidden (by using the No Fill and No Outline settings). The other two slices are apportioned based on the value in cell B1. The formula in cell 44 is

 =MIN(B1,100%)/2 

This formula uses the MIN function to display the smaller of two values: either the value in cell B1 or 100 percent. It then divides this value by 2 because only the top half of the pie is relevant. Using the MIN function prevents the chart from displaying more than 100 percent.

The formula in cell A5 simply calculates the remaining part of the pie-the part to the right of the gauge's needle:

 =50%-A4 

The chart's title was moved below the half-pie. The chart also includes data labels. I deleted two of the data labels and added a link to the remaining one so that it displays the percent completed value in cell B1.

Displaying Conditional Colors in a Column Chart

The Fill tab of the Format Data Series dialog box has an option labeled Vary Colors by Point. This option simply uses more colors for the data series. Unfortunately, the colors aren't related to the values of the data series.

This section describes how to create a column chart in which the color of each column depends on the value that it's displaying. Figure 17-6 shows such a chart. (It's more impressive when you see it in color.) The data used to create the chart is in range A1:F14.

Figure 17-6: The color of the column depends varies with the value.

On the CD 

A workbook with this example is available on the companion CD-ROM. The filename is  conditional colors.xlsx.

This chart displays four data series, but some data is missing for each series. The data for the chart is entered in column B. Formulas in columns C:F determine which series the number belongs to by referencing the bins in Row 1. For example, the formula in cell C3 is

 =IF(B3<=$C$1,B3,"") 

If the value in column B is less than the value in cell C1, the value goes in this column. The formulas are set up such that a value in column B goes into only one column in the row.

The formula in cell D3 is a bit more complex because it must determine whether cell C3 is greater than the value in cell C1 and less than or equal to the value in cell D1:

 =IF(AND($B3>C$1,$B3<=D$1),$B3,"") 

The four data series are overlaid on top of each other in the chart. The trick involves setting the Series Overlap value to a large number. This setting determines the spacing between the series. Use the Series Options tab of the Format Data Series dialog box to adjust this setting

Note 

Series Overlap is a single setting for the chart. If you change the setting for one series, the other series change to the same value.

Creating a Comparative Histogram

With a bit of creativity, you can create charts that you may have considered impossible. For example, Figure 17-7 shows a chart sometimes referred to as a comparative histogram chart. Such charts often display population data.

Figure 17-7: A comparative histogram.

On the CD 

A workbook with this example is available on the companion CD-ROM. The filename is  comparative histogram.xlsx.

Here's how to create the chart:

1. Enter the data in A1:C8, as shown in Figure 17-7. Notice that the values for females are entered as negative values, which is very important.

2. Select A1:C8 and create a bar chart. Use the subtype labeled Clustered Bar.

3. Select the horizontal axis and display the Format Axis dialog box.

4. Click the Number tab and specify the following custom number format:

    0%;0%;0% 

   This custom format eliminates the negative signs in the percentages.

5. Select the vertical axis and display the Format Axis dialog box.

6. In the Axis Options tab, set all tick marks to None and set the Axis Labels option to Low. This setting keeps the vertical axis in the center of the chart but displays the axis labels at the left side.

7. Select either of the data series and display the Format Data Series dialog box.

8. In the Series Options tab, set the Series Overlap to 100% and the Gap Width to 0%.

9. Delete the legend and add two text boxes to the chart (Females and Males) to substitute for the legend.

10. Apply other formatting and labels as desired.

Creating a Gantt Chart

A Gantt chart is a horizontal bar chart often used in project management applications. Although Excel doesn't support Gantt charts per se, creating a simple Gantt chart is fairly easy. The key is getting your data set up properly.

Handling Missing Data

Sometimes, data that you're charting may be missing one or more data points. As shown in the accompanying figure, Excel offers three ways to handle the missing data:

• Gaps: Missing data is simply ignored, and the data series will have a gap.

  This is the default.

• Zero: Missing data is treated as zero.

• Connect with Line: Missing data is interpolated-calculated by using data on either side of the missing point(s).

This option is available only for line charts, area charts, and XY charts.

To specify how to deal with missing data for a chart, choose Chart Tools Design Data Select Data. In the Select Data Source, click the Hidden and Empty Cells button. Excel displays its Hidden and Empty Cell Settings dialog box. Make your choice in the dialog box. The option that you choose applies to the entire chart, and you can't set a different option for different series in the same chart.

Normally, a chart doesn't display data that's in a hidden row or columns. You can use the Hidden and Empty Cell Settings dialog box to force a chart to use hidden data.

Figure 17-8 shows a Gantt chart that depicts the schedule for a project, which is in the range A2:C13. The horizontal axis represents the total time span of the project, and each bar represents a project task. The viewer can quickly see the duration for each task and identify overlapping tasks.

Figure 17-8: You can create a simple Gantt chart from a bar chart.

On the CD 

A workbook with this example is available on the companion CD-ROM. The filename is  gantt chart.xlsx.

Column A contains the task name, column B contains the corresponding start date, and column C contains the duration of the task, in days.

Follow these steps to create this chart:

1. Select the range A2:C13, and create a Stacked Bar Chart.

2. Delete the legend.

3. Select the category (vertical) axis, and display the Format Axis dialog box.

4. In the Format Axis dialog box, specify Categories in Reverse Order to display the tasks in order, starting at the top. Choose Horizontal Axis Crosses at Maximum Category to display the dates at the bottom.

5. Select the Start Date data series, and display the Format Data Series dialog box.

6. In the Format Data Series dialog box, click the Series Options tab and set the Series Overlap to 100%. Click the Fill tab, and specify No Fill. Click the Border Color tab and specify No Line. These steps effectively hide the data series.

7. Select the value (horizontal) axis and display the Format Axis dialog box.

8. In the Format Axis dialog box, adjust the Minimum and Maximum settings to accommodate the dates that you want to display on the axis.

   Unfortunately, you must enter these values as date serial numbers, not actual dates. In this example, the Minimum is 39181 (April 9, 2007) and the Maximum is 39261 (June 28, 2007). Specify 7 for the Major Unit, to display one week intervals. Use the number tab to specify a date format for the axis labels.

9. Apply other formatting as desired.

Creating a Box Plot

A box plot (sometimes known as a quartile plot) is often used to summarize data. Figure 17-9 shows a box plot created for four groups of data. The raw data appears in columns A through D. The range G2:J7, used in the chart, contains formulas that summarize the data. Table 17-1 shows the formulas in column G (which were copied to the three columns to the right).

On the CD 

A workbook with this example is available on the companion CD-ROM. The filename is  box plot.xlsx.

Figure 17-9: This box plot summarizes the data in columns A through D.

Table 17-1: FORMULAS USED TO CREATE A BOX PLOT
Open table as spreadsheet

Cell	Calculation	Formula
G2	25th Percentile	=QUARTILE(A2:A26,1)
G3	Minimum	=MIN(A2:A26)
G4	Mean	=AVERAGE(A2:A26)
G5	50th Percentile	=QUARTILE(A2:A26,2)
G6	Maximum	=MAX(A2:A26)
G7	75th Percentile	=QUARTILE(A2:A26,3)

Follow these steps to create the box plot:

1. Select the range F1:J7.

2. Choose Insert Charts Line, and select the fourth subtype, Line with Markers.

3. Choose Chart Tools Design Data Switch Row/Column to change the orientation of the chart.

4. Choose Chart Tools Layout Analysis Up/Down Bars Up/Down Bars to add up/down bars that connect the first data series (25th Percentile) with the last data series (75th Percentile).

5. Remove the markers from the 25th Percentile series and the 75th percentile series.

6. Choose Chart Tools Layout Analysis Lines Hi-Lo Lines to add a vertical line between the each point to connect the Minimum and Maximum data series.

7. Remove the lines from each of the six data series.

8. Change the series marker to a horizontal line for the following series: Minimum, Maximum, and 50th Percentile.

9. Make other formatting changes as required.

Tip 

After performing all these steps, you may want to create a template to simplify the creation of additional box plots. Activate the chart, and choose Chart Tools Design Type Save As Template.

The legend for this chart displays the series in the order in which they are plotted-which is not the optimal order and may be confusing. Unfortunately, you can't change the plot order because the order is important. (The up/down bars use the first and last series.) If you find that the legend is confusing, you may want to delete all the legend entries except for Mean and 50th Percentile.

Plotting Every nth Data Point

Normally, Excel doesn't plot data that resides in a hidden row or column. You can sometimes use this to your advantage because it's an easy way to control what data appears in the chart.

Suppose you have a lot of data in a column, and you want to plot only every tenth data point. One way to accomplish this is to use filtering in conjunction with a formula. Figure 17-10 shows a two-column table with filtering in effect. The chart plots only the data in the visible (filtered) rows and ignores the values in the hidden rows.

On the CD 

The example in this section, named  plot every nth data point.xlsx, is available on the companion CD-ROM.

Figure 17-10: This chart plots every nth data point (specified in A1) by ignoring data in the rows hidden by filtering.

Cell A1 contains the value 10. The value in this cell determines which rows to hide. Column B contains identical formulas that use the value in cell A1. For example, the formula in cell B4 is as follows:

 =MOD(ROW()-ROW($A$4),$A$1) 

This formula subtracts the current row number from the first data row number in the table, and uses the MOD function to calculate the remainder when that value is divided by the value in A1. As a result, every nth cell (beginning with row 4) contains 0. Use the filter drop-down in cell B3 specify a filter that shows only the rows that contain a 0 in column B.

Note 

If you change the value in cell A1, you need to respecify the filter criteria for column B. (The rows will not hide automatically.)

Although this example uses a table (created using Insert Tables Table), the technique also works with a normal range of data as long as it has column headers. Choose Data Sort & Filter Filter to enable filtering.

Plotting the Last n Data Points

You can use a technique that makes your chart show only the most recent data points in a column. For example, you can create a chart that always displays the most recent six months of data (see Figure 17-11).

Figure 17-11: This chart displays the six most recent data points.

The instructions that follow describe how to create the chart in this figure:

1. Create a worksheet like the one shown in Figure 17-11, and create a chart that uses the data in A1: B26.

2. Choose Formulas Defined Names Name Manager to bring up the Name Manager dialog box.

3. Click New to display the New Name dialog box.

4. In the Name field, enter Dates. In the Refers To field, enter this formula:

    =OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-6,0,6,0) 

   Notice that the OFFSET function refers to cell A1 (not the cell with the first month).

5. Click OK to close the New Name dialog box.

6. Click New to define the second name.

7. In the New Name dialog box, type Sales in the Names in Workbook field. Enter this formula in the Refers To field:

    =OFFSET(Sheet1!$B$1,COUNTA(Sheet1!$B:$B)-6,0,6,1) 

8. Click OK, and then click Close to close the dialog box Name Manager dialog box.

9. Activate the chart and select the data series.

10. In the SERIES formula, replace the range references with the names that you defined in Steps 4 and 7. The formula should read:

     =SERIES(,Sheet1!Dates,Sheet1!Sales,1) 

Note 

To plot a different number of data points, adjust the formulas entered in Steps 4 and 7. Replace all occurrences of 6 with your new value.

On the CD 

The example in this section, named  plot last n data points.xlsx, is available on the companion CD-ROM.

Selecting a Series from a Combo Box

Figure 17-12 shows a chart that displays data as specified by a drop-down control (known as a combo box). The chart uses the data in A1:D2, but the month selected in the combo box determines the contents of these cells. Range A6:D17 contains the monthly data, and formulas in A2:D2 display the data using the value in cell F1 (which is linked to the combo box). For example, when cell F1 contains the value 4, the chart displays data for April (the fourth month).

Figure 17-12: Selecting data to plot using a Combo Box.

The formula in cell A2 is

 =INDEX(A6:A17,$F$1) 

This formula was copied to B2:D2.

The key here is to get the combo box to display the month names and place the selected month index into cell F1. To create the combo box, follow these steps:

1. Make sure that Excel's Developer tab is displayed. If you don't see this tab, choose Office Button Excel Options. Click the Popular tab and place a check mark next to Show Developer Tab in the Ribbon.

2. Choose Developer Controls Insert, and click the Combo Box icon in the Form Controls section.

3. Drag in the worksheet to create the control.

4. Right-click the combo box and choose Format Control to display the Format Control dialog box.

5. In the Format Control dialog box, click the Control tab.

6. Specify A6:A17 as the Input Range, and F1 as the Cell link.

After you perform these steps, the combo box displays the month names and places the index number of the selected month into cell F1. The formulas in row 2 display the appropriate data, which displays in the chart.

On the CD 

This example is available on the companion CD-ROM. The filename is chart from combo box.xlsx.

Plotting Mathematical Functions

The examples in this section demonstrate how to plot mathematical functions that use one variable (a 2-D line chart) and two variables (a 3-D surface chart). The examples make use of Excel's Data Table feature, which enables you to evaluate a formula with varying input values.

Note 

A Data Table is not the same as a table, created using Insert Tables Table.

PLOTTING FUNCTIONS WITH ONE VARIABLE

An XY chart is useful for plotting various mathematical and trigonometric functions. For example, Figure 17-13 shows a plot of the SIN function. The chart plots y for values of x (expressed in radians) from –5 to +5 in increments of 0.5. Each pair of x and y values appears as a data point in the chart, and the points connect with a line.

Figure 17-13: This chart plots the SIN(x).

Tip 

Excel's trigonometric functions use angles expressed in radians. To convert degrees to radians, use the RADIANS function.

The function is expressed as

 y = SIN(x) 

The corresponding formula in cell B2 (which is copied to the cells below) is

 =SIN(A2) 

Figure 17-14 shows a general-purpose, single-variable plotting application. The data for the chart is calculated by a Data Table in columns I:J. Follow these steps to use this application:

1. Enter a formula in cell B7. The formula should contain at least one x variable. In the figure, the formula in cell B3 is

    =SIN(PI() *x) * (PI() *x) 

2. Enter the minimum value for x in cell B8.

3. Enter the maximum value for x cell B9.

Figure 17-14: A general-purpose, single-variable plotting workbook.

The formula in cell B3 displays the value of y for the minimum value of x. The Data Table, however, evaluates the formula for 200 equally spaced values of x, and these values appear in the chart.

On the CD 

This workbook, named  function plot 2D.xlsx, is available on the companion CD-ROM.

PLOTTING FUNCTIONS WITH TWO VARIABLES

The preceding section describes how to plot functions that use a single variable (x). You also can plot functions that use two variables. For example, the following function calculates a value of z for various values of two variables (x and y):

 z = SIN(x)*COS(y) 

Figure 17-15 shows a surface chart that plots the value of z for 21 x values ranging from 2 to 5 (in 0.15 increments) and for 21 y values ranging from –3 to 0 (also in 0.15 increments).

Figure 17-15: Using a surface chart to plot a function with two variables.

Figure 17-16 shows a general-purpose, two-variable plotting application, similar to the single-variable workbook described in the previous section. The data for the chart is a 25 x 25 data table in range M7:AL32 (not shown in the figure). To use this application

1. Enter a formula in cell B3. The formula should contain at least one x variable and at least one y variable. In the figure, the formula in cell B3 is

    =COS(y*x) 

2. Enter the minimum x value in cell B4 and the maximum x value in cell B5.

3. Enter the minimum y value in cell B6 and the maximum y value in cell B7.

Figure 17-16: A general-purpose, two-variable plotting workbook.

The formula in cell B3 displays the value of z for the minimum values of x and y. The data table evaluates the formula for 25 equally spaced values of x and 25 equally spaced values of y. These values are plotted in the surface chart.

On the CD 

This workbook, which is available on the companion CD-ROM, contains simple macros that enable you to easily change the rotation and elevation of the chart by using scrollbars. The file is named  function plot 3D.xlsm.

Plotting a Circle

You can create an XY chart that draws a perfect circle. To do so, you need two ranges: one for the x values and another for the y values. The number of data points in the series determines the smoothness of the circle. Or you can simply select the Smoothed Line option in the Format Data Series dialog box (Line Style tab) for the data series.

Figure 17-17 shows a chart that uses 13 points to create a circle. If you work in degrees, generate a series of values such as the ones shown in column A. The series starts with 0 and increases in 30-degree increments. If you work in radians (column B), the first series starts with 0 and increments by Π/6.

Figure 17-17: Creating a circle using an XY chart.

The ranges used in the chart appear in columns D and E. If you work in degrees, the formula in cell D2 is

 =SIN(RADIANS(A2)) 

The formula in cell E2 is

 =COS(RADIANS(A2)) 

If you work in radians, use this formula in cell D2:

 =SIN(A2) 

And use this formula in cell E2:

 =COS(A2) 

The formulas in cells D2 and E2 are copied down to subsequent rows.

To plot a circle with more data points, you need to adjust the increment value and the number of data points in column A (or column B if working in radians). The final value should be the same as those shown in row 14. In degrees, the increment is 360 divided by the number of data points minus 1. In radians, the increment is Π divided by the number of data points minus 1, divided by 2.

Figure 17-18 shows a general circle plotting application that uses 37 data points. In range H27:H29, you can specify the x origin, the y origin, and the radius for the circle (these are named cells). In the figure, the circle's origin is at 1,3 and it has a radius of 7.25.

Figure 17-18: A general circle plotting application.

The formula in cell D2 is:

 =(SIN(RADIANS(A2))*radius)+x_origin 

The formula in cell E2 is:

 =(COS(RADIANS(A2))*radius)+y_origin 

On the CD 

This example, named  plot circles.xlsx, is available on the companion CD-ROM.

Creating a Clock Chart

Figure 17-19 shows an XY chart formatted to look like a clock. It not only looks like a clock, but it also functions like a clock. There is really no reason why anyone would need to display a clock such as this on a worksheet, but creating the workbook was challenging, and you may find it instructive.

Figure 17-19: This fully functional clock is actually an XY chart in disguise.

The chart uses four data series: one for the hour hand, one for the minute hand, one for the second hand, and one for the numbers. The last data series draws a circle with 12 points (but no line). The numbers consist of manually entered data labels. In addition, I added an oval shape on top of the chart.

The formulas listed in Table 17-2 use basic trigonometry to calculate the data series for the clock hands. (The range G4:L4 contains zero values, not formulas.)

Table 17-2: FORMULAS USED TO GENERATE A CLOCK CHART
Open table as spreadsheet

Cell	Description	Formula
G5	Origin of hour hand	=0.5*SIN((HOUR(NOW())+(MINUTE(NOW())/60))* (2*PI()/12))
H5	End of hour hand	=0.5*COS((HOUR(NOW())+(MINUTE(NOW())/60))* (2*PI()/12))
I5	Origin of minute hand	=0.8*SIN((MINUTE(NOW())+(SECOND(NOW())/60))* (2*PI()/60))
J5	End of minute hand	=0.8*COS((MINUTE(NOW())+(SECOND(NOW())/60))* (2*PI()/60))
K5	Origin of second hand	=0.85*SIN(SECOND(NOW())*(2*PI()/60))
L5	End of second hand	=0.85*COS(SECOND(NOW())*(2*PI()/60))

This workbook uses a simple VBA procedure that schedules an event every second, which causes the clock to run.

In addition to the clock chart, the workbook contains a text box that displays the time using the NOW() function, as shown in Figure 17-20. Normally hidden behind the analog clock, you can display this text box by deselecting the Analog Clock check box. A simple VBA procedure attached to the check box hides and unhides the chart, depending on the status of the check box.

Figure 17-20: Displaying a digital clock in a worksheet is much easier but not as fun to create.

On the CD 

The workbook with the animated clock example appears on the companion CD-ROM. The filename is clock chart.xlsx.

When you examine the workbook, keep the following points in mind:

• The ChartObject, named ClockChart, covers up a range named DigitalClock, which is used to display the time digitally.

• The two buttons on the worksheet are from the Forms group (Developer Controls Insert), and each has a VBA procedure assigned to it (StartClock and StopClock).

• Clicking the check box control executes a procedure named cbClockType_Click, which simply toggles the Visible property of the chart. When the chart is hidden, the digital clock is revealed.

• The UpdateClock procedure uses the OnTime method of the Application object. This method enables you to execute a procedure at a specific time. Before the UpdateClock procedure ends, it sets up a new OnTime event that occurs in one second. In other words, the UpdateClock procedure is called every second.

• The UpdateClock procedure inserts the following formula into the cell named DigitalClock:

   =NOW() 

  Inserting this formula causes the workbook to calculate, updating the clock.

Creating Awesome Designs

Figure 17-21 shows an example of an XY chart that displays hypocycloid curves using random values. This type of curve is the same as that generated by Hasbro's popular Spirograph toy, which you may remember from childhood.

Figure 17-21: A hypocycloid curve.

On the CD 

The companion CD-ROM contains two hypocycloid workbooks: the simple example shown in Figure 17-18 (named  hypocycloid chart.xlsx), and a much more complex example (named hypocycloid animated.xlsm) that adds animation and a few other accoutrements. The animated version uses VBA macros.

The chart uses data in columns D and E (the x and y ranges). These columns contain formulas that rely on data in columns A through C. The formulas in columns A through C rely on the values stored in E1:E3. The data column for the x values (column D) consists of the following formula:

 =(A6-B6)*COS(C6)+B6*COS((A6/B6-1)*C6) 

The formula in the y values (column E) is as follows:

 =(A6-B6)*SIN(C6)-B6*SIN((A6/B6-1)*C6) 

Pressing F9 recalculates the worksheet, which generates new random increment values for E1:E3, and creates a new display in the chart. The variety (and beauty) of charts generated using these formulas may amaze you.

Working with Trendlines

With some charts, you may want to plot a trendline that describes the data. A trendline points out general trends in your data. In some cases, you can forecast future data with trendlines. A single series can have more than one trendline.

To add a trendline, select the chart series and then choose Chart Tools Layout Analysis Trendline. This drop-down control displays options for four types of trendlines. For additional options (and more control over the trendline) choose More Trendline Options, which displays the Trendline Options tab of the Format Trendline dialog box (see Figure 17-22).

Figure 17-22: The Format Trendline dialog box offers several types of automatic trendlines.

The type of trendline that you choose depends on your data. Linear trends are the most common type, but you can describe some data more effectively with another type.

The Trendline Options tab enables you to specify a name to appear in the legend and the number of periods that you want to forecast (if any). Additional options enable you to set the intercept value, specify that the equation used for the trendline should appear on the chart, and choose whether the R-squared value appears on the chart.

When Excel inserts a trendline, it may look like a new data series, but it's not. It's a new chart element with a name, such as Series 1 Trendline 1. And, of course, a trendline does not have a corresponding SERIES formula.

Linear Trendlines

Figure 17-23 shows two charts. The chart on the left depicts a data series without a trendline. As you can see, the data seems to be "linear" over time. The chart on the right is the same chart but with a linear trendline that shows the trend in the data.

Figure 17-23: Before (left chart) and after (right chart) adding a linear trendline to a chart.

On the CD 

The workbook used in this example is available on the companion CD-ROM. The file is named  linear trendline.xlsx.

The second chart also uses the options to display the equation and the R-squared value. In this example, the equation is as follows:

 y = 53.19x + 514.9 

The R-squared value is 0.674

What do these numbers mean? You can describe a straight line with an equation of the form:

 y = mx +b 

For each value of x (the horizontal axis), you can calculate the predicted value of y (the value on the trendline) by using this equation. The variable m represents the slope of the line and b represents the y-intercept. For example, when x is 3 (for March) the predicted value of y is 674.47, calculated with this formula:

 =(53.19*3)+514.9 

The R-squared value, sometimes referred to as the coefficient of determination, ranges in value from 0 to 1. This value indicates how closely the estimated values for the trendline correspond to the actual data. A trendline is most reliable when its R-squared value is at or near 1.

CALCULATING THE SLOPE AND Y-INTERCEPT

As you know, Excel can display the equation for the trendline in a chart. This equation shows the slope (m) and y-intercept (b) of the best-fit trendline. You can calculate the value of the slope and y-intercept yourself, using the LINEST function in a formula.

Figure 17-24 shows 10 data points (x values in column B, y values in column C).

Figure 17-24: Using the LINEST function to calculate slope and y-intercept.

The formula that follows is a multicell array formula that displays its result (the slope and y-intercept) in two cells:

 {=LINEST(C2:C11,B2:B11)} 

To enter this formula, start by selecting two cells (in this example, G2:H2). Then type the formula (without the curly brackets), and press Ctrl+Shift+Enter. Cell G2 displays the slope; cell H2 displays the y-intercept.

CALCULATING PREDICTED VALUES

After you know the values for the slope and y-intercept, you can calculate the predicted y value for each x. Figure 17-25 shows the result. Cell E2 contains the following formula, which is copied down the column:

 =(B2*$G$2)+$H$2 

Figure 17-25: Column E contains formulas that calculate the predicted values for y.

The calculated values in column E represent the values used to plot the linear trendline.

You can also calculate predicted values of y without first computing the slope and y-intercept. You do so with an array formula that uses the TREND function. Select D2:D11, type the following formula (without the curly brackets), and press Ctrl+Shift+Enter:

 {=TREND(C2:C11,B2:B11)} 

LINEAR FORECASTING

When your chart contains a trendline, you can instruct Excel to forecast and plot additional values. You do this on the Trendline Options tab in the Format Trendline dialog box. Just specify the number of periods to forecast. Figure 17-26 shows a chart that forecasts results for two subsequent periods.

Figure 17-26: Using a trendline to forecast values for two additional periods of time.

If you know the values of the slope and y-intercept (see "Calculating the Slope and y-Intercept," earlier in the chapter), you can calculate forecasts for other values of x. For example, to calculate the value of y when x = 11 (November), use the following formula:

 =(53.194*11)+514.93 

You can also forecast values by using the FORECAST function. The following formula, for example, forecasts the value for November (that is, x = 11) using known x and known y values:

 =FORECAST(11,C2:C11,B2:B11) 

CALCULATING R-SQUARED

The accuracy of forecasted values depends on how well the linear trendline fits your actual data. The value of R-squared represents the degree of fit. R-squared values closer to 1 indicate a better fit-and more accurate predictions. In other words, you can interpret R-squared as the proportion of the variance in y attributable to the variance in x.

As described previously, you can instruct Excel to display the R-squared value in the chart. Or you can calculate it directly in your worksheet using the RSQ function. The following formula calculates R-squared for x values in B2:B11 and y values for C2:C11.

 =RSQ(B2:B11,C2:C11) 

Caution 

The value of R-squared calculated by the RSQ function is valid only for a linear trendline.

Working with Nonlinear Trendlines

Besides linear trendlines, an Excel chart can display trendlines of the following types:

• Logarithmic: Used when the rate of change in the data increases or decreases quickly, and then flattens out.

• Power: Used when the data consists of measurements that increase at a specific rate. The data cannot contain zero or negative values.

• Exponential: Used when data values rise or fall at increasingly higher rates. The data cannot contain zero or negative values.

• Polynomial: Used when data fluctuates. You can specify the order of the polynomial (from 2 to 6) depending on the number of fluctuations in the data.

Note 

The Trendline Options tab in the Format Trendline dialog box offers the option of Moving Average, which really isn't a trendline. This option, however, can be useful for smoothing out "noisy" data. The Moving Average option enables you to specify the number of data points to include in each average. For example, if you select 5, Excel averages every group of five data points, and displays the points on a trendline.

Earlier in this chapter, I describe how to calculate the slope and y-intercept for the linear equation that describes a linear trendline. Nonlinear trendlines also have equations, as described in the sections that follow.

On the CD 

The companion CD-ROM contains a workbook with the nonlinear trendline examples described in this section. The file is named  nonlinear trendlines.xlsx.

LOGARITHMIC TRENDLINE

The equation for a logarithmic trendline is as follows:

 y = (c * LN(x)) - b 

Figure 17-27 shows a chart with a logarithmic trendline added. A single array formula in E2:F2 calculates the values for c and b. The formula is

 {=LINEST(C2:C11,LN(B2:B11))} 

Figure 17-27: A chart displaying a logarithmic trendline.

Column C shows the predicted y values for each value of x, using the calculated values for b and c. For example, the formula in cell C2 is

 =($E$2*LN(A2))+$F$2 

As you can see, a logarithmic trendline does not provide a good fit for this data. The R- square value is low, and the trendline does not match the data.

POWER TRENDLINE

The equation for a power trendline looks like this:

 y = c * x^b 

Figure 17-28 shows a chart with a power trendline added. The first element in a two-cell array formula in E2:F2 calculates the values for b. The formula is

 =LINEST(LN(B2:B11),LN(A2:A11),,TRUE) 

Figure 17-28: A chart displaying a power trendline.

The following formula, in cell F3, calculates the value for c:

 =EXP(F2) 

Column C shows the predicted y values for each value of x, using the calculated values for b and c. For example, the formula in cell C2 is as follows:

 =$F$3*(A2^$E$2) 

EXPONENTIAL TRENDLINE

The equation for an exponential trendline looks like this:

 y = c * EXP(b * x) 

Figure 17-29 shows a chart with an exponential trendline added. The first element in a two-cell array formula in F2:G2 calculates the values for b. The formula is

 {=LINEST(LN(B2:B11),A2:A11)} 

Figure 17-29: A chart displaying an exponential trendline.

The following formula, in cell G3, calculates the value for c:

 =EXP(G2) 

Column C shows the predicted y values for each value of x, using the calculated values for b and c. For example, the formula in cell C2 is as follows:

 =$G$3*EXP($F$2*A2) 

Column D uses the GROWTH function in an array formula to generate predicted y values. The array formula, entered in D2:D10, appears like this:

 {=GROWTH(B2:B11,A2:A11)} 

POLYNOMIAL TRENDLINE

When you request a polynomial trendline, you also need to specify the order of the polynomial (ranging from 2 through 6). The equation for a polynomial trendline depends on the order. The following equation, for example, is for a third-order polynomial trendline:

 y = (c3 * x^3) + (c2 * x^2) + (c1 * x^1) + b 

Notice that there are three c coefficients (one for each order).

Figure 17-30 shows a chart with a third-order polynomial trendline added. A four-element array formula entered in F2:I2 calculates the values for each of three c coefficients and the b coefficient. The formula is

 {=LINEST(B2:B11,A2:A11^{1,2,3})} 

Figure 17-30: A chart displaying a polynomial trendline.

Column C shows the predicted y values for each value of x, using the calculated values for b and the three c coefficients. For example, the formula in cell C2 is

 =($F$2*A2^3)+($G$2*A2^2)+($H$2*A2)+$I$2