If there is something that should be essential throughout the comprehensive training of a person, it is knowing how to handle Excel tools . And not only to know how to make a graph , we refer to all the possible functions that are within the scope of the possibilities to learn. Just 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 possibilities are multifaceted. Such as advanced line charts , or in-between aspects like dual axis charts . However, covering all aspects in these very modest lines cannot be possible, so we are going to focus on creating a chart or scatter plot.
You are in the right place to learn how to make a scatter plot or diagram in an Excel spreadsheet . And for this we are going to break it down below.
Index( )
- How to format the data correctly?
- Is it better to use a scatter chart or a line chart?
- How to create a great scatter chart?
- Select the two columns of data and the type of chart
- Select ‘Calc’ and add the Trend line
- How to customize your scatter plot?
- Chart Style
- Chart Titles
- Change the series
- Modify the legend
- Vertical and horizontal axes
- Examples of good use of a scatter plot
- Ice cream sale
- Income and expenses
How to format the data correctly?
Excel is a calculation tool that covers many areas, being used worldwide by people and companies. In the case of the creation of tables, graphs and diagrams, it offers the option of being able to format the data , according to our needs. And doing it is not complicated at all.
To give 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 style, shape and all the appropriate format for what you want to do.
Is it better to use a scatter chart or a line chart?
Much can be said on this subject, but it will be necessary to adhere to the mathematical field in order to give a correct conceptualization. The element is made up of two charts, line chart and scatter chart.
Now, the line graph will be a representation on a Cartesian coordinate axis where different values will be captured for study and / or analysis. For its part, the so-called scatter graph is used when there is a degree to which values are separated in sets, but valued according to their average value.
A scatter plot will be a representation of two variables, within a Cartesian graph, where a data conglomerate will be analyzed. These can be similes one of another by a common point.
In answering the question posed, you must first consider what types of data you are going to work with in order to make a decision. If the data does not vary, or in other words, it is consistent (a dependent value follows a pattern as independent values vary), then it is best to use a line graph.
On the other hand, if the data varies positively or negatively (an independent value can yield a larger or smaller dependent value), then the ideal is to use a scatter plot . That way the data can be better represented, avoiding obtaining a line graph that is not consistent.
In short, scatter charts are used to represent statistical, scientific, and engineering data . This is because it allows a better analysis to be able to modify the graph’s axes as necessary.
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 its tools. Being able to make or create a chart or scatter diagram in an Excel spreadsheet will be extremely simple. This program has everything preconfigured just to append information.
Select the two columns of data and the type of chart
Excel must first be opened 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. Next, in the toolbar you will click on the ‘Insert’ section.
We are going to refer to the space devoted to graphics which is ‘Graphics’. There you will see their category, distributed according to the functions to be carried out. Within the ‘Scatter’ section you will select ‘Scatter only with markers’ . It is done this way because properly a diagram or scatter plot does not have lines.
Once chosen, the interface will show the model that has just been selected, which will 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 have to right-click on a data in the graph. When the menu is displayed, you will select ‘Add trend line’ and a window will immediately 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 to fill in the graph with the information they have.
How to customize your scatter plot?
As you mentioned, in the Excel spreadsheet, a predesigned scatter diagram was attached to only be filled 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
It is possible to customize the style of the graphic to your liking, being able to change the color, thickness, 3D shadow, and much more . Just right click anywhere on the chart to pull down the menu and select ‘Chart Area Format …’ . There you can choose fill, border color, styles and more.
Similarly, right clicking on a markup or on the line will display the menu where you will select ‘Format data series’ . A window will appear where you can customize all aspects of the chart layout, such as color, style, shadow, marker fill, among others.
Chart Titles
To add titles to your scatter diagram in Excel, you just have to click on it so that the ‘Chart tools’ section appears in the toolbar . There you can add titles, axis labels, legends and more in the ‘Labels’ section.
That way, to add the title of the scatter plot you just have to select ‘Chart title’ in ‘Labels’. You can place the title centered on top of or on top of the graphic.
Change the series
Even though scatterplots are of values with varying trends, it is still possible to place lines on them . To further customize your scatter plot you will have to choose the type of data series. You do this directly in the ‘Graphic’ section of the ‘Insert’ section.
You will be able to choose another style of graph besides the one of only 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 consider .
In the same way, you can change the series of the diagram by right-clicking on a markup or trace to display the menu and select ‘Change type of series graph …’ .
Modify the legend
Like the title, you can modify the legend that by default will be named ‘Series1’. For 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 such as choosing where to display the captions, and whether to superimpose them on the graph or not.
Likewise, you can change the name of 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 are working with, you can adjust the scales of the vertical and horizontal axes . Doing this is very simple, having to click on the graphic following the procedure to add a title. Only this time, you will go to the ‘Axes’ section where you will find two options: ‘Axes’ and ‘Grid lines’.
You will select ‘Axes’ and there you can modify both the vertical and horizontal axes. You can modify the scale of the axes, being able to even place a logarithmic scale . In the same way you can remove them in case you need it.
Likewise, you must clarify the relationship between the two variables , which will be represented on the ‘X’ and ‘Y’ axes.
The values of the variables must be determined. It is crucial that both values to be compared are connected with the mutual temporal reality, that is, that their values are both from the same date to be compared.
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 who shapes or molds the interaction between variables on the Cartesian axis. Luckily Excel takes care of all this. And we only have to apply and run the tools that are requested by the interface.
Examples of good use of a scatter plot
Finally, we show you examples of how to use charts or scatter diagrams in Excel . That way you can have a better idea of how the one you are going to make should look like.
Ice cream sale
Here you can see a scatter diagram in Excel where you can see the sale of ice cream according to the temperature at which they are sold . We can notice that it does not have a legend, it has identifiers on the axes and that it has a 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 scatter chart or diagram in an Excel spreadsheet. Well, as it has been made clear, it is one of the many graphs that can be created using the great tool that is Excel . In this way, it is not necessary to perform calculations, just enter the values and that’s it.
