How to Make a Correlation Graph in Excel

Seeing how two different things relate to each other is a fundamental part of data analysis, and a correlation graph is one of the clearest ways to visualize that relationship. Whether you're trying to figure out if your ad spend is actually leading to more sales or if customer satisfaction scores impact repeat purchases, this is the chart for the job. This guide will walk you through exactly how to create a correlation graph in Excel using a scatter plot, covering everything from structuring your data to adding a trendline and interpreting the results.

First, What Exactly is a Correlation Graph?

A correlation graph visually shows the relationship - or lack thereof - between two different variables. In Excel, this is most commonly done using a scatter plot (also called an XY scatter chart). Each dot on the graph represents a single data point with two values: one for the horizontal axis (X-axis) and one for the vertical axis (Y-axis).

By plotting all your data points, you can quickly spot a pattern. This pattern reveals the type of correlation:

• Positive Correlation: As one variable increases, the other variable also tends to increase. The dots on the graph will form a pattern that slopes upwards from left to right. Example: The more you study for a test (Variable X), the higher your score tends to be (Variable Y).
• Negative Correlation: As one variable increases, the other variable tends to decrease. The dots on the graph will slope downwards from left to right. Example: The colder the weather gets (Variable X), the fewer ice cream cones a shop sells (Variable Y).
• No Correlation: There is no apparent relationship between the two variables. The dots will be scattered around the graph with no discernible pattern. Example: A person's shoe size (Variable X) and their monthly salary (Variable Y).

Understanding these patterns helps you move from just looking at numbers to actually seeing the story your data is telling.

Step 1: Get Your Data Ready for Excel

Before you even think about making a chart, your data needs to be organized properly. This is the single most important step, and getting it right will make the rest of the process smooth.

For a correlation graph, you need two columns of numerical data, side-by-side. One column will be for your independent variable (the one you think is causing a change), and the other will be for your dependent variable (the one that's being affected). The independent variable always goes on the X-axis (horizontal), and the dependent variable goes on the Y-axis (vertical).

Let's imagine you're a marketing manager trying to understand the relationship between digital ad spend and website traffic. To do this, you'd set up a simple table with "Ad Spend" as your independent variable and "Website Visitors" as your dependent variable. Your sheet should look like this:

Make sure your data is clean. Check for empty cells in the middle of your dataset or text mixed with numbers, as these can cause errors when creating the chart.

Step 2: Create the Scatter Plot in Excel

Once your data is neatly arranged, creating the basic chart takes less than a minute. Here are the step-by-step instructions:

1. Select Your Data: Click and drag your mouse to highlight the two columns containing your numerical data. In our example, you would select all the cells under "Ad Spend ($)" and "Website Visitors." Do not include the column headers in your selection just yet.
2. Go to the Insert Tab: Look at the top ribbon in Excel and click on the Insert tab.
3. Find the Charts Group: In the middle of the Insert ribbon, you'll see a section called Charts.
4. Click the Scatter Chart Icon: Look for the icon with a lot of dots scattered on it. This is the "Insert Scatter (X, Y) or Bubble Chart" button.
5. Choose the First Scatter Option: A dropdown menu will appear. Select the very first option, which is a simple scatter plot with only markers (dots).

That's it! Excel will immediately generate and place a default scatter plot onto your worksheet. You’ll see your data points plotted, giving you an initial glimpse of the relationship. In our example, you'd likely see the dots trending upwards, hinting at a positive correlation between ad spend and visitors.

Step 3: Enhance Your Correlation Graph for Readability

The default chart Excel provides is functional, but it's not ready to be presented to your team or clients. It lacks context. A few quick formatting changes can transform it from a raw output into a clear and professional visualization.

Give Your Chart a Descriptive Title

The default title is usually something generic like "Chart Title." This isn't helpful. Double-click on it and change it to something that describes exactly what the chart shows.

• Bad Title: "Ad Spend Data"
• Good Title: "Correlation Between Daily Ad Spend and Website Visitors"

Label Your Axes

Without axis labels, no one knows what the numbers on the X and Y axes represent. This is arguably the most common mistake people make. To add them:

1. Click anywhere on your chart to select it.
2. A small plus sign (+) will appear in the top-right corner of the chart. Click it to open the Chart Elements menu.
3. Check the box next to Axis Titles.
4. Text boxes will appear on your chart for the horizontal and vertical axes. Click on each one and type in the appropriate label, including units if applicable (e.g., "Ad Spend ($)" for the X-axis and "Website Visitors" for the Y-axis).

Clean Up the Look

You can also use the Chart Elements menu to remove gridlines if you prefer a cleaner look, or use the paintbrush icon (Chart Styles) that appears next to the chart to quickly change the color scheme and overall style.

Step 4: Quantify the Relationship with a Trendline and R-Squared

Your scatter plot now visually shows the correlation, but how strong is it really? A trendline and R-squared value can give you a statistical measure of the relationship's strength. A trendline (or line of best fit) is a straight line that cuts through the data in a way that best shows the overall trend.

Here’s how to add it:

1. Once again, click the plus sign (+) to open the Chart Elements menu.
2. Hover over Trendline and check the box. A line will immediately appear on your chart.
3. Now, to add the statistics, click the small arrow to the right of Trendline and select More Options…. This will open the Format Trendline pane on the right side of your screen.
4. Scroll down to the bottom of the options and check the boxes for Display Equation on chart and Display R-squared value on chart.

You will now see the R² value on your chart. This little number is incredibly useful.

What Does R-Squared Mean?

The R-squared value, also known as the coefficient of determination, tells you how well your independent variable explains the changes in your dependent variable. It's a value between 0 and 1.

• An R² of 1 means a perfect correlation. All the variation in your website visitors is perfectly explained by your ad spend.
• An R² of 0 means there’s no correlation at all. Your ad spend has no bearing on website traffic.
• A value like R² = 0.82 indicates a strong correlation. It means that 82% of the fluctuation in your website visitors can be explained by the changes in your ad spend. The remaining 18% is due to other factors (e.g., seasonality, SEO, email campaigns).

A higher R-squared value gives you more confidence that the relationship you're seeing isn't just a random fluke.

Step 5: Final Example

Let's walk through one more example: analyzing session duration and pages per visit for a website. The hypothesis is that visitors who stay on the site longer (session duration) also tend to view more pages (pages per visit).

1. Data Setup: Create two columns in Excel: "Avg. Session Duration (sec)" and "Pages per Visit."
2. Create the Chart: Select the data, go to Insert > Scatter, and choose the first scatter plot type.
3. Format the Chart: Change the title to "Relationship Between Session Duration and Pages Viewed." Use the Chart Elements (+) menu to add axis titles for "Average Session Duration (Seconds)" and "Pages per Visit."
4. Analyze with Trendline: Click Chart Elements (+) again, check "Trendline," and go to "More Options." Check the box to display the R-squared value on the chart.

If you see an R-squared value of, say, 0.65, you can interpret that directly: "Our analysis shows a moderately strong positive correlation. Roughly 65% of the variation in pages per visit can be attributed to how long a user stays on our site. This suggests that engaging content that keeps users on the site longer directly contributes to deeper exploration of our content."

Final Thoughts

Creating a correlation graph in Excel is a straightforward process that transforms two columns of data into a powerful visual insight. By using a scatter plot and enhancing it with a trendline and an R-squared value, you can quickly see if and how your business variables are related, moving beyond guesswork to data-backed conclusions.

While Excel is a fantastically useful tool, the process still involves several manual steps - from fetching and cleaning the data to customizing the chart every time. At Graphed, we automate this entire workflow. You can simply connect your live data sources like Google Analytics or Salesforce and ask in plain English, "Show me the correlation between monthly ad spend and revenue." We instantly build a live, interactive dashboard for you, saving you valuable time on manual report-building and letting you focus on making better decisions with your data.