Thank you for subscribing!

It's great to feel loved.

How to Make a Scatter Plot in Microsoft Excel

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:

  1. Positive correlation. If there is a line from x and y origin to the top, it is seen as a positive correlation
  2. 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.
Positive correlation positive   Negative correlation excel

 

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 To Make A Scatter Plot In Excel    

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:

  1. 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.
  2. 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.

  3. 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.

  4. 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.

Excel reporting tool

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:

  1. 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.

  2. 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.

  3. 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)

  4. 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.

  5. 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.

  6. 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.

  7. 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.

  8. 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.

Conclusion

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. 

Mike Bennet
Written By Mike Bennet
whatagraph
How to Group Worksheets in Microsoft Excel
What if you want to write the same data, make the same changes or apply the same action at multiple worksheets at the same time in Excel? Microsoft Excel makes it easy to group worksheets, and you do not have to waste your time and work on them individually.
whatagraph
How to Edit Legend in Microsoft Excel
Excel is a software that permits its users to organize and calculate data with multiple formulas using a spreadsheet. Microsoft Excel aids users create charts that are meaningful to visualize their spreadsheet data by automatically inferring some of the chart elements. What about Legends?
whatagraph
Best Microsoft Excel Alternatives
Excel is an important tool in everyday life. It entails various features that help in storing information, arrangement, and processing. It is an app specialised in project management and data analysis. But then Excel has been far overhyped in its functions and tasks can be carried out by other Excel alternatives.
In this PPC audit guide, we’ll walk you through the methods to set up your PPC audit while also providing a handy step-by-step PPC audit checklist. With these audit checklist, you’ll have an insight into the areas that need improvements, expansion, and advanced optimization.
Read more...
Mike Bennet
May 27, 2020 4 min read
If you can analyze your search term reports regularly or periodically, you’ll be sure that you aren’t wasting your advertising dollars on irrelevant search queries. Apart from that, you’ll also discover new keyword data and opportunities that you can incorporate into your campaigns.
Read more...
Wendy Gooseberry
May 27, 2020 2 min read
Sending one message to your entire subscribers consistently is an act that has the potentials of diminishing your brand reputation. Apart from that, it depletes an email list of your subscribers as well as halt conversions.
Read more...
Gintaras Baltusevicius
May 26, 2020 3 min read