- Choosing a Chart Type
- Understanding a Date-Based Axis Versus a Category-Based Axis
- Using a Chart to Communicate Effectively
- 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
- Using a Scatter Plot to Show a Trend
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. Sometimes, however, you might want to allow Excel to make a prediction based on past results. For just such situations, Excel offers a trendline feature. Excel can draw a straight line that fits the existing data points. You can either ask Excel to extrapolate the trendline into the future, or, 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 or trendline.
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.35, column A contains the days of the month. Column B contains 125 for each data point; Excel therefore 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 would write an equal number of pages six days per week. Column D is labeled Actual; this is where I record the daily progress toward the goal.
)
Figure 3.35 In the top chart, the actual line is running behind the trendline, but it seems close.
The chart is created as a line chart. Gridlines are removed. The legend is removed. The trendline is formatted as a lighter gray. The actual line is formatted as a thick line. The top chart in Figure 3.35 shows the chart before the trendline is complete. You can see that the thick line is not quite above the progress line.
To add a trendline, you follow these steps:
- Right-click the series line for the Actual column. Choose Add Trendline.
- The Format Trendline dialog offers to add exponential, linear, logarithmic, polynomial, power, or moving average trendlines. Choose a linear trendline.
- In the Trendline Name section, either leave the name as Linear (Actual) or enter a custom name, such as Forecast.
- For the settings where you can forecast forward or backward a certain number of periods, because this chart already has data points for the entire month, leave both of those settings at 0. 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 ribbon, choose Shape Outline, Dashes and then select the fourth dash option. Also choose Shape Outline, Weight, ¾ point.
The trendline is shown in the bottom of Figure 3.35. 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.