READ, WATCH and CREATE: Excel Spreadsheets Skills (3)
As we learned previously a Scatter (XY) Plot gives us the relationship between two (or more) sets of data (X and Y). When plotting data in a graph, we may often want to visualize the general trend in our data. This can be done by adding a trendline to a chart.
Using a trendline in a graph provides a visual illustration of how a variable (X) relates to another variable (Y). A trendline is a line that is used to represent the behavior of a set of data to determine if there is a certain pattern. A trendline is an analytical tool used most often in conjunction with a scatter plot (a two-dimensional graph of ordered pairs) to see if there is a relationship between two variables. The main purposes of a trendline are:
Determining if a set of points exhibits a positive trend, a negative trend, or no trend at all. The red trendlines in the picture below illustrate these three relationships with sets of data.
Positive Trend: If the values of one set of data increases and the values of other set also increases then the two sets of related data shows a positive trend.
Negative Trend: As the value of a set of data increases, the values of the other set of data decreases, and vice-versa, as the X-values decrease, the Y-values increase. This two sets of related data show a negative trend.
Predicting unknown or future data points.
Trendline in Excel:
A trendline, also called “Line of Best Fit”, is a straight or curved line in a chart that shows the general pattern or overall direction of the data. We will frequently use this analytical tool to show data changes (fluctuations) over a period of time or correlation between two variables (X and Y). When plotted in a graph, a trendline looks a little bit like a line chart, but it does not connect the actual data points as a line chart does. A best-fit line shows the general trend in all the data.
Excel charts and trendlines:
A trendline can be added to XY scatter, unstacked 2-D bar, column, area, line graphs. You cannot add a trendline to 3-D or stacked charts, pie, and radar.
Excel trendline equation and fit (R2):
Trendline equation is a formula that mathematically describes the line that best fits the data points. The equations are different for different trendline types, though in every equation Excel uses the least squares method to find the best fit for a line though data points. When drawing the line of best fit in Excel, you can display its equation in a chart. Additionally, you can display the R-squared value (Coefficient of Determination), which indicates how well the trendline corresponds to the data. The closer the R2 value to 1, the better the fit.
Since you are all Kinesiology** students, let’s focus our learning on examples that apply to the study of movement.
**Kinesiology is the scientific study of human or non-human body movement.
We are using the dataset in the Excel file “XYScatterData.xlsx Download XYScatterData.xlsx” to practice different trendline settings.
(A) Position and time relation (100m sprint data):
Select “Time 1 (s)” and “Position 1 (m)” as your dataset to plot in the chart for this example.
Click the Insert tab, and then click X Y Scatter, and under Scatter, pick the 1st option “Scatter”, which plots your data with “markers” only.
With the chart selected, click the Chart Design tab to do any of the following:
Click Add Chart Element to modify details like the title, labels, and the legend as appropriate
Now let’s add a Trendline to the data (Adding Trendline (Windows) videoLinks to an external site.) and/or macOS – Excel HelpLinks to an external site..
Select Linear Trendline and display Equation and R-squared on your chart.
It is now clear to see that there is a direct linear relationship between Time (X axis) and Position (Y axis). As time passes the distance covered (position data) by the runner increases.
(B) Position and time relation (400m dash data):
Select “Time 2 (s)” and “Position 2 (m)” as your dataset to plot in the chart.
Insert XY Scatter following the same steps you did in example (A).
Now, because we are exploring a different dataset, and we know that this runner may have a different strategy, since it’s a longer sprint (400m). Let’s add two trendlines to the data.
Graph Trend Lines in ExcelLinks to an external site. https://www.youtube.com/watch?v=rBme0yo45Bo
Select the first five points (from 0 to 4s) as your line data (Linear Trend)
Select the remaining points (from 4.5 to 10s) as your curve data (Polynomial 2nd order Trend)
Display Equation and R-squared on your chart for both curves.
It is now possible to see that there is still a direct relationship between Time (X axis) and Position (Y axis), i.e. as one increases the other also increases. However at some point along the race the relationship changes because the runner is accelerating, so it’s no longer linear relation.
(C) Mass, acceleration and force relations:
Select “Mass (kg)”, “Acceleration (m/s2)” and “Force (N)” as your dataset to plot in the chart.
Insert XY Scatter as you did in (A).
This time we want to add a secondary axis to display force (on the right side second Y axis), because our Y variables (acceleration and force) have different ranges and we also want to find out what’s their relation to mass. (Adding a Secondary Axis (Windows) https://www.microsoft.com/en-us/videoplayer/embed/RE269IY?pid=ocpVideo1-innerdiv-oneplayer&postJsllMsg=true&maskLevel=20&reporting=true&market=en-us videoLinks to an external site. and/or macOS – Excel HelpLinks to an external site..) https://support.microsoft.com/en-us/office/add-or-remove-a-secondary-axis-in-a-chart-in-excel-91da1e2f-5db1-41e9-8908-e1a2e14dd5a9#OfficeVersion=macOS
Add Axes Titles and Legend to your chart, as appropriate.
Add Trendlines to both sets of data, i.e., (Mass vs. Acceleration) and (Mass vs. Force).
Display Equation and R-squared on your chart for both lines.
It is easy to see that there is a positive relationship between (Mass and Force), which means that as Mass increases, Force also increases. On the other hand, there is a negative relationship between (Mass and Acceleration), meaning that an increase in Mass will make Acceleration decrease.
Submit your finalized Excel file, showing (A), (B), and (C) scatter plots as your “Graphing (Excel 4)” assignment (see class schedule for due date).
There are other resources, check out video resources available in the “WATCH: Excel Scatter Plots” page.
WATCH: Excel Scatter Plots
Here are some video resources (there are many more resources available on the web – it all depends on what exactly you would like to learn and your needs for a particular set of data):
Quickly Add a Series of Data to XY Scatter Chart (MS Excel) Links to an external site.(5 min.) https://www.youtube.com/watch?v=hCAbxtkZoVg&t=8s
Adding a 2nd set of data (with different number of data points) to a scatter plotLinks to an external site.(3 min.) (by LBB). https://www.youtube.com/watch?v=rLZpyW6dWUI
Scatter Plot (Khan Academy)Links to an external site.(2.5 min.) https://www.youtube.com/watch?v=sHbX58y5D4U&ab_channel=KhanAcademy
**Additional Support: MS Office Support Page: Excel help & learning links to an external site.
