Minhazur Rahman Sakib
Excel has various types of charts which can be used to make graphs and identify their various characteristics. The X Y graph is also known as a scatter chart or XY chart. Those charts provided below show trends and correlations between the two variables included in our diagram. In this tutorial, we will learn how to make an X Y graph in Excel. Also how to add axis labels, data labels, and many other useful tips.
Download Practice Workbook
Download the practice workbook from here
What Is X Y Graph in Excel?
The term XY graph refers to a graph where the values are plotted on the X and Y (horizontal and vertical) axes, but in particular, it includes mean scatter graphs and line graphs. Learning to produce these graphs with Microsoft Excel is a little bit tough.
Producing an Excel plot of X- and Y-axis points is achieved with built-in functions, and they’re easy to find and use. After learning how to create the graphs, it’s a good idea to know more about deciding which XY graph best suits your specific data set.
Various Types of X Y Graph in Excel
There are mainly Four types of X Y graphs in Excel:
- Smooth Lines
- Smooth Lines and Markers
- Straight Lines
- Straight Lines and Markers
All these four above X Y plots are suitable when we have fewer data points and when we’re plotting two series in the chart.
Step by Step Procedures for Making an X Y Graph in Excel
We will set up a dataset in columns B and C and then using the X Y graph we will display, modify, and format our X and Y plots.
Step 1: Collect Data
Suppose, we have the Apple Sale vs Month data of a small seller as shown below and we want to plot this information in an X Y Graph with smooth lines chart.
Step 2: Select Data
- Firstly, we need to choose both columns, including the column titles. In our case, the range is from B4 to C10.
Step 3: Input in Correlation
- Now, go to the Insert tab.
- Then, In the Chart group, click on the Insert Scatter Chart icon.
- Next, choose any Scatter chart from the drop-down.
Final Output
- Hence, The above steps would insert an X Y Graph in the worksheet as shown below.
Here, the column on the left which is the Number of Months in our example would be plotted on the X-Axis and the Apple Sale would be plotted on the Y-Axis.
Note: It’s best to have the independent metric in the left column and the one for which you need to find the correlation in the column on the right.
How to Customize an X Y Graph in Excel
Just like any other chart in Excel, We can easily customize the X Y Graph. In this section, we will cover some of the customizations we can do with an X Y graph in Excel.
1. Chart Title
By clicking double on the chart title, we can give a new title as we wish (e.g., Apple Sale vs Number of Months).
Read More: How to Create a Chart from Selected Range of Cells in Excel
2. Horizontal & Vertical Axis Format
To remove the unwanted white space around the data points, we can format the X and Y axes by changing the initial point of the graph.
For example, in this graph, 30 is the first value along the Y-axis. But the chart starts from zero point. So, we will do the following formatting for them with the steps below.
- Firstly, click on the chart area and go to the Format tab.
- Then, click on the Y-axis. The following side window will come on the Right.
- Next, from the Format Axis window, click on the Axis Options button and then click on the Axis Options drop-down menu.
- Now, input 20 in the Minimum box and 90 in the Maximum box as the maximum value is 86, located in the Bounds section.
- Then, press Enter.
- The graph looks much better now than before.
Read More: How to Plot Graph in Excel with Multiple Y Axis (3 Handy Ways)
3. Data Labels
Data labels are not seen when we generate a scatter plot in Excel. But we can easily add and format these.
- Firstly, to add the data labels to the X Y Graph, select the graph,
- After that, click on the plus icon on the right.
- And then check the Data Labels option.
- Finally, This will add the data labels that will show the Y-axis value for each data point in the scatter graph.
We can also add data labels on our X Y graph in the following way.
- Firstly, Click on the scatter plot and then click on the Chart Elements button.
- Then, click on the Data Labels drop-down >> More Options.
Here, we can add more formatting with the options in the following window.
Note: Data labels are perfect for scatter plots that have fewer data points. If our graph has many data points, the data labels will make the points congested and less presentable.
Read More: How to Plot Multiple Lines in One Graph in Excel
4. Trendline and Equation
We can add a trendline for our data points and format it in the following way.
- In the beginning, click on the chart area and then on the Chart Elements (Plus) button.
- Then, put mark the Trendline box.
- Here, from the Trendline drop-down, select More Options to add formatting to the trendline.
Further, to add a Correlation Equation to the graph.
- Go to the Format Trendline window as described above and mark the Display Equation on the chart box from the Trendline Options section.
Read More: How to Make a Graph from a Table in Excel (5 Suitable Ways)
5. Chart Style
We can change the chart style of your scatter plot in the following way.
- Firstly, click anywhere in the chart area.
- Then, go to the Chart Design tab.
- After that, go to the Chart Styles group.
- In the end, select a suitable chart style from the available formats.
- After following all these, the X Y graph will look like the below image.
Read More: Plotting Row Number Instead of Value in Excel (with Easy Steps)
Conclusion
Here, you will be able to learn how to make an x y graph in excel following the above-described procedures. Keep using them and let us know if you have more ways to do the task. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.
