This Excel tutorial takes you from adding a simple trend line on an Excel chart to creating dynamic trend lines the user can control with check boxes and drop-down lists.
Humans have been trying to forecast the future at least as far back as early Neolithic times, 14,000 – 10,000 B.C.E, and probably even before that. Of course that was all guess work by shamans who squatted by the fire and threw rune stones to see patterns that foretold the future. We’re much more sophisticated now. Now we sit in ergonomic Aeron chairs, run Excel on our quad-core computers, and use TREND functions to create forecasts that are accurate and true. (I can’t talk right now, my tongue is in cheek.)
In this Excel dashboard tutorial you’ll learn how to add check boxes and drop-down lists to show or hide the trend line and switch between different types of trends. The figure below shows the sample file you can download in the last article.
This tutorial will show you how to create trend lines in Excel charts and dashboards so you can,
- Use a chart command to embed different types of trend lines on a chart
- Calculate data points on trend lines
- Extend trends into the future
- Dynamically show or hide trend lines with a custom check box
- Dynamically select types of trend lines from a custom drop-down list
With Excel you can calculate different regressions that show the best-fit line to a set of data and then plot that best fit as a trend lines on your chart. Regressions calculate different types of lines, straight or curved, to best fit the data with the least amount of error. If you understand the reliability and volatility of your data you can even use a trend line from these regressions to make short forecasts into the future.
This Excel tutorial is divided into multiple short articles. The next article has a link to download the first worksheet. The final tutorial has a downloadable Excel file containing all Excel charts with dynamic trends. Although this example uses Excel 2010, you can do everything in the tutorial with Excel versions at least as early as Excel 2003.