To make a correlation graph in Excel, select two columns of paired numeric data and choose Insert > Insert Scatter (X, Y) or Bubble Chart > Scatter. A scatter plot shows how the two variables move together; calculate their Pearson correlation separately with Excel’s CORREL function if you want a numerical measure.
What a correlation graph shows
People often use correlation graph to mean a scatter plot used to inspect the relationship between two variables. Each marker represents one paired observation: its horizontal position comes from an X value and its vertical position from the matching Y value. For example, each point might represent one month, with advertising spend on the X axis and sales on the Y axis.
A scatter plot is useful because it reveals the shape of the relationship, not just whether two columns have similar averages. A rising pattern suggests that higher X values tend to occur alongside higher Y values; a falling pattern suggests the reverse. A loose cloud of points may indicate little obvious linear relationship. The graph does not, by itself, prove that one variable causes changes in the other.
Excel does not have a separate chart type named Correlation Graph. The scatter chart is the appropriate chart for examining two numeric variables, while a correlation coefficient such as Pearson’s r summarizes the strength and direction of a linear relationship as a number from -1 to +1.
Prepare the data before charting
Arrange the two variables in adjacent columns, with one observation per row. Put a clear heading at the top of each column, such as Temperature and Ice Cream Sales. Each value in a row must belong to the same observation: matching dates, people, products, or other units. If the rows do not correspond, the chart and correlation calculation will be meaningless.
- Use numeric values for both variables. Text-formatted numbers, notes, and symbols can be misread or omitted.
- Decide how to handle blank cells, errors, and incomplete pairs. Do not delete a value from one column while leaving its matching observation in the other.
- Check for duplicate rows and extreme values. A genuine unusual observation may be important, but a data-entry error can distort the apparent pattern.
- Keep units and measurement periods consistent. Mixing weekly sales with monthly advertising totals, for example, can create a misleading comparison.
For a simple example, imagine five paired observations: X values of 1, 2, 3, 4, and 5, and corresponding Y values of 2, 3, 5, 4, and 6. Excel will plot five points. The pairing is essential: sorting one column independently would change which values are compared and therefore change the result.
Create a scatter plot in Excel
In Excel for Microsoft 365 on Windows, select both data columns, including their headings, then use Insert > Insert Scatter (X, Y) or Bubble Chart > Scatter. Choose the basic scatter option, which displays markers without connecting lines. If Excel assigns the wrong columns to the axes, edit the series rather than rebuilding the data.
- Click the chart, then open Chart Design > Select Data.
- Select the series and choose Edit. Set Series X values to the numeric range for the horizontal variable and Series Y values to the matching numeric range for the vertical variable. Exclude the heading cells from these value ranges.
- Add descriptive labels. Select the chart, then use Chart Design > Add Chart Element > Axis Titles. Replace the default title and axis-title text with names that include units when useful.
- To show a fitted straight line, use Chart Design > Add Chart Element > Trendline > Linear. A trendline helps communicate the overall direction, but it does not make the relationship causal.
On Excel for Mac, the same scatter-chart approach is available from the Insert tab; ribbon labels and chart controls can differ slightly by release. Excel for the web also supports scatter charts, though some detailed chart-formatting controls may be more limited than in the desktop app. If data is in nonadjacent columns, select the first range and then use the platform’s range-selection modifier to add the second, or create the chart and define its series through Select Data.
Calculate the correlation coefficient
A graph gives a visual impression; Pearson’s correlation coefficient, r, gives a compact measure of the direction and strength of a linear association. It is not a percentage and it does not describe every possible relationship. Values near +1 indicate a strong positive linear pattern, values near -1 a strong negative linear pattern, and values near 0 little linear association. A coefficient near zero can still occur when the data follows a curved pattern.
With the X observations in A2:A6 and the paired Y observations in B2:B6, enter this formula in an empty cell:
=CORREL(A2:A6,B2:B6)
For the example values above, this returns 0.9. The result is positive and relatively close to +1, consistent with the upward pattern in the scatter plot. CORREL ignores text, logical values, and empty cells in referenced ranges, but the two ranges must contain corresponding observations; mismatched omissions can make the computed pairs differ from the pairs you intended. Check that the two ranges cover the same rows and that missing data is handled consistently.
If the data contains a non-linear pattern, Pearson’s r may understate the relationship. For example, a U-shaped set of points can have a correlation close to zero even though Y changes systematically with X. Inspect the scatter plot alongside the number, and consider whether a different analysis is appropriate before drawing conclusions.
Make a correlation matrix for several variables
When you have more than two numeric variables, a correlation matrix is often more useful than making a separate graph for every pair. It displays a coefficient for each variable pair, making it easier to spot broad patterns. In Excel desktop for Windows, the Analysis ToolPak provides this calculation: choose Data > Data Analysis > Correlation.
If Data Analysis is not visible on the Data tab, enable the add-in through File > Options > Add-ins. In the Manage box, select Excel Add-ins, choose Go, check Analysis ToolPak, and select OK. On Mac, enable the add-in from Tools > Excel Add-ins and select Analysis ToolPak if it is available in your installation. Excel for the web does not provide the same desktop ToolPak workflow, so use CORREL formulas or open the workbook in the desktop app.
To run the tool, place each variable in its own column, select Data > Data Analysis > Correlation, and set the Input Range to include the numeric columns. Choose Grouped By Columns; select Labels in First Row if the headings are included. Choose an Output Range or a new worksheet, then select OK. The output includes each variable’s correlation with itself, normally 1, as well as the pairwise correlations. A matrix is a summary, not a substitute for plotting important pairs: it can hide outliers, clusters, and curved relationships.
Interpret the chart without overclaiming
Read the point pattern before relying on a single statistic. A narrow upward band suggests positive linear association; a narrow downward band suggests negative association. A broad cloud suggests a weaker linear relationship. Clusters may indicate that the data combines different groups, while a lone point far from the others may have a large effect on the correlation coefficient and fitted trendline.
Correlation is not causation. A connection between study time and test scores, for example, would not by itself show that study time caused the score difference. Other factors, selection effects, or the direction of influence may explain some or all of the association. A correlation also depends on the observations included: a limited range of values or a different time period can produce a different result.
Do not confuse a chart trendline’s R-squared value with the correlation coefficient. For a simple linear trendline with one X variable, R-squared is the square of Pearson’s r, so it loses the sign. If a chart reports an R-squared of 0.81, the correlation could be +0.9 or -0.9; the slope’s direction distinguishes them. In more complex models, that simple relationship should not be assumed.
Common problems and how to fix them
- The chart looks like a line chart: choose the scatter chart option, not a line chart. A line chart treats horizontal labels as categories, while a scatter plot uses numeric X positions.
- Excel puts the wrong variable on an axis: use Chart Design > Select Data > Edit and assign the intended X and Y ranges explicitly.
- The graph has one point or no points: verify that the source ranges contain numeric values and that the X and Y ranges are the same length. Check for numbers stored as text.
- The coefficient seems unexpectedly weak or strong: inspect the scatter plot for outliers, separate groups, restricted ranges, or curved patterns. Confirm that the same paired rows are included in both ranges.
- The ToolPak command is missing: enable Analysis ToolPak in Excel’s add-ins settings, or use the CORREL worksheet function instead.
One common mistake is to connect the markers and treat the result as a time-series line when the X values are not ordered time points. For ordinary paired measurements, markers without connecting lines make the relationship easier to assess. If the X variable is time and sequence matters, a line chart may be useful for showing change over time, but it answers a different visual question.
When to use a chart, a formula, or a matrix
Use a scatter plot when you need to see the shape of one relationship, detect unusual observations, or explain the pattern to someone else. Use CORREL when you need a concise Pearson coefficient for a specific pair of columns. Use the ToolPak correlation matrix when you have several variables and want a quick overview of their pairwise linear associations.
These methods work best together. A matrix can help identify pairs worth investigating, the scatter plots can reveal whether their relationships are linear or affected by outliers, and the coefficient can summarize a relationship after the data has been checked. None of the three establishes cause and effect or replaces a study design suited to the question.
Frequently Asked Questions
Is a correlation graph the same as a scatter plot?
Usually, yes. In Excel, a scatter plot is the standard chart for showing paired numeric values and visually examining their relationship. Excel does not list a separate chart type called a correlation graph.
How do I show the correlation value on an Excel chart?
Excel does not automatically place Pearson’s r on a scatter chart. Calculate it with CORREL in a worksheet cell and display that result separately, or add a text box to the chart and enter the value. If the data changes, remember to update any manually entered chart text.
Does a trendline show the correlation?
No. A linear trendline shows a fitted direction, and its optional R-squared value describes fit in a different form. For a simple linear relationship, the sign of r comes from the slope and its magnitude is the square root of R-squared, but CORREL calculates r directly.
Can correlation be negative?
Yes. A negative coefficient means higher values of one variable tend to accompany lower values of the other in a linear pattern. It does not mean the relationship is bad, and it does not prove that either variable causes the other to change.
Why does CORREL return an error?
Check that both ranges contain numeric observations and have matching dimensions. Error values in the data can also prevent a valid result. Remove or resolve errors consistently and make sure each X value remains paired with its correct Y value.
Related
- Protect Sheet in Excel: Allow Editing Specific Cells
- Excel Conditional Formatting Icon Sets Explained
- Markdown Table Merge Cells? What You Can Do
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Dashboard Styles: Layouts, Uses, and Design TipsExcel dashboard styles are the visual and structural approaches used to present important measures, trends, and comparisons on…
- Explode Pie Chart in Excel: Steps and Formatting TipsTo explode a pie chart in Excel, select the chart, select the slice you want to move, open…
- Pivot Charter School in Riverview, FL: What to KnowYes. Pivot Charter School, also known as Pivot Charter School of Tampa Bay, is a tuition-free public charter…
- How to Make a Line Graph in Excel With Data in a Few StepsTo make a line graph in Excel, arrange your data in columns with time periods or other categories…
- Google Sheets Pivot Tables: Create, Edit, and AnalyzeYes. In Google Sheets, select a range of data and choose Insert > Pivot table, then use the…
- Excel Pivot Tables: Analyze Data with Examples and InsightsYes—an Excel PivotTable analyzes a list by summarizing its values and letting you rearrange, filter, group, and compare…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.