How to Display Equation on Chart in Excel on Mac
Adding a regression equation to a chart in Excel isn't just for statisticians, it's a powerful way for marketers, founders, and analysts to visualize the relationship between two variables. This article will walk you through exactly how to display an equation and the R-squared value on a chart in Excel for Mac, breaking down what it all means for your business.
Why Bother Showing an Equation on Your Chart?
You might wonder why you’d ever need to add a mathematical formula to your business chart. The answer is that it transforms your chart from a simple picture of your data into an analytical tool. Displaying the equation of a trendline helps you understand and even predict outcomes based on your data.
Consider these common scenarios:
Marketing Spend vs. Revenue: You can determine a formula that estimates how much revenue you can expect for a given amount of ad spend. The equation might be something like Sales = 2.5 * (Ad Spend) + 1,000. This formula tells you that for every additional dollar you spend on ads, you generate $2.50 in sales, with a baseline of $1,000 in sales even with no ad spend.
Website Traffic vs. Sign-ups: Analyze the relationship to predict how many new sign-ups you'll get if you hit a certain website traffic goal.
Price vs. Units Sold: Understand price elasticity by seeing how a change in a product's price (your x-variable) affects the number of units sold (your y-variable).
The equation gives you a model for making forecasts and setting goals, while the R-squared value (which we'll cover later) tells you how reliable that model is.
Step 1: Get Your Data Ready
Before you create a chart, you need clean, well-organized data. The method for displaying an equation works best with data where you're trying to find a relationship between an independent variable (the thing you control, like ad spend) and a dependent variable (the thing you measure, like revenue).
Arrange your data in two columns. For our example, let's use a simple dataset tracking monthly Facebook Ad Spend and the corresponding Website Sales.
Here’s what our sample data looks like in Excel:
Column A: FB Ad Spend ($)
Column B: Website Sales ($)
Your sheet should look something like this:
A (Ad Spend) | B (Sales) |
500 | 2150 |
800 | 3000 |
1100 | 3850 |
1500 | 4500 |
1800 | 5700 |
2200 | 6500 |
2500 | 7400 |
Step 2: Create a Scatter Chart on Your Mac
A scatter (XY) chart is the ideal format for this kind of analysis because it plots individual data points without connecting them, making it easy to see the underlying relationship and fit a trendline. A line chart, by contrast, implies a sequence, which isn't what we’re trying to show here.
Follow these steps to create your scatter chart in Excel on Mac:
Click and drag to select both columns of your data, including the headers ("Ad Spend" and "Sales").
Navigate to the Insert tab on the Excel ribbon at the top of the screen.
In the "Charts" section, find the scatter chart icon (it looks like a set of dots). Click it to open a dropdown menu of scatter chart types.
Select the first option, the basic Scatter chart.
Excel will instantly generate a chart on your worksheet plotting your data points. You’ll see "Ad Spend" on the horizontal X-axis and "Sales" on the vertical Y-axis.
Step 3: Add a Trendline and Display the Equation
Now that you have your chart, the next step is to add a trendline. This is the visual representation of the relationship in your data. Excel for Mac makes this easy.
Click on the chart to select it. You should see two new tabs appear on the ribbon: Chart Design and Format.
Make sure you’re on the Chart Design tab. On the far left, click the Add Chart Element button.
From the dropdown menu, hover over Trendline and select Linear. A dotted line will immediately appear on your chart, cutting through your data points.
Your chart now has a trendline, but the equation is still hidden. Here’s how to reveal it:
Go back to Add Chart Element > Trendline > More Trendline Options.... This will open the Format Trendline pane on the right side of your Excel window.
If the pane doesn't open, you can also right-click (or Control+click on a Mac) directly on the trendline itself and select Format Trendline... from the context menu.
In the Format Trendline pane, make sure the Trendline Options tab (the icon with three vertical bars) is selected.
Scroll down to the bottom of the options. You'll see two checkboxes:
Display Equation on chart
Display R-squared value on chart
Check both boxes. As soon as you do, a small text box containing the equation and the R-squared value will appear on your chart. You did it!
Step 4: Understand What the Equation and R-squared Actually Mean
Seeing y = 2.9248x + 606.01 on your chart looks technical, but it’s telling you a simple story in the language of your data. Let's break it down:
y: This represents your dependent variable, which is Website Sales in our example.
x: This represents your independent variable, FB Ad Spend.
2.9248 (the slope, or 'm'): This is the most valuable number here. It means that for every one unit increase in x, y increases by 2.9248. In plain English: For every additional $1 you spend on Facebook Ads, you can expect to generate approximately $2.92 in sales.
606.01 (the y-intercept, or 'b'): This is the value of y when x is 0. Practically speaking, it suggests that if you spent $0 on Facebook Ads, you’d still generate about $606 in sales from other sources.
And what about the R-squared value?
The R-squared value, or coefficient of determination, tells you how well the trendline fits your data. It's always a number between 0 and 1 (or 0% and 100%).
An R-squared of 1.0 (or 100%) means your data is a perfect fit for the line. Every change in your Ad Spend perfectly predicts the change in Sales.
An R-squared of 0 means there is no correlation at all.
In our example, the R-squared value is approximately 0.9789. This is a very high value, indicating a strong relationship. It means that about 97.9% of the variation in Website Sales can be explained by the changes in FB Ad Spend. This gives you strong confidence that your ad spending is a primary driver of sales.
Step 5: Formatting and Tidying Up
The default placement of the equation text box can be awkward, often overlapping your data points or the gridlines. You can treat it just like any other text box in Excel.
Move It: Click on the text box (be careful not to click the chart itself) and drag it to a clear, open spot on your chart, such as the top corner.
Resize It: Select the text box and use the small white handles on the corners and sides to make it bigger or smaller.
Change the Font: With the text box selected, go to the Home tab. You can change the font size, make it bold, or change its color to improve readability.
You should also give your chart a clear title (e.g., "Impact of Facebook Ad Spend on Website Sales") and label your axes if Excel hasn't done it for you, using the Add Chart Element menu.
Choosing Other Trendline Types
While a linear (straight-line) trendline is the most common, Excel for Mac offers other types in the Format Trendline pane. You might use these if your data shows a curve instead of a straight line.
Polynomial: Useful when the data has peaks and valleys, like for analyzing seasonality. For example, the relationship between time of year and sales of a seasonal product.
Logarithmic: Best for when your data values increase or decrease quickly at first and then level off. Think of the law of diminishing returns - your first $1,000 in ad spend might have a huge impact, but the impact of an additional $1,000 dwindles as your budget grows.
Exponential: Use this for data where the values rise or fall at increasingly higher rates, such as modeling viral growth.
When you select a different trendline type, Excel automatically updates the equation on the chart to match the new formula type. Always look at the R-squared value to see which model provides the best fit for your specific data.
Final Thoughts
Popping an equation onto a chart in Excel for Mac is a simple yet insightful way to add a layer of predictive analysis to your reports. By understanding the relationship between different business metrics through trendlines, you're better equipped to make smarter, data-driven decisions for forecasting budgets and setting future goals.
While mastering these features in Excel is a great skill, we know the main goal is to get answers quickly without getting lost in formatting panes and option menus. We built Graphed to remove this friction entirely. Instead of clicking through chart menus, you can just ask questions in plain English like "what is the relationship between my Facebook ad spend and Shopify sales?" and get an instant analysis, complete with real-time visualizations that connect to all your data sources automatically.