Back
Use Excel to Make a Scatter Plot
> ... Math > Excel > Use Excel to Make a Scatter Plot

Introduction

Excel is a powerful tool for storing data and making graphs. In this lesson, you will learn one way to make a scatter plot in Excel. A scatter plot is a graph that plots the data points, but it doesn’t connect them with a line.


This video illustrates the lesson material below. Watching the video is optional.


Scatter Plot Basics

Scatter plots are pretty simple to make. Here are the basic steps for creating a scatter plot in Excel:

First, input the data into Excel. In column A, list the years, and in column B, list the church population in millions. In other words, each number in the column multiplied by a million will give you the total number of members in that year. This information will be used to create a scatter plot. The following steps are illustrated in Figure 1:

  1. Highlight the data including the heading of both columns you would like to plot.
  2. Select the Insert tab. This can be found in the top-left corner of the program.
  3. Find the Charts section.
  4. Select the scatter plot icon. It looks like a graph with a bunch of scattered dots.
  5. Edit axis range, axis title, chart title, and anything else you would like to adjust.
A number 1 is shown on a group of cells that are selected. Three circles that are numbered 2-4 are shown at the different places in the image. The circle labeled 2 is around the Insert menu, the circle labeled 3 is around the Charts icon, and the last circled labeled 4 is around the first chart option under Scatter.

Figure 1

The figure shows a chart titled, Church Population in Millions.

Figure 2

Scatter Plot Settings

There are many ways to edit a scatter plot. Look at the example below to determine what changes you can and want to make.

Notice that the visual is set up so the x-axis displays the years, separated in 20-year increments. The y-axis displays the population by millions, separated in 2-million increments. The graph has also been automatically titled: “Church Population in Millions.”

To edit the scatter plot’s title, select the title, which automatically makes the information editable. You can change the title to whatever you think best suits the scatter plot information.

A callout titled, Edit Title, is pointing at the Chart title.

Figure 3

Take a closer look at the axes. While they have been set up to match the data, they do not have labels. This makes the graph somewhat unclear for anyone trying to read the information. Follow the steps below to add axis titles:

  1. Select the graph by double-clicking on it.
  2. Select Add Chart Element, which is found at the top-left or top-right side of the page.
  3. Under Add Chart Element, select Axis Titles. The primary horizontal title refers to the x-axis, and the primary vertical title refers to the y-axis. Titles can be added to both axes.
  4. The default axis title will read, “Axis Title.” To change this, select the title and type a label which represents the information in that axis.
The figure shows number 1-4 across the image. Number one is situated in the middle of the chart, number two is right next to a circle that is around a plus sign icon, number three is next to a box that surrounds the title Chart Elements, and number four is next to a box that is around the Axis Titles option.

Figure 4

You can edit the tick marks on the graph by double-clicking on a number, which opens a pop-up window with the tick mark settings. Try starting with the x-axis tick marks. The marks are separated by 20 years. In this example, it makes more sense to separate the data by 10 years, since the data has been gathered every 10 years. Follow these steps to do so:

  1. Double-click on the years on the x-axis.
  2. In the Format Axis panel, change the major units to 10.
Number one is next to a box that surrounds the years that labels the data for x-axis, while number two is next to a circle that surrounds the Major option under Format Axis.

Figure 5

Notice that the scatter plot begins in 1900, but the data begins in 1907. In the Format Axis panel, you might consider changing the minimum bound from 1900 to 1907.

A callout labeled, Begin in 1907, is pointing at the first year in the x-axis. A circle is around the Minimum Bounds in the Format Axis.

Figure 6


Things to Remember


  • To make a scatter plot in Excel, highlight the data you want to graph, select Insert, and select the scatter plot icon.
  • To edit the graph, select the Add Chart Element option in the upper ribbon, or double-click items within the graph that you would like to edit.

Practice Problems

The following table summarizes data on the number of hours that students spent studying and the scores that they earned on their exams.

Hours StudiedExam Score
7.568
9.592
6.7579
1.7536
672
995
7.586
795
8.573
0.7548
Use Excel to create a scatter plot of these data, where the time spent studying is on the x-axis (the horizontal axis) and the test score is on the y-axis (the vertical axis). (
Solution
x
Solution:
This is a scatter plot with x-axis from zero to ten that represents hours studied and y-axis from zero to a hundred that represents the Exam score. The points on the scatter plot are (0.75, 48), (1.75, 36), (6, 72), (6.75, 79), (7, 95), (7.5, 68), (7.5, 86), (8.5, 73), (9, 95), (9.5, 92).
Details:
1. Type the information into Excel:
This is a picture of an Excel spreadsheet. There are two columns. The first has the number of hours studied. The second has the exam score for the corresponding hours.
2. Highlight both columns (How to Select Cells (01:07 mins) Transcript):
This is a picture of an Excel spreadsheet. There are two columns. The first has the number of hours studied. The second has the exam score for the corresponding hours. Both columns have been highlighted.
3. Click Insert, then Chart, then X Y (Scatter).
An Excel spreadsheet with columns for hours studied and corresponding exam score. The steps to make a scatter plot are first, click on the “Insert” tab in the Excel menu bar. Second, select “Chart” from the resulting menu. Third, select “X Y (Scatter)” from the next resulting menu.
4. You now have a chart!

Note: You can make adjustments to your chart if you would like. Try adding labels, colors, a legend, etc. by selecting the Chart Design tab in Excel.
)

    Need More Help?


    1. Study other Math Lessons in the Resource Center.
    2. Visit the Online Tutoring Resources in the Resource Center.
    3. Contact your Instructor.
    4. If you still need help, Schedule a Tutor.