Lean TrainingSite Map Lean Principles Muda 8 Wastes Goal - Lean Flow Roadmap - Lean Journey Hansei Lean Thinking Lean Dictionary Online Lean Training Private Training
Systems2win TrainingQuick Start Initial Training Systems2win Leadership
CustomersOperational Excellence Lean Manufacturing Lean Office and Service Lean Healthcare Customer List
aka x-y graph, scatter diagram, scattergram, scatter graph, scatter chart
A scatter chart graphically shows the relationship between two sets of data.
It can also be used to determine whether the two variables might be correlated.
Use your Excel Scatter Plot template to determine whether two sets of numerical data might be related
Sample Scatter Plot example
How to use your
Find and open your template
Find and open your Scatter Plot template
in the same way that you find and open your other 150+ Systems2win templates.
Save your working document
following the usual document storage and naming conventions established by your leaders
Open a Blank Sheet
When you're ready to start doing your own real work...
click the button to 'Open a Blank Sheet'
Excel Ribbon > Systems2win tab > Open a Blank Sheet
This blank sheet is where you will do your real work
(not on the Sample 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
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
Brief description of the primary purpose of this document.
The Title that you enter here will also appear as the Title in your scatter chart.
The Author is the one person (or team) authorized to make changes to this document.
To change date format: Right-click > Format Cells > Number tab
You'll get a lot more out of this training if you have your template open in front of you
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.
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.
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'
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.
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.
Your Systems2win menu includes a special utility to 'Copy Chart' that copies a picture of your chart to the clipboard, where it can then be pasted to any other document.
(which might be Excel, Word, PowerPoint, etc.)
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.
The correlation formula is the Pearson product moment coefficient,
and you can look that up on the Internet to get detailed explanations of exactly how it is calculated and why...
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...
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
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.
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:
Suggested Reading and Resources for your
See ExcelFunctions.net to learn more about how to use Excel functions
Another template similar to the Scatter Diagram is the Quadrant Chart
to empower every team member
Download Free Trial
If your organization has
not yet provided a license,
download your free trial now
New User Training
Your Systems2win templates come with free New User Training
to quickly learn features that are common to all 150+ templates