How to Display Equation on Chart in Excel

Displaying an equation directly on an Excel chart instantly transforms a simple visualization into a powerful analytical tool. It allows you to see the mathematical relationship behind your data, understand trends, and even make future projections. This guide will walk you through, step-by-step, how to add a trendline and its corresponding equation to your charts in Excel.

What Do You Gain by Adding an Equation to a Chart?

Before jumping into the "how," let's quickly cover the "why." Putting a formula on your chart isn't just for appearances, it serves several practical functions:

• Trend Analysis: It quantifies the trend in your data. Instead of just saying "sales are going up," you can say "sales are increasing at a rate of $500 per unit of ad spend," which is far more actionable.
• Predictive Power: Once you have the equation, you can use it for forecasting. For example, if your equation is y = 500x + 2000, you can plug in a future value for 'x' (like next month's ad budget) to predict 'y' (the resulting sales).
• Model Validation: Alongside the equation, Excel can display an R-squared (R²) value. This number, ranging from 0 to 1, tells you how well the trendline fits your actual data points. A value of 0.95, for example, means 95% of the variation in the data is explained by the model—a very strong fit!
• Clear Communication: Presenting a chart with its equation and R² value shows a deeper level of analysis, making your findings more credible and easier for stakeholders to understand.

Step-by-Step: Adding an Equation to a Scatter Plot

The most common scenario for displaying an equation is with a scatter plot, which is designed to show the relationship between two different variables. Let’s use a simple example: tracking monthly ad spend against website traffic.

Imagine you have the following data in your worksheet:

Column A: Ad Spend ($) Column B: Website Sessions

Step 1: Create the Scatter Plot

First, you need to visualize your data. Excel makes this easy.

1. Select your data. Click and drag your cursor to highlight both the "Ad Spend" and "Website Sessions" columns, including the headers.
2. Go to the Insert tab. In the Excel ribbon at the top, click on Insert.
3. Choose the Scatter chart. In the "Charts" section, find the icon that looks like a plot of dots and click it. Select the first option, which is a simple Scatter chart.

Excel will immediately generate a chart showing your ad spend on the x-axis and website sessions on the y-axis.

Step 2: Add a Trendline

Now, we need to add a line of best fit that represents the general trend of your data points.

Method 1: Using the Chart Elements Button

• Click once on your chart to select it.
• A plus sign (+) icon will appear in the top-right corner of the chart. Click it to open the "Chart Elements" menu.
• Hover your mouse over Trendline. An arrow will appear to the right. Click it and select More Options….

Method 2: Right-Clicking the Data Points

• Right-click on any of the blue dots (the data series) on your chart.
• From the context menu that appears, click Add Trendline....

Either method will add a default linear trendline to your chart and open the "Format Trendline" pane on the right side of your screen.

Step 3: Display the Equation and R-squared Value

This is where the magic happens. The "Format Trendline" pane holds all the controls you need.

1. Make sure you are in the Trendline Options tab (the icon with three green bars).
2. Scroll down to the bottom of the options.
3. Check the box next to "Display Equation on chart". You will immediately see an equation (like y = 9.87x + 1050) appear on your chart.
4. For extra analytical power, also check the box for "Display R-squared value on chart". This adds the R² value right below the equation, giving you instant insight into the effectiveness of your model.

That's it! You've successfully added a predictive equation to your chart.

Exploring Different Trendline Options and Their Equations

Excel offers more than just the simple straight-line model. The "Format Trendline" pane lets you choose the type of equation that best fits your data’s underlying pattern. Here’s a quick overview of the most common options:

Linear

Equation: y = mx + b

This is the default option and is best for data that shows a steady, consistent rate of increase or decrease. It's the simplest trend to interpret.

Exponential

Equation: y = ce^{bx}

Use this when your data grows or decays at an increasingly rapid rate. Think of phenomena like viral marketing campaign growth or compound interest. The curve gets steeper and steeper over time.

Logarithmic

Equation: y = c ln(x) + b

This is the opposite of exponential. It's ideal for data that increases or decreases quickly at the beginning and then starts to level off. Examples include learning curves, where you make rapid progress at first but slower gains over time.

Polynomial

Equation: y = b + c₁x + c₂x² + ...

A polynomial trendline is incredibly versatile and can be used for data with fluctuations (peaks and valleys). The "Order" determines how many bends the line has. An Order 2 polynomial has one bend, Order 3 has two bends, and so on. Be careful not to overcomplicate it, often, a simpler model is better.

Power

Equation: y = cx^{b}

This is used for data that measures increases at a specific rate. For example, the distance a car travels versus its acceleration.

You can experiment by clicking on each of these options in the "Format Trendline" pane. Watch how the line, equation, and R-squared value all change to reflect the new model. Always aim for the simplest model with the highest R-squared value.

Using Equations with Other Chart Types

While scatter plots are ideal, you can also add trendlines and equations to other common chart types, like bar, column, and line charts.

For Bar or Column Charts: The process is exactly the same. Click your chart, right-click on one of the bars, select "Add Trendline," and then check the box to display the equation. This is useful for spotting trends in sequential data, like monthly sales growth.

For Line Charts: Line charts are essentially scatter plots with the dots connected, so adding a trendline and equation here is very common and follows the exact same steps.

Note that this feature doesn't work for every single chart type. You can't add a trendline to a Pie chart, Treemap, or Waterfall chart, for instance, as they do not show relationships between two numerical axes.

Formatting Your Equation for Readability

The default equation text can sometimes be small and awkwardly placed.

• Move it: Click on the equation's text box. You can then drag and drop it to a different location on your chart, such as a clear area where it does not obscure your data points.
• Resize and restyle it: With the text box selected, go to the Home tab on the Excel ribbon. From there, you can increase the font size, make the text bold, or change its color to improve visibility.
• Format the numbers: Sometimes, the equation appears with too many decimal places. Right-click on the equation text box and choose Format Trendline Label.... In the pane that opens, you can change the category to "Number" and specify the number of decimal places to clean it up.

Using the Equation to Make Predictions (Forecasting)

The real power of displaying the equation is using it to forecast future values. There are two main ways to do this in Excel.

Manually Calculating a Forecast

Once you have the equation, you can use it like any other algebraic formula. Let’s say your chart gave you the equation:

y = 5.42x + 1380

In our example, 'x' is ad spend and 'y' is website sessions. If you want to predict how many website sessions you’ll get with an ad spend of $1,500, simply substitute 1500 for 'x' in a blank Excel cell:

= (5.42 * 1500) + 1380

The result, 9510, is your forecast for website sessions.

Using Excel's FORECAST Function

For more integrated forecasting, Excel has built-in functions. The FORECAST.LINEAR function does the same calculation for you.

The syntax is: =FORECAST.LINEAR(x, known_y's, known_x's)

• x: The new x-value you want to forecast for (e.g., cell reference to 1500).
• known_y's: The range of your existing y-values (e.g., B2:B13).
• known_x's: The range of your existing x-values (e.g., A2:A13).

This function creates the same linear regression model behind the scenes and returns the predicted 'y' value, making it easy to create an entire column of forecasts based on different potential inputs.

Final Thoughts

Adding equations to your Excel charts is a small step that produces a big impact, elevating your reports from simple data displays to sophisticated analytical dashboards. By mastering trendlines, you can better understand the story behind your data, demonstrate the validity of your insights, and make informed predictions about the future.

Eventually, tracking performance across platforms and manually building reports in Excel can become time-consuming. When you're spending more time exporting CSVs and fighting with pivot tables than you are analyzing insights, we built Graphed to help. We automate the entire reporting process by connecting directly to your marketing and sales tools, allowing you to create powerful dashboards using simple, natural language. Just ask "show me ad spend vs revenue for my top campaigns," and your live dashboard is ready in seconds.