If there is something that should be essential in all the integral formation of a person, it is to know handle Excel tools. And not only to know how to make a graph, we mean all possible functions that are within the scope of learning possibilities. Like a scatter plot in Excel. Either in its installed version or in its online version, where you can easily create spreadsheets.
In Excel the The possibilities are multifaceted. As advanced line charts or intermediate functions like dual axis charts. However, it is not possible to cover all the aspects in these very modest lines, so we will focus on creating a chart or scatter plot.
You are in the right place to learn how make a scatter plot or graph on a spreadsheet Standing out. And for this we are going to break it down below.
How to format the data correctly?
Excel is a calculation tool that covers many areas, being used around the world by people and companies. In the case of table creation, graphs and diagrams offers power option format the data, according to our need. And doing it is not complicated at all.
To give the correct format we will only have to select the cells that contain the data, then in the Home tab you will click on 'Format’. That way you can give it style, form and all the appropriate format to what you want to do.
Is it better to use a scatter chart or a line chart?
Much can be said on this topic, but it will be necessary to adhere to the mathematical field to give a correct conceptualization. The element consists of two graphics, line graph and scatter graph.
Now the line chart will be a representation on a Cartesian coordinate axis where different values will be reflected for their study and / or analysis. For his part, the so-called scatter plot is used when there is a degree to which the values of the sets differ, but valued based on their average value.
A scatter plot will be a representation of two variables, within a graph of Cartesian nature, where to analyze a data cluster. These can be similar to each other by a common point.
In answering the question posed, must first be considered what types of data are you going to work with to be able to make a decision. If the data does not vary, or in other words, they are consistent (a dependent value follows a pattern as the independent values vary), so it is better to use a line graph.
On the other hand, whether the data varies positively or negatively (an independent value can produce a larger or smaller dependent value), then the ideal is use a scatter plot. That way the data can be better represented, avoiding getting a line graph that is not consistent.
In summary, scatter plots are used to represent statistical data, scientific and engineering. This is because it allows for better analysis by being able to modify the chart axes as needed.
How to create a great scatter chart?
Excel is famous for greatly facilitating the work of users, That is why we refer at the beginning to the importance of knowing how to use your tools. Be able to do or create a chart or scatter plot on a spreadsheet in excel, it will be extremely simple. This program has everything preconfigured just to add information.
Select the two columns of data and the type of chart.
You must first open Excel to access your spreadsheet. Once inside the file, you will select all the data that you want to be part of the graph. You must remember to enter them in pairs or trios, depending on what you want to create. Then, in the toolbar, click on the 'Insert' section.
We are going to refer to the space dedicated to graphics, which is ‘Graphics’. There you will see your category, distributed according to the functions to be performed. Inner section 'Dispersion’ you will select ‘Disperse only with markers’. It is done this way because correctly a diagram or scatter plot does not have lines.
Once chosen, the interface show model that was just selected, who is going to insert it into the spreadsheet, for editing.
Select ‘Calc’ and add the trend line
To add a trend line, typical of a scatter plot, you will need to right-click on a data on the chart. When the menu is displayed, will select ‘Add trend line’ and immediately a window will appear with the available options. You can choose the one that best suits your data and what you want to do. In this part you will find types of trend, you can name the line, extrapolate and more options.
The respective edition of the scatter graph is at the discretion and need of the Internet user who is preparing it.. Excel is extremely easy to handle, since it only remains for the user, Fill in the box with the information you have.
How to customize your scatter plot?
As mentioned, in excel spreadsheet, it is attached a scatter plot pre-designed to be filled out solely by the user. Now, you should know how to customize the graphic so that it looks more like what you are looking for.
Chart Style
Graphic style can be customized to your liking, change the color, thickness, 3D shadow and much more. Just right-click anywhere on the chart to display the menu and select ‘Chart area format …’. There you can choose the filling, border color, styles and more.
Similarly, when right-clicking on a mark or on the line, the menu will be displayed where you will select ‘Format data series’. A window will appear where you can customize all aspects of the chart plot, as color, style, shadow, marker fill, among others.
Graphics Titles
To add titles to your scatterplot in Excel, just click on it to bring up the section on the toolbar. ‘Graphics tools’. There you can add titles, axis labels, legends and more in the 'Tags' section.
In that way, to add the title of the scatter plot just select ‘Title of the chart’ in 'Tags'. You can place the title centered above or above the chart.
Change series
Although scatterplots are of values with varying trends, yet it is possible to put lines. To further customize your scatter plot, you will have to choose the type of data series. Do this directly in the section ‘Graph’ from the ‘Insert’ section.
You can choose another chart style in addition to the single markers. You can also choose with smooth lines and markers, with smooth lines, with straight lines and with straight lines and markers. Each style has its characteristics that you will have to take into account.
In the same way, you can change the chart series by right-clicking on a mark or line to display the menu and select ‘Change type of series chart …’.
Modify the legend
Like the title, You can modify the legend that by default will be called 'Series1'. To do this you must follow the same procedure to add the title, only this time you will have to click where it says 'Legend’. There you can add, remove or place legends on the chart. You will find different options available, how to choose where to display the legends and whether to superimpose them on the chart or not.
In addition, you can rename the legends by right clicking and selecting ‘Select data …’. You can also change the format, size, color and other customization options.
Vertical and horizontal axes
Depending on the type of data you work with, you can adjust the scales of the vertical and horizontal axes. Doing this is very simple, having to click on the chart following the procedure to add a title. Only this time, will go to the section ‘Axes’ where you will find two options: ‘Axes’ and 'Grid lines'.
You will select 'Axes’ and there you can modify both the vertical and horizontal axis. You can modify the scale of the axes, being able to even place a logarithmic scale. In the same way you can delete them in case you need it.
You should also clarify the relationship between the two variables, which will be represented in the ‘X axes’ e 'Y'.
The values of the variables must be determined. It is crucial that both values to be compared are connected with the mutual temporal reality, namely, that your values both are from the same date compare.
The variable must be positioned in the precise place, in general measures, the independent variable, is assigned to the Cartesian axis of 'X'.
The determination of the correlation coefficient will be the one that shapes or molds the interaction between variables on the Cartesian axis.. Fortunately, Excel takes care of all this. And we just have to apply and run the tools that are requested through the interface.
Examples of good use of a scatter plot
Finally we teach you Examples of how to use charts or scatterplots in Excel. That way, you can get a better idea of what the one you are going to do should look like.
Sale of ice cream
Here you can see a scatter plot in Excel where you can see the sale of ice cream according to the temperature at which it is sold. We can notice that it has no legend, has handles on axes and has primary and secondary grid.
Income and expenses
In this example you can see the relationship between income and expenses of any company, where the balance is also reflected. We can see that it has three legends plus its title, noting that because they are monetary figures, as the scale, takes negative values.
That's how easy it is to make a diagram or scatter chart in an Excel spreadsheet. Well, as it has been clear, is one of many charts that can be created with the great tool that is Excel. Thus, no need to perform calculations, just enter the values and voila.
