What Is Trend Line In Google Sheets?
The Trend Line in Google Sheets are diagonal lines drawn through an existing chart to highlighting the pattern or the trend of the given dataset.
Since the Google Sheets Trend Line is not an inbuilt chart, we can plot it on some inbuilt charts that support it, such as Line Chart, Column Chart, etc.
For example, we will create Trend lines for the data given below.
Use this Trend Line In Google Sheets Template to follow along with the examples in this article.
Download Excel Template
Select the cell range A1:B4, generate a Column Chart and insert the Trend Line.
The Trend Line generated using the Column Chart, as shown above.
Key Takeaways
- The Trend Line in Google Sheets is a dotted line plotted on an existing graph/chart to visualize the data points and the variation of data.
- We can plot different types of Trend Lines such as Linear, Exponential, Linear Forecast, etc., and customize it as required.
- It is not an inbuilt in Google Sheets but can be added using the “Chart editor – Customize – Series” path, on a generated chart.
- The trend line meaning refers to a diagonal line drawn through a price chart to show the trend. A minimum of two swing lows or swing highs are necessary for individuals to draw it in either direction.
- There are different trend line types that one can use in different situations. For instance, individuals can use the exponential one when data values fluctuate at increasingly higher rates.
Types
The different types of Trend Line in Google Sheets are as follows:
- Linear: It is used for simple linear datasets where the data points in a line increase or decrease at a steady rate.
- Exponential: It is used when the data values surge or drop at increasingly higher rates. Except, it only works on positive values and cannot be used if the dataset consists of 0 or negative values.
- Polynomial: Itis a curved line used when data fluctuates. For instance, individuals can use it to analyze profits and losses over large data sets.
- Logarithmic: Also known as the best-fit curved line, it is beneficial for traders when the rate of price fluctuation surges or drops quickly and before levelling out.
- Power Series: It is used if the data increases at a specific rate.
- Moving Average: It utilizes a particular number of data points, averages them and utilizes the average value as a point in the pattern. Therefore, it is used when the data has large variations. and help neutralize variations to give a better idea of the underlying trend.
How To Create A Trend Line In Google Sheets?
MS Excel and Google Sheets doesn’t have an inbuilt Trend Line chart. However, we canplot the trend linesusing some of the chart types, such as Line Chart, Column Chart, Area Chart, Scatter Chart, etc.
The steps to Create A Trend Line in Google Sheets are as follows:
Step 1: Choose the entire dataset or the required cell range – select the “Insert” tab – choose the “Chart” option.
Step 2: A default chart, i.e., the “Column chart”, generates. The “Chart editor” window opens on the right-side.
Step 3: To select a different “Chart type”, select the “Setup” menu. Click the “Chart type” drop-down, as shown below.
Step 4: In the Chart Editor window, select the “Customize” tab – click the “Series” option drop-down – check/tick the “Trendline” checkbox, as shown below.
Step 5: We have the different types of trendlines under the “Trendline” option. We can select a type and customize it as per our requirements.
Examples
We will understand the trendlines types using the Trend Line in Google Sheets examples plotted ondifferent inbuilt chart options.
Example #1 – Linear Trendline
The below table that shows a person’s monthly income and savings data. We will create a Column Chart and then plot the Linear Trendline in Google Sheets.
The steps to create a Column chart In Google Sheets for the given data are,
Step 1: Choose the cell range A1:C7 – select the “Insert” tab – choose the “Chart” option and the “Chart editor” window opens on the right-side, as shown below.
The Column Chart generates, with gaps in-between the compared categories, as shown below.
Step 2: First, click the “Customize” option in the “Chart editor” window, then, click the “Chart style” option right arrow and check/tick the “3D” option, as shown below.
Now, the chart appears clustered, as a 3D Chart, i.e., the gaps reduce, as shown below.
Step 3: Let us update the titles, chart styles, range values within the chart, etc. Then, we get the following final 3D Column Chart.
Step 4: Next, to plot the Linear Trendlines, go to the Chart Editor window – select the “Customize” tab – click the “Series” option drop-down – check/tick the “Trendline” checkbox, as shown below.
Step 5: Notice that the moment we check the “Trendlines” option, we get the options as seen in the below image and, by default, the Linear option is selected. If not too, select the “Linear” option from the “Type” drop-down, as shown below. We get the following 3D Column Chart with the Linear Trendline in Google Sheets.
Output Observation: The Column Chart graphically represents the person’s income and savings and the trendlines are also plotted. The blue trendline represents the Total income pattern and the red trendline represents the total savings pattern.
Example #2 – Exponential Trendline
We will plot an Exponential Trendline in Google Sheets on an Area Chart for the given Quantity and Price of fruits.
The steps to create an Area Chart in Google Sheets for the given data are,
Step 1: Choose the cell range A1:C5 – select the “Insert” tab – choose the “Chart” option and the “Chart editor” window opens on the right-side, as shown below.
The following Area Chart generates.
Step 2: Let us update the titles, chart styles, range values within the chart, etc. Then, we get the following final Area Chart.
Step 3: Next, to plot the Exponential Trendlines, go to the Chart Editor window – select the “Customize” tab – click the “Series” option drop-down – check/tick the “Trendline” checkbox, as shown below.
Step 4: Notice that the moment we check the “Trendlines” option, we get the options as seen in the below image and, by default, the Linear option is selected. There, select the “Exponential” option from the “Type” drop-down, as shown below.
We get the following Area Chart with the Exponential Trendlines in Google Sheets.
Example #3 – Customizing a trendline
We will plot a Linear Trendline in Google Sheets on a Line Chart for the given products sales and purchase amount and customize the trendlines.
The steps to create a Line Chart in Google Sheets for the given data are,
Step 1: Choose the cell range A1:C4 – select the “Insert” tab – choose the “Chart” option and the “Chart editor” window opens on the right-side, as shown below.
The following Area Chart generates.
Step 2: Let us update the titles, chart styles, range values within the chart, etc. Then, we get the following final Line Chart.
Step 3: Next, to plot the Linear Trendlines, go to the Chart Editor window – select the “Customize” tab – click the “Series” option drop-down – check/tick the “Trendline” checkbox, as shown below.
Step 4: Select the “Linear” option from the “Type” drop-down, as shown below.
We get the following Line Chart with the Linear Trendline in Google Sheets.
Step 5: Let us learn to customize the trendlines as follows:
- Select the “Type” as “Linear” and the “Line color” as “Yellow”, as shown below.
- Select the “Line opacity” to 60% and the “Line thickness” to “4px”, for clear visibility of the trendlines. Or else we can choose as per our requirement using the rest of the available options.
- In the “Label” drop-down, we can choose the options shown below. Here, we will select the “Custom” option.
- Finally, check/tick the “Show R2” option checkbox.
We get the following Trendlines customized Line chart.
Important Things To Note
- Unlike Ms Excel, we cannot plot the trendlines on a Bar Chart or a Bubble Chart.
- The Trend Line shows the “Moving Average” of the mentioned periods.
- We can always format the default Trend Line, i.e. the Linear Trendline, to make it more understandable.
Frequently Asked Questions (FAQs)
A few limitations of Trend Lines in Google Sheets are,
a. We must readjust the data constantly every time we update or add new data.
b. When we modify data by adding new data, the existing may not get updated unless we refresh the chart.
c. When deciding whether to use a trendline, we must keep the data type in mind and check if it is compatible and can it be accurately represented by this chart type.
The uses of Trend Line in Google Sheets are,
• It is used when there is a certain rate of increase or decrease in data. For ex, it is used to calculate the profit or loss of given data.
• It helps to visualize the points of the data variation when it is plotted on a chart.
• The Trend Line mathematically describes the line that best fits the data points when used in an equation or formula.
A few reasons the Trend Line in Google Sheets will not work are as follows:
• The dataset used to plot the chart and the trendline is modified or deleted.
• We are trying to plot a trendline to the Bubble of the Bar charts, which doesn’t support trendlines.
Use this Trend Line In Google Sheets Template to follow along with the examples in this article.
Download Excel Template