Making a scatter plot graph is fairly straightforward in Excel, but a few extra steps are necessary to change from the default plot into a decent format.
If you haven’t done so, go here to get some sample data to use.
Select your data
Select the two columns labeled Time (s) and Position (m) by placing your mouse on the cell labeled Time (s). Press and hold the left mouse button. While still holding the left mouse button, drag the mouse down to the lower right hand corner of the data set. Once there, all of the data, including the labels should be selected. Depending on your version of Excel, the cells may be dimmed and outlined. With the default settings for Word 2013, my selected cells are gray and outlined in green.
Data Selection
Make the initial graph.
In the menu list at the top of the Excel select the “tab” labelled “INSERT”. The ribbon will change and you will see several icons with descriptions near them. Move to the section that lists recommended charts. Look for the one that looks like several dots with no connecting lines. This is the scatter plot option, and a description should show up when you hold your mouse over it. Click on it and you should see the following…
Chart options in Microsoft Excel 2013
Choose the selection that has points but no lines. We will add a trend line later, and we want that to be a true mathematical description. The other options here will essentially just “connect the dots”. When your mouse is over the other options, you should see a preview of each type of graph. Once you have checked them out, click the option with no lines.
Add a quick layout.
After you click and create the graph, the ribbon has now changed to the Chart Tools – DESIGN option. To the left hand side, you will see an icon with a label “Quick Layout” choose the option in the upper left hand corner. This adds generic labels and a legend to your graph. We will move these around to suit our needs.
Alternatively you can use the button beside it, “Add Chart Element”. This allows you to add individual elements to your chart with several options for each.
If you don’t see the “Chart Layout” option, make sure that you have clicked on the graph. Microsoft Office’s Ribbon feature changes the menus based on your activity. If the graph isn’t selected, a different ribbon will be shown. Unfortunately, the ribbon is very context specific. Specific menus will appear and disappear depending on your selection. Keep this in mind as you navigate through the program.
Add the Proper Axis Labels
The data in the spreadsheet has the columns labeled already. When inputting your own data, be sure to add labels. You never know when you will need to look at old data a second or third time. Sometimes it might be several weeks, months, or years later. Labels will help you remember details about the data. Since these labels are already here, let’s make use of them.
Click on the x-axis label, and highlight all of letters in “Axis Title”. Now find the formula bar. This is located right above the “A, B, C, etc.” labels for the columns. There is an x symbol, a checkmark, and an f_x just to the left of it. Click in this box and a cursor will appear. Type =, and then click on the appropriate cell containing the axis title. In the data pictured, this is cell A2. (If you copied the data into a slightly different place, click on the appropriate cell.) Alternatively, you can type in the title manually.
Do the same for the y-axis label.
Add a trend line
Right click on one of the data points on your graph. Sometimes this is tricky, so you might have to click on an empty cell off of the graph, and then right click on the data point.
Once you have successfully right clicked on the data point, a context menu will appear. Select the option at the near the bottom of the list, “Add Trendline”. A “Format Trendline” menu will appear to the right hand side of the Excel Window. Depending on the nature of your data, you will select Linear, Exponential, Logarithmic, or one of the other choices.
The data in our example is representing motion with a constant acceleration, so we will choose “Polynomial”, and leave the order at “2”. This should make sense. The equation of motion for this type of situation is given by…
\(\color{black}{ y = y_{o} + v_{o} t + \frac{1}{2} a t^{2}}\)Further down the menu, you can add a custom name. I typically just give the fit a name of “Fit” or “Model”. Type in one of these, and then move down further and click on the boxes next to “Display Equation on chart”, and “Display R-squared value on chart”. We hope to extract information from this trendline, so we need to see the parameters on the chart.
If you have been following along, your graph should now look somewhat like this…
Slightly formatted graph of an object in free fall.
At this point, the scatter plot graph meets the minimum requirements for a graph, as we saw back here. It still needs work though.
In the next lessons, we will clean up this graph and make it even more readable. We will also use the trend line information to determine the acceleration of the object.
If you have any questions or comments, please use the form below.