- Choosing a Chart Type
- Understanding Date-Based Axis Versus Category-Based Axis in Trend Charts
- Communicate Effectively with Charts
- Adding an Automatic Trendline to a Chart
- Showing a Trend of Monthly Sales and Year-to-Date Sales
- Understanding the Shortcomings of Stacked Column Charts
- Shortcomings of Showing Many Trends on a Single Chart
- Next Steps
This chapter is from the book
This chapter is from the book
This chapter is from the book
Adding an Automatic Trendline to a Chart
In the previous example, an analyst had created a forecast for the next two quarters. However, sometimes you might want to allow Excel to make a prediction based on past results. In these situations, Excel offers a trendline feature in which Excel draws a straight line that fits the existing data points. You can ask Excel to extrapolate the trendline into the future. If your data series contains blank points that represent the future, Excel can automatically add the trendline. I regularly use these charts to track my progress toward a goal.
The easiest way to add a trendline is to build a data series that includes all the days that the project is scheduled to run. In Figure 3.31, column A contains the days of the month and column B contains 125 for each data point. Therefore, Excel draws a straight line across the chart, showing the goal at the end of the project. Column C shows the writing progress I should make each day. In this particular month, I am assuming that I will write an equal number of pages six days per week. Column D, which is labeled Actual, is where I record the daily progress toward the goal.
)
Figure 3.31. In the top chart, the actual line is running behind the target line, but it seems close.
The chart is created as a line chart with the gridlines and legend removed. The trendline is formatted as a lighter gray. The actual line is formatted as a thick line. The top chart in Figure 3.31 shows the chart before the trendline is complete. Notice that the thick line is not quite above the progress line.
To add a trendline, follow these steps:
- Right-click the series line for the Actual column. Select Add Trendline. A default linear trendline is added to the chart. The Format Trendline task pane appears. Your choices in the task pane are for trendlines that use Exponential, Linear, Logarithmic, Polynomial, Power, or Moving Average. Leave the choice at Linear for this example.
- In the Trendline Name section, either leave the name as Linear (Actual) or enter a custom name such as Forecast.
- When forecasting forward or backward for a certain number of periods, leave both of those settings at 0 because this chart already has data points for the entire month. There are also settings where Excel shows the regression equation on the chart. Add this if you desire.
- Right-click the trendline to select it. On the Format tab, select Shape Outline, Dashes and then select the fourth dash option. Also, select Shape Outline, Weight, 3/4 point.
The trendline is shown at the bottom of Figure 3.31. In this particular case, the trendline extrapolates that if I continue writing at the normal pace, I will miss the deadline by 15 pages or so.
Excel’s trendline is not an intelligent forecasting system. It merely fits past points to a straight line and extrapolates that data. It works great as a motivational tool. For example, the current example shows that it would take a few days of above-average production before the trendline would project that the goal would be met.