Scatter Charts with Excel

Scatter Charts with Excel

Scatter Diagrams are known by many different names in English like scatterplot, scatter graph, scatter chart, scattergram, X-Y graph. Temporarily translated are scatter charts, X-Y graphs or correlation charts.

What is a Scatter Chart?

  • A Scatter Chart is a type of chart which was constructed by points with mathematical coordinates to determine the correlation between two variables. We will need to plot two sets of data on a graph, with the vertical axis Y used for the predicted variable (also called the dependent variable), and the horizontal X axis used for the variable that used to make predictions (also called independent variables). After determining this correlation, you can predict the outcome of the dependent variable based on the measurement of the independent variable.
  • Scatter Diagram is one of the legendary 7 Quality Tools (7 QC tools). For those who have just seen through this chart, it will probably make you a bit confused by the detailed image. It looks strange, but it's not that hard to understand, difficult to draw, or use compared to the familiar line and column charts that we often see. This topic will help you get familiar with distributed charts, as well as how to create and use this type of chart in a very intuitive way.

When to use Scatter Charts?

  • The simplest and most concise way is when you have two pairs of data (variables), and you want to determine if the two variables are related to each other or not?
  • Relevant, how much is involved, and how is it?
  • For example: In a study of factors (causes) that affect the rate of freezing of 1 liter of ice in the freezer compartment of the refrigerator. Brainstorming comes up with a list of potential (cause) factors, such as freezing temperature, water mass shape when freezing, contact area for freezing, refrigerator-size, etc.
  • At this time, the Scatter Chart will help you quickly see the temperature with pictures. Affect the freezing speed; the size of the refrigerator does not affect anything, etc.

Conclusion of correlation in Scatter Charts

  • There are many ways to divide the correlation between two variables. But the most general and summarized, we can classify into two main categories are YES correlated and NOT correlated. In the correlated type, we may divide it into two branches: positive and negative correlation. Based on the degree of relationship, firm association, weak correlation, etc. Will be clarified in the next section.
  • Regarding the correlation method, there is an R. R correlation coefficient, whose value runs from -1 to +1. In which, ±1 indicates two correlated variables. Absolutely and 0 shows that the two variables have nothing to do with each other. However, as mentioned above, this article will focus on understanding how to use distributed charts most intuitively. So we won't go deep into the correlation formula for R correlation here. Instead, let's find out how to conclude the correlation of a statement.
  • The Scatter Plot is based on the shape, slope, and focus points of the chart below. And the way to determine whether it is correlated or not is quite simple, simplified in item 4 through an illustrative example.

Based on the slope

  • Is considering the direction of change of the value of Y according to the increase of X. If Y increases when X increases, the two variables have a positive correlation (R> 0).
  • Also known as a positive slope. Conversely, if Y decreases as X increases, then the two variables have a negative correlation (R < 0). Also known as the negative slope

Based on the concentration of points

  • Considering the extent of dispersion points in the graph:
  • If the dots are close together, we can draw a line through them. We can say that the correlation is robust (R is close to ±1). At this point, controlling X means you can fully control Y.
  • If the dispersion points are vast, the relationship will be weak, which means that the change of X will lead to the shift of Y. However, Y also depends on other factors/causes.
  • Where the points in the random spread chart do not follow any rules, it means that the two variables are not related to each other (R = 0).

How to analyze the visual chart of a scatter chart.

How to

  • In some cases, for many reasons, at first sight, the two variables seem to be correlated. But actually, they have no relationship with each other and vice versa. Or looking at your focus is hard to tell if there's a good correlation is not? So, this section will share on how to analyze a simple scatter chart without going through the correlation coefficient calculation. This method is appropriate and convenient for the case of your data set is not too much.
  • Step 1: Collect data of the pair of variables you want to determine whether there is a correlation or not
  • Step 2: Draw a chart with independent variables on the X-axis and the dependent variable on the Y-axis
  • Step 3: Look at the point model to see if the relationship is clear. If the scatter points on the chart form a clear line, you can stop because you were able to find correlations between variables. If not, continue with the following steps:
  • Step 4: Divide the area for the points on the chart into four quadrants. If there are x points on the graph:
  • Count x / 2 points from top to bottom and draw a horizontal line
  • Count x / 2 points from left to right and draw a vertical line
  • If the number of points x is odd, draw a horizontal/straight line through the middle point.
  • Step 5: Count the number of points in each quadrant. If the line intersects in the case of an odd score, we won't count any points.
  • Step 6: Consider the opposite quadrants in the diagonal direction. Find the smaller sum and the total score in all quadrants
  • A = point in the upper left quadrant + the point in the lower right quadrant B = the point in the upper right quadrant + the point in the lower left quadrant
  • Q = smaller value in the result of A and B
  • N = A + B
  • Step 7: Look up the limit for N in the trend checker table

Create a Scatter Chart in Excel with Excel Office Component

  • So we have a rough look at the features and uses of the Scatter Chart with Excel. Perhaps now you will want to create a Scatter Chart for yourself. However, creating a Scatter Chart with Excel on the .NET platform will have a lot of difficulties when it is necessary to use a lot of different knowledge and tools.
  • No need to worry anymore cause you now have the full support of the Excel Office Component, making it easier than ever to create charts with Excel.
  • We will show you how to create yourself an Excel Scatter Chart step-by-step, so please stick with us ;)
  • First, please create your own Dataset, which is an original Excel file, and we will proceed to draw a Radar chart based on this amount of data. To do this with the .NET platform, just follow these simple steps:
  • C# Version:
sheet.Range["B3"].Text = "Precipitation,in.";
sheet.Range["C3"].Text = "Temperature,deg.F";
sheet.Range["A4"].Text = "Jan";
sheet.Range["A5"].Text = "Feb";
sheet.Range["A6"].Text = "March";
sheet.Range["A7"].Text = "Apr";
sheet.Range["A8"].Text = "May";
sheet.Range["A9"].Text = "June";
sheet.Range["A10"].Text = "July";
sheet.Range["A11"].Text = "Aug";
sheet.Range["A12"].Text = "Sept";
sheet.Range["A13"].Text = "Oct";
sheet.Range["A14"].Text = "Nov";
sheet.Range["A15"].Text = "Dec";  
sheet.Range["B4"].Number = 10.9;
sheet.Range["B5"].Number = 8.9;
sheet.Range["B6"].Number = 8.6;
sheet.Range["B7"].Number = 4.8;
sheet.Range["B8"].Number = 3.2;
sheet.Range["B9"].Number = 1.4;
sheet.Range["B10"].Number = 0.6;
sheet.Range["B11"].Number = 0.7;
sheet.Range["B12"].Number = 1.7;
sheet.Range["B13"].Number = 5.4;
sheet.Range["B14"].Number = 9.0;
sheet.Range["B15"].Number = 10.4;  
sheet.Range["C4"].Number = 47.5;
sheet.Range["C5"].Number = 48.7;
sheet.Range["C6"].Number = 48.9;
sheet.Range["C7"].Number = 50.2;
sheet.Range["C8"].Number = 53.1;
sheet.Range["C9"].Number = 56.3;
sheet.Range["C10"].Number = 58.1;
sheet.Range["C11"].Number = 59.0;
sheet.Range["C12"].Number = 58.5;
sheet.Range["C13"].Number = 55.4;
sheet.Range["C14"].Number = 51.1;
sheet.Range["C15"].Number = 47.8;
sheet.UsedRange.AutofitColumns();
  • VB Version:
sheet.Range("B3").Text = "Precipitation,in."
sheet.Range("C3").Text = "Temperature,deg.F"
sheet.Range("A4").Text = "Jan"
sheet.Range("A5").Text = "Feb"
sheet.Range("A6").Text = "March"
sheet.Range("A7").Text = "Apr"
sheet.Range("A8").Text = "May"
sheet.Range("A9").Text = "June"
sheet.Range("A10").Text = "July"
sheet.Range("A11").Text = "Aug"
sheet.Range("A12").Text = "Sept"
sheet.Range("A13").Text = "Oct"
sheet.Range("A14").Text = "Nov"
sheet.Range("A15").Text = "Dec"
sheet.Range("B4").Number = 10.9
sheet.Range("B5").Number = 8.9
sheet.Range("B6").Number = 8.6
sheet.Range("B7").Number = 4.8
sheet.Range("B8").Number = 3.2
sheet.Range("B9").Number = 1.4
sheet.Range("B10").Number = 0.6
sheet.Range("B11").Number = 0.7
sheet.Range("B12").Number = 1.7
sheet.Range("B13").Number = 5.4
sheet.Range("B14").Number = 9.0
sheet.Range("B15").Number = 10.4
sheet.Range("C4").Number = 47.5
sheet.Range("C5").Number = 48.7
sheet.Range("C6").Number = 48.9
sheet.Range("C7").Number = 50.2
sheet.Range("C8").Number = 53.1
sheet.Range("C9").Number = 56.3
sheet.Range("C10").Number = 58.1
sheet.Range("C11").Number = 59.0
sheet.Range("C12").Number = 58.5
sheet.Range("C13").Number = 55.4
sheet.Range("C14").Number = 51.1
sheet.Range("C15").Number = 47.8
sheet.UsedRange.AutofitColumns()
  • To create a new Excel file for storing output data - that is, a Scatter Chart, you can use the WorkbookManager class in our Excel Office Component library.
  • C# Version: WorkbookManager manager = new WorkbookManager();
  • VB Version: Dim manager As New WorkbookManager()
  • Once you've created a WorkbookManager, you can add new workbooks as well as specify the version of the newly created workbook.
  • Add a new workbook
  • C# Version: Workbook workbook = manager.Workbooks.Add();
  • VB Version: Dim workbook As Workbook = manager.Workbooks.Add()
  • Declare a new version
  • C# Version: workbook.Version = SaveAsFormat;
  • VB Version: workbook.Version = SaveAsFormat
  • You can then create yourself a new sheet:
  • C# Version: IWorksheet sheet = workbook.Worksheets[0];
  • VB Version: Dim sheet As IWorksheet = workbook.Worksheets(0)
  • After this step, everything is ready; you can now create your chart with the necessary attributes.
  • Add a new Chart
  • C# Version: IChart chart = workbook.Charts.Add();
  • VB Version: Dim sheet As IWorksheet = workbook.Worksheets(0)
  • Declare properties
  • C# Version:
IChart chart = workbook.Charts.Add();
chart.Name = "Radar Chart";
chart.ChartTitle = "Radar Chart - Sales by Region";
chart.DataRange = sheet.Range["A3:C15"];
>> - **VB Version:**
Dim chart As IChart = workbook.Charts.Add()
chart.Name = "Radar Chart"
chart.ChartTitle = "Radar Chart - Sales by Region"
chart.DataRange = sheet.Range("A3:C15")
Select chart category and location:
  • C# Version:
switch (Type)
{
	case 0:
		chart.ChartType = ChartType.Scatter_Line;
		break;
	case 1:
		chart.ChartType = ChartType.Scatter_Line_Markers;
		break;
	case 2:
		chart.ChartType = ChartType.Scatter_Markers;
		break;
	case 3:
		chart.ChartType = ChartType.Scatter_SmoothedLine;
		break;
	case 4:
		chart.ChartType = ChartType.Scatter_SmoothedLine_Markers;
		break;
}

chart.IsSeriesInRows = true;
chart.Legend.Position = ExcelLegendPosition.Bottom;
chart.Legend.IsVerticalLegend = false;
  • VB Version:
Select Case Type
	Case 0
		chart.ChartType = ChartType.Scatter_Line
	Case 1
		chart.ChartType = ChartType.Scatter_Line_Markers
	Case 2
		chart.ChartType = ChartType.Scatter_Markers
	Case 3
		chart.ChartType = ChartType.Scatter_SmoothedLine
	Case 4
		chart.ChartType = ChartType.Scatter_SmoothedLine_Markers
End Select
chart.IsSeriesInRows = True
chart.Legend.Position = ExcelLegendPosition.Bottom
chart.Legend.IsVerticalLegend = False
  • And with the Activate() command, your Radar chart creation is complete.
  • C# Version: chart.Activate();
  • VB Version: chart.Activate()

The full source code of this example is also available in our Excel package.

A live demo for Scatter Charts is also available on our site. If you also need Excel functionality, check out our Excel online demos.

45-Day Money Back Guarantee

We will refund your full money in 45 days
if you are not satisfied with our products

Buy Now
You have successfully subcribed to our mailing list.
Dont miss out Get update on new articles and other opportunities Subscribe