Creating a Scatter Chart

The XY chart, sometimes called a scatter chart, is one of the most commonly used charts for technical data. An XY chart differs from most charts in that it has values along both axes; the data points are the intersections between the two data sets. There is no category axis. This type of chart compares at least two sets of values or pairs of data and is used to show a relationship between sets of values.

Step1. To switch to the appropriate worksheet,

Click the Scatter Chart worksheet tab

We have data compiled from the University of the Midwest regarding workshop evaluation responses. For six months, workshop data was tracked to determine if there might be a correlation between the number of participants who thought the instructor answered questions adequately and the number of participants who thought the workshop was satisfactory.

We will create an XY Scatter chart using Excel's charting features. Then we will create a trendline and determine the equation that governs this line.

Charting the Data

When charting this data, as with any chart, selecting only the desired data is critical to success. We are not always restricted to selecting data from the worksheet with its column labels. Here, we may want to select the values from both columns, but not include the column labels in the selection. The reason for possibly omitting the column labels is we can assign the labels later, in the chart, and use more appropriate text for this chart than those used as column labels. So for this Scatter chart, we will omit the column labels from our selected data.

Step1. To select the appropriate data,

Press & Drag from B3 to C8

The data is selected.

Step2. To switch to the INSERT tab, on the Ribbon,

Click the Insert tab

We will initially create a scatter chart that displays markers.

Step3. To select the appropriate chart type, in the Charts group,

Click,

Click the first Scatter chart

Step4. To move the chart, if necessary,

Press & Drag the chart below the data

Adding Titles and a Trendline

This chart needs a descriptive title and axis labels that reflect the purpose of each axis. This type of chart will not need a legend.

Trendlines are used to graphically display trends in existing data and to help predict forecasts of future values. A trendline can be added to any data series in an unstacked, 2-D area, bar, column, line, or scatter chart. The default trendline is linear, and the data points will show an increasing or decreasing line at a steady rate.

Excel calculates such a line based on the experimental data, and adds it to the chart. Essentially, the line expresses the "trend" of the data, and answers the question, "Is the trend favorable or unfavorable?"

We will use a predefined layout to add title placeholders and the trendline. This will save us some steps.

Step1. To choose a predefined layout, on the Design tab, in the Chart Layouts group,

Click

NOTE for MacOS Users: If the chart is selected, the correct command tab on the Ribbon is named the Chart Design tab.

We will choose the layout with a trendline and the title placeholders already in place.

Step2. To choose a layout,

Click Layout 9

Notice how this chart differs from a line chart; the trendline does not precisely intersect with all the data points. Instead, the line is a mathematical construction based on the aggregate data set. The equation in the form of y = mx + b (where b is the Y intercept) appears on the chart. The R² value also appears in a text box on the chart.

The accuracy of the trendline's interpolative and extrapolative data (and the equation that describes it) depends on how well the trendline "fits" our data. This "fit" can be measured by calculating the R² value. A trendline is most reliable when its R-squared value is at or near 1. The R² value of 0.991 indicates that our trendline is a very good fit to the data. It is, therefore, apparent that there is definitely a strong relationship between the instructor answering questions adequately and participants rating the workshop as satisfactory.

NOTE: Since a trendline is a mathematical construction, it can be extrapolated to predict values outside of its data set as well. Essentially, extrapolation estimates the values of a series, and instead of "filling in" the missing data points, it can extend the data forward and/or backward.

The placeholders for the chart title and axis titles are in place. The legend is not necessary so we will remove it from the chart.

Step3. To remove the legend from the chart,

Click the legend, press: Delete key

The legend disappears from the chart, and the plot area expands automatically.

Next we will add descriptive titles to the chart.

Step4. To select the chart title placeholder,

Click the chart title placeholder

Step5. To position the cursor correctly,

Click in the formula bar

Step6. To add descriptive text, type:

Participant Responses Enter

The title is added to the chart.

We will add the horizontal axis title by linking it to the contents of cell B2.

Step7. To begin to add the horizontal axis title,

Click the horizontal axis title placeholder, Click in the formula bar, type: =

Step8. To add the horizontal axis title,

Click cell B2, press: Enter

The horizontal axis title is linked to the appropriate cell.

We will add the vertical axis title by linking it to the contents of cell C2.

Step9. To begin to add the vertical axis title,

Click the vertical axis title placeholder, Click in the formula bar, type: =

Step10. To add the vertical axis title,

Click cell C2, press: Enter

The vertical axis title is linked to the appropriate cell.

Next we will move the equation text box to the bottom right corner of the chart and bold the text.

Step11. To move the equation, with the four-sided arrow cursor,

Press & Drag the equation to the lower right corner of the chart

Step12. To make the equation appear in a bold font style, with the text box selected, press:

Control key+b

NOTE for MacOS Users: To make the equation appear in a bold font style, with the equation text box selected, press: Control key+Click the text box, Click Font... , the Font dialog box opens. In the Font Style drop-down list, Click Bold.

We can use the mini-toolbar to make format changes to the textbox.

Step13. Change the color of the equation text box font, if desired.

Step14. To display the transparent toolbar,

Right-Click the equation text box

A mini-toolbar appears.

Step15. To change the color of the border of the equation text box,

Click, Clickany color

NOTE for MacOS Users: To change the color of the border of the equation text box, with the equation text box selected, Click the "Shape Outline" tool on the Format tab of the Ribbon.

Step16. Add a fill color to the text box, if desired.

Step17. Deselect the text box.

Step18. Save the workbook ChartData.xlsx.

Adding Vertical Gridlines

Many XY charts have significant numeric data. Thus, being able to see interior gridlines for one or both axes makes the data easier to read.

In this case, we will add vertical gridlines to the chart and remove the minor horizontal gridlines.

Step1. To add major gridlines, to the right of the chart,

Click, Click,

Click the "Primary Major Vertical" checkbox

NOTE for MacOS Users: To add major gridlines, select the chart, on the Chart Design tab in the Ribbon, Click Add Chart Element, Point to Gridlines, Click the Primary Major Vertical option.

Step2. To toggle off the minor horizontal gridlines,

Click the "Primary Minor Horizontal" checkbox

NOTE for MacOS Users: To toggle off the minor horizontal gridlines, on the Chart Design tab in the Ribbon, Click Add Chart Element, Point to Gridlines, Click the Primary Minor Horizontal option.

Step3. To close the Chart Elements menu,

Click outside the chart area

At this point, the chart will look something like this:

The horizontal and vertical gridlines are present in the chart, but they do not interfere with the visibility of the data or trendline. Next we will adjust the values for the horizontal and vertical axes.

Adjusting the Horizontal Axis

Excel has set the minimum and maximum scale values of the horizontal and vertical axes to 0 in this chart. As a result, we have a lot of "dead space" that can be eliminated.

Since we have no values below 200, we will first change the minimum horizontal value to 150.

Step1. Select the chart.

Step2. To begin the process of formatting the horizontal axis, to the right of the chart,

Click, Click,

Click More Options...

NOTE for MacOS Users: To begin the process of formatting the horizontal axis, on the Chart Design tab in the Ribbon, Click Add Chart Element, Point to Axes, Click More Axis Options...

TheFormat Axis pane is displayed to the right of the worksheet, showing controls for the horizontal axis:

You will also note that the horizontal axis is selected on the chart, which is why the pane is displaying the horizontal axis controls.

We will change the new minimum value to 150.

Step3. Open the Axis options, if necessary.

Step4. To adjust the minimum value, in the Minimum field of the Bounds section,

Press & Drag the Minimum value, type: 150 Tab key

We will leave the Format Axis pane open and adjust the vertical axis minimum value.

Adjusting the Vertical Axis Minimum Value

Since the vertical axis data don't include any values before 198, we will adjust the vertical axis to begin with the minimum value of 150.

Step1. To select the vertical (value) axis, in the chart,

Click anywhere in the vertical axis

NOTE: If you Click the Format tab, at the far left of its Ribbon, you can easily select or verify that the vertical axis is selected in a drop-down menu. Certain elements of a chart may be easier to select using this drop-down menu.

The Format Axis pane displays the appropriate options.

The Y axis is built on increments of 50. We will adjust the minimum value for the vertical axis to 150, and then change the increment value to 25.

Step2. To adjust the minimum value, in the Minimum field in the Bounds section,

Press & Drag the Minimum value, type: 150 Tab key

Step3. To change the increment value to 25, under Units, in the Major field,

Press & Drag the value, type: 25 Tab key

Step4. Close the Format Axis pane.

You should see:

5. Save the workbook ChartData.xlsx.

Now we want to emphasize a particular data point on this chart. We will create a callout shape with text, to direct viewers attention to that.