Did you know that the Scatter Chart is the only chart in Excel having both X and Y axes?
We draft a scatter chart (also known as a scatter graph or plot) to showcase the relationship between two variables. We use it to identify the patterns within the data and see if there are any unexpected gaps or outlier points. All these can help in analyzing and interpreting our dataset.
We typically use a dot to represent the point of intersection between two numeric variables. But it can be other symbols too, like square, triangle, plus sign, etc.
The perpendicular lines on the graph refer to the x and y axes.
- The x-axis is the horizontal line.
- The y-axis is the vertical line.
In this tutorial, I will show you how to switch the x-axis and the y-axis in a Scatter Chart.
Scatter Chart Default Settings
When you create a scatter chart in Excel, the leftmost numeric column in the dataset, by default, becomes the x-axis, while the column that comes after it becomes the y-axis.
In theory, the x-axis values refer to the independent variables, and the y-axis values refer to the dependent variables.
If for any reason, you need to swap the two, you can do the steps listed in the following options.
Change the X-axis and Y-axis on a Scatter Plot by Editing the Series
1. Right-click anywhere on the chart. From the list of options that appear, click on Select Data.
2. Once the Select Data Source form appears, click on the Edit button under the Legend Entries (Series) section.
3. You should now see the Edit Series form (as shown below).
4. First, we are going to reset the contents of the x-axis.
Highlight the entire text in Series X values and press DEL.
5. Next, click on the upward arrow button next to the textbox.
This button will let you select the range that will serve as the basis for the values on the x-axis.
The blank Edit Series form should appear.
6. Once you see that, highlight the numeric values in your data that you want to move to the x-axis.
Note: Do not include the header.
Once selected, click on the downward arrow button. Doing so will lead you back to the Edit Series form.
7. Next, we are going to edit the range for the y-axis.
Repeat steps 4 to 6, only this time, do it on the Series Y values field.
- Highlight the entire text in Series Y Values and press DEL.
- Click on the upward arrow button next to it.
- Highlight the cells containing the numeric values that intend to move to the y-axis.
- Once done, click on the downward arrow button in the Edit Series form.
8. In the Edit Series form, you should now see your x and y axes ranges interchanged.
Once you are happy with the result, click OK.
9. And that’s it! You should now see the updated version of your chart with the x-axis numbers moved to the y-axis (and vice versa)
10. If you have added axis labels, you may need to edit them to match the new numbers.
In my example above, I must remove the “No. of Hours Worked” from the x-axis and move it to the y-axis.
It should now look like this:
Change the X-axis and Y-axis on a Scatter Plot by Reordering the Data in the Dataset
By theory, the leftmost column should be the independent variable, while the column that comes after is the dependent variable.
If you had these two variables mixed up and now you want to reorder your dataset, then all you must do is switch the columns within the dataset.
To do this:
1. Copy the current x-axis column from the dataset and temporarily paste it somewhere else (e.g., an empty area on your sheet or onto a new sheet)
2. Copy the current y-axis column and paste it onto the leftmost side of the data set.
3. Go back to the empty area where you have temporarily pasted the previous x-axis column.
Highlight those cells, copy them, and paste them onto the second column in the dataset.
IMPORTANT: You should do a copy-and-paste, and not a cut-and-paste. Otherwise, your chart will not work as expected as it will not recognize the new data.
4. And that is it! You should now see your chart’s x and y axes interchanged.
Delete the temporary column and, if needed, update your axis labels to display the correct text.
Conclusion
Remember that the scatter charts are the only charts in Excel with both X and Y axes. The solutions suggested above will only work on this kind of chart.
You may have read some articles saying that to switch the x and y axes, you need to go to the Chart Design menu and click on the Switch Row/Column. Please know that this is not correct. This method DOES NOT switch the numbers in the x and y axes. As its name suggests, it instead swaps the row and column of the chart.