Today we are going to show you how to add a trendline in Excel. Microsoft Excel is full of functions, formulas, and features that help users to manage data in a much simpler way.
With a trendline, we will be able to create a useful data analysis tool that gives us a lot of information when we look at it. This is very useful for sales or growth in general. It is also great to compare the performance of different years.
How to add a trendline in Excel?
- First, we will select the data and go to the Insert menu and in the Charts group, we will select the desired chart type for this purpose. Let’s say it is a scatter chart.
- Once the chart is integrated into the spreadsheet, click on the + sign located in the upper right corner, and in the options, we will activate the “Trendline” checkbox.
Trendline types in Excel
We can click on “Trendline” to list the different line options to use. To display all the options we can select any of the chart points, right-click and select “Add trendline” in order to display the Excel side menu associated with the trendline.
Linear
It is the default setting in Excel and is formed by a line that increases or decreases based on the data provided in the spreadsheet.
Exponential
It is ideal when the data values increase based on the x values, there the exponential trend line offers a much more orderly view of the data.
Logarithmic
It is a special logarithmic trendline for viewing the data as the rate of change decreases with increasing x-values.
Polynomial
It is ideal where the data has an upward and downward trend based on wave patterns and its design allows you to see the number of curves in the graph either up or down. Note that the “Degree” field is available in which we can define the curve.
Potential
Applies where data tends to increase frequently.
Moving average
It is an ideal trendline in cases where the data is highly variable, as it takes an average since this line will take the average of every two points in the given graph.
How to add a trendline and R squared value in the chart in Excel?
At the bottom of the trendline options, we find a checkbox called “Show R-squared value on chart” which will display the R-value. This is a measure that indicates the distance of each point on the trend chart, so the closer the R-squared value is to each other, the better the lines will fit the trend data. By checking this box we see the line with the R-squared value in the selected chart.
How to extrapolate a graph by trendline in Excel?
Extrapolation is synonymous with the forecast in the data, there we have the options “In the future” and “In the past” and they are measured in periods, so we can define how many periods in the future to apply and the trend line will do the rest.
How to add different trendlines in a chart in Excel?
For design or management reasons, it is possible to add multiple trendlines with a range of data, to do this we must select any of the points on the chart, right-click and select “Add trendline.” After that just select the type of line on the right side.
How to add a trendline in Excel using the keyboard?
We show you another very simple way to be able to add a chart and trendline in Excel 2016.
Create the Excel table that you are going to use as a base to make the chart, and to be able to see the trendline later.
Once the table is made, select the data in the Excel sheet, the data you want to represent in the chart. We advise you when selecting the data, also select the headers.
Press the F11 key. This generates the chart automatically. You will see that the chart has been created on a new sheet, within the document you are working on.
To remove the trendline, on the new chart you have created by pressing the F11 key on your keyboard, hover the mouse pointer over a value inside the chart.
Press the right mouse button, you will see that in the menu that appears, one of the options displayed is “Add trendline.”
