Excel Instructions: Scatterplot
To open Excel with the data in the worksheet, click here and it will automatically download the file to your computer. Find the downloaded file (usually in the Downloads folder) and double-click it to open it in Excel.
As you can see, our dataset contains the following variables:
gender: 0 = male, 1 = female.
height: in inches.
weight: in pounds.
First we will create a scatterplot to examine how weight is related to height, ignoring gender.
To do that in Excel:
- Sort the data by gender:
- Hold down the Control key (Command key on MacOS) and click the A key to select all of the data in the worksheet.
- Select the Home tab, then the Editing group Sort & Filter -> Custom Sort.
- In the pop-up window, make sure that My list has headers box is checked and then choose gender from the pull-down menu next to Sort by. Click OK.
- Now select all of the data in columns B and C, select the Insert tab and in the Charts group choose Scatter.
- Choose the first scatterplot option (Scatter with only Markers).
- Now we have a scatterplot, but the data is all on the right of the plot. To fix this:
- Right-click on the x-axis, and choose Format Axis from the pop-up menu.
- Make sure that Axis Options is selected on the left, and then next to Minimum enter 50 in the textbox. Click the X button to close the menu.