How to Use Excel to Determine the Relationship Between Two Sets of Data

by Emily Ediger
You can create a line graph in Excel to view the relationship between data.

You can create a line graph in Excel to view the relationship between data.

Microsoft Excel can help you visualize your data to determine relationships between two sets. Excel has a variety of graphs that convert your values to data points and allow you to see what type of association your data sets have. You can also use Excel's graphs to determine that there is no relationship between two data sets. Storing your data in an Excel spreadsheet allows you to visualize, calculate and analyze your data. You can email your graph, copy it into another document or place it in a presentation.

1

Open the Excel spreadsheet that contains your two data sets. If you don't already have your data sets in Excel, create a new spreadsheet and copy and paste your data sets in a new sheet. Place your x-values in column A and your y-values in column B.

2

Label column A with a description of your x-values. Label column B with a description of your y-values. You can set up your charts more easily if your data sets are labeled.

3

Highlight your data range and select the "Insert" tab of the ribbon at the top of the page. Click "Scatter" under the "Charts" area to create a scatterplot. A scatterplot takes the x-value and corresponding y-value to create a data point on your chart.

4

Select the "Scatter with Only Markers" options under the "Scatter" button. Click the "Layout" button on the ribbon and select "Trendline" under the "Analysis" area. Creating a trendline allows you to visually determine how closely your two data sets are related.

5

Click the "More Trendline Options" button, select "Linear" or "Exponential" and check the "R squared" option box. You will see the "R squared" value on the chart, which you can use to determine the strength of the linear relationship. Values close to -1 or 1 represent strong relationships and values close to 0 are weak linear relationships.

Tip

  • check You can add labels to your chart and axes from the "Layout" tab of the ribbon. Labels allow your reader to better understand what the chart represents.

About the Author

Emily Ediger began writing professionally in 2007. Her work includes documenting technical procedures and editing event programs. Her expertise lies in technology, interactive learning and information retrieval. She holds a Bachelor of Arts in English from Portland State University.

Photo Credits

  • photo_camera Ryan McVay/Photodisc/Getty Images