Trendlines show a general increase or decrease in a chart. These lines represent a trend over a period of time so that even a layman can analyse the result and can make a conclusion. Trendlines are important if you are giving a presentation or are generally expressing your opinion in some matter.
Microsoft Excel provides you with the option to add trendlines in the spreadsheet. In this article, we will show you how you can add trendlines in Excel, including in multiple data sets, and formatting them.
Types of Trendlines options in Excel
Excel has several Trendlines options that you can choose from. Some of the options are given below.
- Linear: A linear trendline uses the following equation: y=mx+b, where m is the slope and b is the intercept.
- Exponential: This trendline shows the increase or decrease in data rates exponentially. So, this trendline tends to have more curves than the linear one. The formula for this trendline is y=ce^bx, where c as well as b, are constants and e is the base of the algorithm. Remember, you cannot select the exponential trendlines if the data has negative values.
- Logarithmic: This is the best trendline to use if the data rises and falls at a rapid pace before levelling out. This trendline can use both negative as well as positive values.
- Polynomial: Usually, this trendline shows the results over a large period of time and thus is useful if you have a large chunk of data to represent.’
- Moving Average: If you want to smoothen the fluctuation in your data set, then this is the trendline for you. This trendline uses the average of the data points that are set by the users and thus flatten the fluctuations.
Also read: What is a CSV file and how can you open it?
How to add trendlines in Excel?
To add a trendline in Excel, you will have to first represent the values in a graph or a chart format (refer the screenshot above). To insert a graph you will have to first enter the data in a tabular format. After that click on the Insert option from the toolbar at the top and then select the format of the chart as per your requirement.
Step 1: Once the chart has been made, click on it and then on the plus button at the top left of the chart.Step 2: From the drop-down menu, click on the Trendline option at the bottom.Step 3: After that, click on the data category which you wish to display on the trendline. You will see that a trendline will appear on the chart. Also read: How to add text box in Google Docs?
How to add trendlines to multiple data sets?
If you are using a chart of many data sets, you might want to include multiple trendlines in your chart. Using the process above, you can add only one trendline. Here are the steps to add multiple trendlines to your chart.
Step 1: Click on a specific bar of the chart that you want to insert the trendline. Below, I have clicked on the orange bar. Step 2: Now, click on Add Trendline and then select the trendline type. In the same way, you can add trendlines to as many data sets as you want.
How to format a trendline?
Step 1: Go to the plus sign at the top right of the chart and then hover the mouse cursor over the Trendline option from the bottom.Step 2: Go the arrow and then from the menu, click on More options.Step 3: Here, you will see three headings with multiple options inside those headings. The three main headings are: Trendline options, Effects and Fill and Line. Choose the appropriate heading and then the sub-heading to format your trendline. For example, if you want to change the colour and texture of the trendline, you can select the Fill and Line option. Or, if you want to change the trendline type, you can go to Trendline options heading. That’s it. Now that you have added trendlines, you can add a bit more clarity to your presentation.