Use Excel to Determine if Your Data Shows a Relevant Trend

Problem:

You have a workbook that contains a full twelve months of production. Even after graphing the data, it still isn’t clear if production went up or down last year.

Solution:

Insert a trendline. With a trendline, you can see if your sales went up, and if so, by how much. Charts reveal a great deal about your data in a dynamic and accessible format. Sometimes, there are swings in the data that make it difficult to discern if there is an important trend in the information. Trendlines perform calculations behind the scenes and provide an indicator of the direction your data is moving to help make the big picture clear.

Including a trendline in your charts may help illustrate both the size and direction of changes in your data. They are also useful in forecasting future or past values based on available data.

To add a Trendline to a chart:

1. Right-click on any data bar in the chart and select Add Trendline from the menu that appears [A] to open the Format Trendline task pane [B].
2. For a basic trendline indicating the general direction of your data, click the radio button to the left of Linear [C].
3. Click the Display R-squared value on chart checkbox [D].
4. Click the Close button [E] to close the task pane.
5. Review your chart with the trendline in place [F].

Note the R-squared value on the trendline:

In the simplest terms, this describes the trendline’s accuracy. The closer a trend is to “1,” or 100%, the more accurate it is. Most trends should not be considered significant until they are at least 0.5, or 50%.

Try this: Click through the various trendline options in the Format Trendline task pane to preview how each option changes the trendline’s shape as it changes how your data is analyzed.

Pro Tip!

Add a trendline from the Chart Elements button by checking the Trendline checkbox on the Chart Elements pane [G]. Click the triangle to choose from a list of types of trendlines, or to open the Format Trendline task pane by clicking More Options…