*Predict future results or expected values of a dependent variable in Google Sheets using the trendline equation generated from a graph.*

Extrapolation involves using historical information to make predictions about events that will happen in the future. In this post, you will learn how you can extrapolate a graph in Google Sheets.

Quick Jump

Toggle## How to Extrapolate a Graph in Google Sheets

Data analysis can be presented in various ways. Two of the most popular types of data analysis are descriptive and prescriptive analysis.

Descriptive analysis provides insight into what has happened in the past.

Prescriptive analysis, on the other hand, builds on descriptive analysis. When you do prescriptive analysis, historical data is used to create a model or an equation. This model is then used to speculate on future occurrences or expectations.

Predicting future events based on past occurrences is what extrapolation entails. To extrapolate a graph in Google Sheets, here are the steps involved.

### Step 1: Prepare the Data

In preparing your data, make sure you do the necessary data cleaning and transformation. This includes checking for and managing outliers, missing values, improper formatting, and all types of errors that can reduce the model’s accuracy.

### Step 2: Insert a Line Chart

Once the data is prepared, the next step is to insert a chart. Simply do the following:

1. Select the data.

2. Go to the **Insert** menu. Select **Chart** from the dropdown options.

3. From the **Chart editor** window on the right of the spreadsheet, go to the **Chart type** section, click on the dropdown chart options, and select **Line chart**.

Now you should have a line chart on your spreadsheet.

### Step 3: Add a Trendline

The trendline on a graph is used to estimate the underlying pattern of individual data points. Data points on a graph can be all over the place, but a trendline provides an estimate of the direction toward which the data tends.

Trendlines also provide a model or equation that is used to estimate the relationship between variables and predict future values.

To add a trendline, follow these steps:

1. In the **Chart editor**, click on the **Customize** tab.

2. Scroll up until you find the **Series** dropdown.

3. After clicking on the **Series** dropdown, go to the options in the last row. Check the **Trendline** box.

A straight line will appear across the graph as in the image below.

By default, the trendline uses a linear relationship to estimate the direction. However, the spreadsheet provides six different types of trendline options.

You can see these options by clicking on the **Type** dropdown box.

This will show you all the available trendline options.

To use the most appropriate trendline for your data set, you must consider the following important information.

1. **The R-squared Value**: The R-squared or coefficient of determination tells you how much variation in the dependent variable is explained by the independent variable. In other words, it measures how much influence variable X has on variable Y. By taking the square root of R-squared, you can get the correlation coefficient. The correlation coefficient is a measure of the strength and direction of the relationship between two variables.

To add the R-squared value to your graph, check the R-squared box.

The R-squared value should appear on the graph alongside the equation of the trendline.

The R-squared value ranges from 0 to 1. When choosing a trendline, you should use one with an R-squared that is closer to 1. The closer the R-squared is to 1, the more reliable the trendline equation.

2. **The Pattern of the Graph**: Graphs have different patterns, and each one can be described using any of the trendline types mentioned above. Therefore, understanding the pattern of your graph will help you select the best-fit trendline for the model.

In our sample, our graph doesn’t display a straight-line pattern. It doesn’t rise and fall at a constant rate; therefore, a linear graph won’t be appropriate.

The graph has a polynomial pattern. A polynomial trendline is best fit for a curve that has multiple highs and falls or bends (hills and valleys). As such, a polynomial trendline is most appropriate for our sample data. You will also find that the R-squared is much closer to 1 than that of the linear trendline.

### Step 4: Extrapolate Data

With the trendline equation, we can extrapolate values for the upcoming months in our sample data.

Our sample data contains sales information from January to October. The first thing we need to do is to add November and December to the Month column. After this, we will create a new column called month number (**Month no.**) and enter the corresponding month numbers.

Now, to get the expected sales value for November, follow these steps:

1. Double-click on the equation in the graph to highlight it. Then copy the highlighted equation.

2. Paste the copied equation into the cell that records the sales figure for November (**C13**).

3. Transform the copied equation into a formula. Add an equal to sign “**=**” in front of the equation, replace the parentheses with asterisks “*****” and replace “x” with cell references from the **Month no.** column.

When you drag down the formula, you can predict future sales value for the remainder of the months.

As the new values are added, the curve on the graph also adjusts with respect to the changes. The trendline equation will also update.

## Extrapolation in Google Sheets: FAQs

### Can you extrapolate a line in Google Sheets?

Yes, you can extrapolate a line graph in Google Sheets.

### How do I extrapolate a graph?

Extrapolating a graph in Google Sheets is easy. The first thing you want to do is make sure your data is prepared. That means scanning for errors and outliers that can affect your model negatively. When you’re sure your data is in the right shape, select the ranges containing your data, go to the **Insert** menu, and select **Chart**. You can also insert the chart from the toolbar options.

After inserting the chart, change the chart type to a **Line chart** in the Chart editor. From here, you want to add a trendline. To add the trendline, click on the **Customize** tab, go to the **Series** option, and add a checkmark to the **Trendline** box. From the options that appear, add a checkmark to the **Show R****2** box and select **Use equation** in the **Label** option.

When you do this, a trendline, an equation, and the R-squared value should appear on the chart. The closer the R-squared value is to 1, the more accurate the equation will be.

To extrapolate future values, copy the equation by double-clicking on the equation in the chart. Paste this equation in the cell whose value you want to determine. Transform the copied equation into a formula by adding an equals sign “**=**” in front of the equation, replacing parentheses with asterisks “*****” and “x” with cell references.

Dragging down the formula, you can now get the expected values of future events.