How to Make a Scatter Plot in Microsoft Excel
Dec 12, 2019 ● 6 min read
Scatter graph is another word for scatter plot and they are related to line graphs. A scatter plot uses dots for the representation of data pieces while line graphs just use lines X and Y. Statistically, scatter plots are used to identify if two variables are related in any way. For this reason, you need to know how to do a scatter plot in Excel.
This knowledge will help you to identify and establish linear relationships between distinct variables. If a relationship is found in scatter plots, it is called correlation. Two types of correlation are identified here, these are:
- Positive correlation. If there is a line from x and y origin to the top, it is seen as a positive correlation
- Negative correlation. If the data points start from high x and y and end up at a lower point, it is seen as a negative correlation.
For the generally known regression Analysis to be performed in Microsoft Excel, most of the time you cannot do without scattering plot. The scatter plot, therefore, helps to clearly explore a relationship between two recorded sets of values. It is done by using a scatter chart XY.
A good example would be to analyze a set of data ranging between C2: C13 which shows the number of values while the worksheet ranges between D2: D13 which shows its recorded sales. With this kind of data, you should find it easy to evaluate the cause and effect of those values to its recorded sales.
How Do You Construct a Scatter Plot
To create a scatter plot in Excel for some set of data recorded, some steps have to be followed:
- You have to select the worksheet chart range if C2: D13 and click on the XY scatter chart which can be found on the Insert tab, you then have to choose a chart subtype that does not include lines.
When Excel has finally displayed the scatter chart, you will have to confirm the data organization of the chart, and this is done by looking with the intent on the chart to be sure that it is displaying in the right way it should. If it is not arranged properly, then you will have to do more work to set it right.
This step is done by clicking on the switch row and column button on the Design tab's tools. Then, you should be able to do some creative work on arranging and making it rightly set, this can also help your learning process.
Now, you can move on and annotate the created chart, if that is what you want, these little additions can make the chart very presentable.
Now It will be right to add a trend line. This is done by clicking on the add chart menu button but to display this, you have to go click on the Design tab and then the add chart command. For the design tab to show though, you must have selected a hidden chart or clicked on a chart sheet.
You have to pick the exact trend line that is okay for you by clicking on the available options displayed, you have to choose what works for you.
As an example, after you have fully understood how to make a scatterplot in Excel and you have decided that you want a linear regression, you have to click on the Lines icon.
How Do I Plot Data in Excel
The right knowledge on how to create a scatter plot in Excel is important. To some people, this is their work. Particularly, plotting of data or graph using Excel. Graph plotting is a very good way to analyze and view data in a clear and easily understandable way. The way Excel is used to plot data or graph is quite simple:
You should enter your Data: Definitely, using Excel to plot graphs cannot work if no data is inputted, so you have to transport or create your newly recorded data for input into the software, so the real work can be started.
You should decide on which of the available graph options you want: Using Excel, there are numerous graph or chart options to use and create your desired graph. We have bar graphs, pie charts, scatter plots, linear graphs, and more. It is within your right to know which of them will be useful in depicting your data, so when you have decided, you click.
Data highlighting and Graph insertion: Since you have picked the chart to work with, then you have to input your data variables into the graph(X and Y)
Data switching, when necessary: There are times when you need to switch your data between Y and X, you can just right-click on the graph, tap on select data and click on 'Switch Row/Column.' There will be an automatic rearrangement between the axes.
Layout adjustment: For changes in legend and labeling, click on the graph, and then move to the chart design. You can easily choose whatever layout you really prefer to work with on your project.
Size of chart legend and labels: Size of the Legend labels and also the axis may be too small, depending on the chosen chart, you should find it easy to change this factor if you so desire.
Measurement options, y-axis: If there is a need to increase or reduce the y-axis, it can easily be changed to fit your liking.
Data re-order: When there is a need to reorder your data you can simply highlight your data in the cells on the top of your chart, just click on 'data' and choose 'sort', that will do the magic but first they have to be arranged in descending or ascending order.
Although it seems technical from afar you will agree with me that it's quite easy, plotting of graph is one sure way of representing data in a presentable and easy to digest way. In case you are struggling with Microsoft Excel reporting tasks try Excel integration with Whatagraph to get fast and userfriendly reports.