In This Chapter
When most people think of Excel, they think of analyzing rows and columns of numbers. As you probably know already, though, Excel is no slouch when it comes to presenting data visually in the form of a chart. In fact, it’s a safe bet that Excel is the most commonly used software for creating charts.
After you create a chart, you have almost complete control over nearly every aspect of each chart. This chapter, which assumes that you’re familiar with Excel’s charting features, demonstrates some useful charting techniques—most of which involve formulas.
You create charts from numbers that appear in a worksheet. You can enter these numbers directly, or you can derive them as the result of formulas. Normally, the data used by a chart resides in a single worksheet, within one file, but that’s not a strict requirement. A single chart can use data from any number of worksheets or even from different workbooks.
A chart consists of one or more data series, and each data series appears as a line, column, bar, and so on. Each series in a chart has a SERIES formula. When you select a data series in a chart, Excel highlights the worksheet data with an outline, and its SERIES formula appears in the Formula bar (see Figure 17.1).
A SERIES formula has the following syntax:
=SERIES(series_name, category_labels, values, order, sizes)
The arguments that you can use in the SERIES formula include
Range references in a SERIES formula are always absolute, and (with one exception) they always include the sheet name. Here’s an example of a SERIES formula that doesn’t use category labels:
=SERIES(Sheet1!$B$1,,Sheet1!$B$2:$B$7,1)
A range reference can consist of a noncontiguous range. If so, each range is separated by a comma, and the argument is enclosed in parentheses. In the following SERIES formula, the values range consists of B2:B3 and B5:B7:
=SERIES(,,(Sheet1!$B$2:$B$3,Sheet1!$B$5:$B$7),1)
Although a SERIES formula can refer to data in other worksheets, all the data for a series must reside on a single sheet. The following SERIES formula, for example, is not valid because the data series references two different worksheets:
=SERIES(,,(Sheet1!$B$2,Sheet2!$B$2),1)
You can substitute range names for the range references in a SERIES formula. When you do so, Excel changes the reference in the SERIES formula to include the workbook name. For example, the SERIES formula shown here uses a range named MyData (located in a workbook named budget.xlsx). Excel added the workbook name and exclamation point.
=SERIES(Sheet1!$B$1,,budget.xlsx!MyData,1)
Using names in a SERIES formula provides a significant advantage: if you change the range reference for the name, the chart automatically displays the new data. In the preceding SERIES formula, for example, assume that the range named MyData refers to A1:A20. The chart displays the 20 values in that range. You can then use the Name Manager to redefine MyData as a different range—say, A1:A30. The chart then displays the 30 data points defined by MyData. (No chart editing is necessary.)
As noted previously, a SERIES formula cannot use worksheet functions. You can, however, create named formulas (which use functions) and use these named formulas in your SERIES formula. As you see later in this chapter, this technique enables you to perform some useful charting tricks.
Normally, an Excel chart uses data stored in a range. If you change the data in the range, the chart updates automatically. In some cases, you may want to “unlink” the chart from its data ranges and produce a static chart—a chart that never changes. For example, if you plot data generated by various what-if scenarios, you may want to save a chart that represents some baseline so you can compare it with other scenarios.
There are two ways to create a static chart:
=SERIES(,{"Jan","Feb","Mar"},{1869,2085,2451},1)
You can add cell links to various elements of a chart. Adding cell links can make your charts more dynamic. You can set dynamic links for chart titles, data labels, and axis labels. In addition, you can insert a text box that links to a cell.
The chart title is normally not linked to a cell. In other words, it contains static text that changes only when you edit the title manually. You can, however, create a link so that a title refers to a worksheet cell.
Here’s how to create a linked title:
The result is a formula that contains the sheet reference and the cell reference as an absolute reference (for example, =Sheet3!$A$1). Figure 17.3 shows a chart in which the chart title is linked to cell A1 on Sheet3.
The axis titles are optional and are used to describe the data for an axis. The process for adding a link to an axis title is identical to that described in the previous section for a chart title.
You can also add a linked text box to a chart. The process is a bit tricky, however. Follow these steps exactly:
You can apply any type of formatting you like to the text box.
A chart can display a “live” picture of a range of cells. When you change a cell in the linked range, the change appears in the linked picture. Again, the process isn’t exactly intuitive. Start by creating a chart. Then do this:
Activate a cell (not the chart) and choose Home ➜ Clipboard ➜ Paste ➜ Linked Picture (I).
Excel inserts the linked picture of the range on the worksheet’s draw layer.
Activate the chart and press Ctrl+V.
The linked picture is cut from the worksheet and pasted into the chart. However, the link no longer functions.
This section contains a variety of chart examples that you may find useful or informative. At the very least, they may inspire you to create charts that are relevant to your work.
Effective charts don’t always have to be complicated. This section presents some charts that display a single data point.
Figure 17.4 shows five charts, each of which uses one data point. These are minimalistic charts. The only chart elements are the single data point series, the data label for that data point, and the chart title (displayed on the left). The single column fills the entire width of the plot area.
One of the charts is grouped with a shape object that contains text.
Figure 17.5 shows another single data point chart. The chart, which shows the value in cell B21, is actually a line chart with markers. A shape object was copied and pasted to replace the normal line marker. The chart contains a second series, which was added for the secondary axis.
Figure 17.6 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).
One slice of the pie—the slice at the bottom—always consists of 50 percent. The pie has been rotated so that the 50 percent slice is at the bottom. Then that slice was hidden by specifying No Fill and No Border for the data point.
The other two slices are apportioned based on the value in cell B1. The formula in cell A4 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 (Percent Completed) was moved below the half-pie. A linked text box displays the percent completed value in cell B1.
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.7 shows such a chart. (It’s more impressive when you see it in color.) The data used to create the chart is in range A2:F14.
This chart actually displays four data series, but some data is missing for each series. The data for the chart is entered into column B. Formulas in columns C:F determine which series the number belongs to by referencing the cut-off values 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 section of the Format Data Series task pane to adjust this setting. That section has another setting, Gap Width. In this case, the Gap Width essentially controls the width of the columns.
With a bit of creativity, you can create charts that you may have considered impossible. For example, Figure 17.8 shows a chart sometimes referred to as a comparative histogram chart. Such charts often display population data.
Here’s how to create the chart:
Enter the data in A1:C8, as shown in Figure 17.8.
Notice that the values for females are entered as negative values.
Expand the Number section and specify the following custom number format:
0%;0%;0%
Select the vertical axis and display the Format Axis task pane.
In the Tick Marks section, set all tick marks to None, and in the Labels section, set the Label Position option to Low.
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.
Figure 17.9 shows a Gantt chart that depicts the schedule for a project that 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.
Column A contains the task name, column B contains the corresponding start date, and column C contains the duration of the task, in days. Note that cell A1 does not have a descriptive label. Leaving that cell empty ensures that Excel does not use columns A and B as the category axis.
Follow these steps to create this chart:
In the Series Options section, set the Series Overlap to 100%. In the Fill section, specify No Fill. In the Border section, specify No Line.
These steps effectively hide the data series.
In the Axis Options, adjust the Minimum and Maximum settings to accommodate the dates that you want to display on the axis.
You can enter a date value, and Excel converts it to a date serial number. In the example, the Minimum is 5/2/2016, and the Maximum is 7/24/2016.
A box plot (sometimes known as a quartile plot) is often used to summarize data. Figure 17.10 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 lists the formulas in column G (which were copied to the three columns to the right).
Table 17.1 Formulas Used to Create a Box Plot
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:
The chart shown here does not have a legend. It’s been replaced with a graphic that explains how to read the chart. The legend for this chart displays the series in the order in which they are plotted—which is not the optimal order and can be very confusing. Unfortunately, you can’t change the plot order because the order is important. (The up/down bars use the first and last series.) Creating a descriptive graphic seems like a good alternative to a confusing legend.
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 10th data point. One way to accomplish this is to use filtering in conjunction with a formula. Figure 17.11 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.
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 list in cell B3 to specify a filter that shows only the rows that contain a 0 in column B.
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.
Figure 17.12 shows a line chart that has its maximum and minimum values identified with a circle and a square, respectively. These identifiers are the result of using two additional series in the chart. You can achieve this effect manually, by adding two shapes, but using the additional series makes it fully automated.
Start by creating a line chart using the data in range A1:B13:
Enter the following formula in cell C2:
=IF(B2=MAX($B$2:$B$13),B2,NA())
Enter this formula in cell D2:
=IF(B2=MIN($B$2:$B$13),B2,NA())
Figure 17.13 shows a scatter chart, set up to display a timeline of events. The chart uses the data in columns A and B, and the series uses vertical error bars to connect each marker to the timeline (the horizontal value axis). The text consists of data labels from column C. The vertical value axis for the chart is hidden, but it is set to display Values In Reverse Order so that the earliest events display higher in the vertical dimension.
This type of chart is limited to relatively small amounts of text. Otherwise, the data labels wrap, and the text may be obscured.
The examples in this section demonstrate how to plot mathematical functions that use one variable (a 2D line chart) and two variables (a 3D surface chart). Some of the examples make use of Excel’s Data Table feature, which enables you to evaluate a formula with varying input values.
An XY chart (also known as a scatter chart) is useful for plotting various mathematical and trigonometric functions. For example, Figure 17.14 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.
The function is expressed like this:
y = SIN(x)
The corresponding formula in cell B2 (which is copied to the cells below) is
=SIN(A2)
Figure 17.15 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:
Enter a formula in cell B7. The formula should contain at least one x variable.
In the figure, the formula in cell B7 is
=SIN(x^3)*COS(x^2)
The formula in cell B7 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.
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.16 shows a surface chart that plots the value of z for 25 x values ranging from –1 to 1 (in 0.1 increments) and for 25 y values ranging from –2 to 2 (in 0.2 increments).
Figure 17.17 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 × 25 data table in range M7:AL32 (not shown in the figure). To use this application
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
=SIN(SQRT(x^2 + y^2))
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.
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.18 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.
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(B2)
And use this formula in cell E2:
=COS(B2)
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.19 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). A second series plots the origin as a single data point. In the figure, the circle’s origin is at 2,3, and it has a radius of 7.25.
The formula in cell D2 is
=(SIN(RADIANS(A2))*radius)+x_origin
The formula in cell E2 is
=(COS(RADIANS(A2))*radius)+y_origin
Figure 17.20 shows an XY chart formatted to look like a clock. It not only looks like a clock but 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.
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.
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
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 formula to recalculate.
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.21. 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.
When you examine the workbook, keep the following points in mind:
The UpdateClock procedure inserts the following formula into the cell named DigitalClock:
=NOW()
Figure 17.22 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.
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 for 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.
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 data with trendlines. A single series can have more than one trendline.
To add a trendline in Excel 2016, select the chart series, click the Chart Elements icon (to the right of the chart), and select Trendline. The default trendline is Linear, but you can expand the selection choice and choose a different type. For additional options (and more control over the trendline), choose More Options to display the Format Trendline task pane (see Figure 17.23).
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.
On the Trendline Options tab, you can specify a name to appear in the legend and the number of periods that you want to forecast (if any). Additional options there 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.
Figure 17.24 shows two charts. The first chart depicts a data series without a trendline. As you can see, the data seems to be “linear” over time. The next chart is the same chart but with a linear trendline that shows the trend in the data.
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.194x + 514.93
The R-squared value is 0.6748.
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 closer to 1.
This section describes how to use the LINEST function to calculate the slope (m) and y-intercept (b) of the best-fit linear trendline.
Figure 17.25 shows ten data points (x values in column B, actual y values in column C).
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. Note that these are the same values displayed in the chart for the linear trendline.
After you know the values for the slope and y-intercept, you can calculate the predicted y value for each x. Figure 17.26 shows the result. Cell E2 contains the following formula, which is copied down the column:
=(B2*$G$2)+$H$2
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)}
When your chart contains a trendline, you can instruct Excel to extend the trendline to forecast additional values. You do this on the Trendline Options section of the Format Trendline task pane. (Read earlier in this section to see how to open this task pane.) Just specify the number of periods to forecast. Figure 17.27 shows a chart with a trendline that’s extended to forecast two subsequent periods.
If you know the values of the slope and y-intercept (see the “Calculating the slope and y-intercept” section, 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)
The preceding example uses the FORECAST function to predict future months’ values. Excel 2016 introduces five new forecasting functions to give you more algorithm options. The new functions use advanced machine learning algorithms, which are beyond the scope of this book. But we include a brief summary and the syntax of the new functions here:
FORECAST.LINEAR(x, known_y's, known_x's)
FORECAST.LINEAR is the direct replacement for the deprecated FORECAST function. It uses linear regression to predict a y value for the given x value:
FORECAST.ETS(target_date, values, timeline, seasonality, data_completion, aggregation)
FORECAST.ETS uses the AAA version of an algorithm called Exponential Smoothing, or ETS. The first three arguments—target_date, values, and timeline—are similar to the arguments for FORECAST.LINEAR. Target_date can be any number, not just a date, but it must be larger than the largest date in timeline. That is, it only predicts future values.
The timeline argument is a series of dates that have a consistent step. That is, each element of the series must be one day apart, one month apart, one year apart, or any other value as long as it’s consistent. There are a few exceptions to this rule depending on the values you provide for the optional arguments.
The remaining arguments are optional.
FORECAST.ETS.SEASONALITY(target_date, values, timeline, seasonality, data_completion)
FORECAST.ETS.SEASONALITY uses many of the same arguments as FORECAST.ETS. If you specify 1 as the seasonality argument of FORECAST.ETS, the algorithm determines the seasonality. This function returns what the algorithm calculates for seasonality. In Figure 17.28, cell G12 shows that two periods is the seasonality computed from the data.
FORECAST.ETS.CONFINT(target_date, values, timeline, confidence_level, seasonality, data_completion, aggregation)
FORECAST.ETS.CONFINT returns the confidence interval for the forecasted data using ETS. It has the same arguments as FORECAST.ETS except for confidence_level. Confidence_level is a number between 0 and 1. (The default is 95%.) Cell H12 in Figure 17.28 uses a confidence_level of .9 (90%) and calculates that there is 90% confidence that the actual future values will be +/-131 of the predicted values in column F:
FORECAST.ETS.STAT(target_date, values, timeline, statistic_type, seasonality, data_completion, aggregation)
FORECAST.ETS.STAT returns one of several statistical measures from the data provided. Again, the arguments are mostly the same as FORECAST.ETS. The exception is statistic_type, where you indicate which statistic you want the formula to return. The list of statistics can be found in Excel’s help. Figure 17.28 uses the Step size detected statistic in cell I12 to report that it determined that the values in column B incremented by 1.
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)
Besides linear trendlines, an Excel chart can display trendlines of the following types:
Figure 17.29 shows charts that depict each of the trendline options.
Earlier in this chapter, you learned how to calculate the slope and y-intercept for the linear equation that describes a linear trendline. Nonlinear trendlines also have equations, and they are a bit more complex.
This section contains a concise summary of trendline equation. These equations assume that your sheet has two named ranges: x and y.
Equation: y = m * x + b m: =SLOPE(y,x) b: =INTERCEPT(y,x)
Equation: y = (c * LN(x)) + b c: =INDEX(LINEST(y,LN(x)),1) b: =INDEX(LINEST(y,LN(x)),1,2)
Equation: y=c*x^b c: =EXP(INDEX(LINEST(LN(y),LN(x),,),1,2)) b: =INDEX(LINEST(LN(y),LN(x),,),1)
Equation: y = c *e ^(b * x) c: =EXP(INDEX(LINEST(LN(y),x),1,2)) b: =INDEX(LINEST(LN(y),x),1)
Equation: y = (c2 * x^2) + (c1 * x ^1) + b c2: =INDEX(LINEST(y,x^{1,2}),1) c1: =INDEX(LINEST(y,x^{1,2}),1,2) b = INDEX(LINEST(y,x^{1,2}),1,3)
Equation: y = (c3 * x^3) + (c2 * x^2) + (c1 * x^1) + b c3: =INDEX(LINEST(y,x^{1,2,3}),1) c2: =INDEX(LINEST(y,x^{1,2,3}),1,2) c1: =INDEX(LINEST(y,x^{1,2,3}),1,3) b: =INDEX(LINEST(y,x^{1,2,3}),1,4)
An interactive chart allows the user to easily change various parameters that affect what’s displayed in the chart. Often, VBA macros are used to make charts interactive. The examples in this section use no macros and demonstrate what can be done using only the tools built into Excel.
Figure 17.30 shows a chart that displays data as specified by a drop-down list in cell F2. Cell F2 uses data validation, which allows the user to select a month from a list. The chart uses the data in F1:I2, but the values depend on the selected month. The formulas in range G2:I2 retrieve the values from columns B:D that correspond to the selected month.
The formula in cell G2, which was copied to the two cells to the right, is
=INDEX(B2:B13,MATCH($F$2,$A$2:$A$13,0))
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. Figure 17.31 shows a worksheet set up so the user can specify the number of data points.
The workbook uses three names. N is the name for cell E3, which holds the number of data points to plot.
MonthRange is a dynamic named formula, defined as
=OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)–Sheet1!N,0,Sheet1!N,1)
SalesRange is a dynamic named formula, defined as
=OFFSET(Sheet1!$B$1,COUNTA(Sheet1!$B:$B)–Sheet1!N,0,Sheet1!N,1)
The chart’s SERIES formula uses the two named formulas:
=SERIES(,Sheet1!MonthRange,Sheet1!SalesRange,1)
This example uses dynamic formulas to allow the user to display a chart with a specified number of data points, beginning with a selected starting date. Figure 17.32 shows a worksheet with 365 rows of daily sales data.
The start date is indicated in cell E2 (named StartDay), and the number of data points is specified in cell G2 (named NumDays). Both cells use data validation to display a drop-down list of options.
What makes this work is two named formulas. The formula named Date is
=OFFSET(Sheet1!$B$2,MATCH(StartDay,Sheet1!$B:$B,1)–2,0,NumDays,1)
The formula named Sales is
=OFFSET(Sheet1!$B$2,MATCH(StartDay,Sheet1!$B:$B,1)–2,1,NumDays,1)
These are both dynamic names that return a range that depends on the value of StartDay and NumDays. The two names are used in the chart’s SERIES formula. (The filename qualifier was removed for simplicity.)
=SERIES(,Date,Sales,1)
The example in this section uses the population pyramid technique described earlier in this chapter. (See “Creating a comparative histogram.”) Here, the user can choose two years to view population by age groups side by side.
Figure 17.33 shows the age distribution for 1950 and 2013.
The years are entered in cells C2 and G2. The years can be entered manually or specified by using a scrollbar linked to a cell. Formulas below the chart retrieve data from a table that has population statistics (and projections) from 1950 through 2100.
For each year, the chart also displays the total population and the percentage of people age 65 or older. That information is displayed by using text boxes linked to cells.
The example shown in Figure 17.34 is a useful application that allows the user to choose two U.S. cities (from a list of 284 cities) and view a chart that compares the cities by month in any of the following categories: average precipitation, average temperature, percent sunshine, and average wind speed.
The cities are chosen from a drop-down list using Excel’s data validation feature, and the data option is selected using four OptionButton controls, which are linked to a cell. All the pieces are connected using a few formulas.
The key to this application is that the chart uses data in a specific range. The data in this range is retrieved from the appropriate data table by using formulas that use the VLOOKUP function.
The formula in cell A23, which looks up data based on the contents of City1, is
=VLOOKUP(City1,INDIRECT(DataTable),COLUMN(),FALSE)
The formula in cell A24 is the same except that it looks up data based on the contents of City2:
=VLOOKUP(City2,INDIRECT(DataTable),COLUMN(),FALSE)
These formulas were entered and then copied across to the next 12 columns (see Figure 17.34).
Row 25 contains formulas that calculate the difference between the two cities for each month. Conditional formatting was used to apply a different color background for the largest difference and the smallest difference.
The label above the month names is generated by a formula that refers to the DataTable cell and constructs a descriptive title. The formula is
="Average " & LEFT(DataTable,LEN(DataTable)–4)
After you’ve completed the previous tasks, the final step—creating the actual chart—is a breeze. The line chart has two data series and uses the data in A22:M24. The chart title is linked to cell B21. The data in A23:M24 changes, of course, whenever an OptionButton control is selected or a new city is selected from either of the Data Validation lists.
52.14.234.213