Excel Scatter Plot template

aka x-y graph, scatter diagram, scattergram, scatter graph, scatter chart

When to use an X-Y Graph Scatter Diagram

Use your Excel Scatter Plot template to determine whether two sets of numerical data might be related

• Use it to brainstorm potential causes when performing Root Cause Analysis
• Use it to scientifically determine whether suspected causes and effects are closely related
• Use it to determine whether two effects that appear related both happen with the same cause

Sample Scatter Plot example

---

how

How to use your

Excel Scatter Plot template

Find and open your template

Save your working document

following the usual document storage and naming conventions established by your leaders

Open a Blank Sheet

Rename your new sheet.

Or... Insert Sheet

As an alternative to opening a stand-alone document (as instructed above), you also have the option to Insert Sheet into any other Excel workbook.

(which also gives you the option to Link Data so that your chart instantly changes as your data changes)

If English is not your preferred language

Switch to your language, just like every Systems2win Excel template.

---

Now your team is ready to start using your

X-Y Graph Scatter Diagram

Enter Titles for your X and Y data

In the pink cells at the top of the page above the Print Area, enter a Title for the horizontal X Axis, and a Title for the vertical Y Axis.

The Titles that you enter in these cells will also appear in the clear text boxes above the gold column headers for X and Y, and as the Axis Titles in your scatter graph.

Enter header data

Title

Header Data

Any data that you want at the top of your document.

Tip: Hide unused rows. Or copy this row for unlimited user-defined header data.

Optional Comments Text Box

You can hide or unhide rows for the Comments text box.
You can leave the Comments text box where it is, or optionally move those rows anywhere else.
You can copy those rows for additional Comments text boxes.
Comments text boxes can be within the Print Area, or outside of the Print Area.

Enter your X-Y Graph data

In the X and Y columns, enter your numerical data.

Each row must have a matched pair of data for both X and Y.
In other words, each column must have the same number of data elements.

labels

Optional Descriptions

Description is optional, (and often not used).

If your description is a date, then you can change the cells to Date format.
Right-click > Format Cells > Number tab

If you want to show your Descriptions as data point labels on your chart...

then use the special button

In the Systems2win menu in the Excel Ribbon bar, select 'Apply Chart Labels'

Optionally format your Scatter Chart

Select your chart, and then in the chart-related tabs that appear in the Excel Ribbon bar, you can use everything that you know (or learn) about familiar Microsoft Excel to format your chart anyway that you like it.

Tip: If you want your chart labels to show X or Y data, rather than Description...
select 'None' for Chart Labels to remove the Descriptions and start fresh.

Analyze your Scatter Plot Diagram

The chart will instantly change as your data changes.

Tip: You can use everything you know (or learn) about Excel Charts to change colors, styles, fonts, etc.

Look for a pattern in the data points.

---

analysis

How to use your Scatter Plot template for

Correlation Analysis

Just below the header section and above the scatter chart, you can optionally hide or unhide rows for the Analysis section.

As delivered... there is only one formula in the Analysis section... the formula for Correlation because we believe that for most users, (who are not statisticians), that is the only formula that is needed.

You don't need to understand the Correlation Formula

bur our philosophy is that you don't need to learn how to build a power plant in order to plug into an electric socket

All that you need to know is...

How to interpret the results of the Correlation Analysis

If you speak English, then think of 'r' as 'relationship'

Option 1) Positive Correlation

If r is positive, then there is a positive correlation.

The more that one thing happens, the more that the other will happen too.

A small number means a weak correlation.

Sometimes the other thing happens; sometimes it doesn't.

If r is +1, then there is perfect (maximum) positive correlation.

Every time that one thing happens, the other will always happen.

Important Reminder: Correlation is not the same as 'cause and effect'.

Just because two things happen together (or opposite of each other) doesn't mean that one causes the other.

There might be some other outside factor (or factors) that influence both of the things you are studying.

Option 2) Negative Correlation

If r is negative, then there is a negative correlation.

The more that one thing happens, the LESS that the other will happen.

If r is -1, then there is perfect (maximum) negative correlation.

Every time that one thing happens, the other will NOT happen.

Option 3) No Correlation

If r is zero, there is no correlation.

There seems to be no relationship between how often the two things happen.

Option 4) #N/A Error

There are a different number of values in the 2 columns.

Solution: Every value in one column needs a matching value in the other.

Option 5) #DIV/0! Error

Either one of the columns is blank, or all of the values in a column are identical (so the standard deviation of the values equals zero)

Solution: Change one of the identical values to be slightly different.

Example: If all of the values are 6, change one to 6.0001

That's all that most users want (or need) to know

1. Is there a Correlation between the two factors?
2. Is it a positive or negative correlation?
3. How strong or weak is it?

---

More Correlation Analysis

Regression Analysis

In the Analysis section, you can optionally add an unlimited number of user-defined rows to use everything that you know (or learn) about Microsoft Excel functions to further analyze your data.

For example...

If, in your Black Belt training, you learned how to use Excel's LINEST() function, then you can easily analyze your data for all of the regression analysis data that is generated by that Excel function

which uses the Least Squares method to return a whole slew of numbers that only trained statisticians or Black Belts will understand

LOGEST() is similar to LINEST

but for analyzing values that fit an exponential curve, rather than a linear relationship

TREND() for linear or GROWTH() for exponential trends

INTERCEPT() and SLOPE() for more statistics that require special training to understand

We could have chosen to pre-program all of those confusing statistics, but...

Your Systems2win scatter plot template is designed to be easy

Easy enough for a Green Belt to use.
Easy enough for a non-statistician to use.

For 99% of users, the (much simpler) Correlation Analysis provides information very similar to Regression Analysis
in a way that is much easier to understand.

In those rare cases when you believe that your scatter graph data deserves further analysis...

you can either:

1. Go for it... using everything that you know (or learn) about familiar Microsoft Excel
2. or seek the assistance of your Black Belts to perform any one of 25 different types of Regression Analysis

---

Reading

Suggested Reading and Resources for your

Excel Scatter Plot template

---

---

This scatter plot template is one of the 7 Basic Tools of Quality

 

 

to empower every team member to improve ever process