When you create a chart in Excel, you will get the X-axis (the horizontal axis), and the Y-axis (the vertical axis). You can change the display by switching the X-axis and Y-axis.
Let us look at the steps to interchange the X and Y axis.
2 Methods of switching X-axis and Y-axis in Microsoft Excel:
- Swap the Data Range
- Rearrange the Data Source
Both methods of switching the axis are simple. Follow the steps in the sections below to switch the X- axis and Y-axis easily.
Let us begin.
Preparation: Create a Scatter Chart
To switch the axis, you must have a chart or you can create a chart and rearrange the axis. In this example, we will use sales data to first create a Scatter chart and later swap the axis.
A scatter chart shows the relationship between two different variables. It helps highlight the similarities in the data set.
Follow the steps below to create a Scatter chart.
Step #1: Create a dataset
Open Microsoft Excel.
Open an existing spreadsheet or create a new sheet.
In our example, the illustration displays data in three columns.
Enter the data as shown in the image above.
Select the cells in columns B and C:
Click with your mouse on B1. While holding the mouse button down, drag your cursor to C1 and then to the bottom of the column (Cell range B1:C11).
This will be our source data to create a Scatter Chart.
Step #2: Create a Scatter Chart
In the main menu bar, click on the Insert tab.
On the Insert ribbon, navigate to the Charts group.
Click the Insert Scatter (X, Y) icon to display the graph options.
Tip: Hover with your mouse over the icons to see their names.
The Scatter chart is plotted as displayed below.
When you create a chart, Excel decides the range to display on each axis.
The chart displays the X-axis (the horizontal axis) with values from column B – Sales. It displays the Y-axis (the vertical axis) with values from column C – Quantity.
We want to switch the X-axis to display values from column C – Quantity. And then the Y-axis must display the values from column B – Sales.
Method #1: Swap the Data Range
This method assists you in switching the axis by selecting alternative columns once the scatter chart has been created.
Step #1: Select the data
Click on the scatter chart.
Right-click with your mouse on the X-axis or the Y-axis.
From the pop-up list, choose Select Data...
The Select Data Source dialog box displays.
Step #2: Edit the Data Source
The Select Data Source dialog box displays the vertical values (Series), it is the X-axis (Quantity).
The right side displays horizontal values (Category), it is the Y-axis (Sales).
Click the Edit tab on the left.
In the Edit Series box, you can see:
- Series X values range “=Sheet2!$B$2:$B$11”
- Series Y values range “=Sheet2!$C$2:$C$11”
Step #3: Swap the Series
To switch the X- axis and Y-axis, let us swap the two value ranges.
Click on the arrow to the right of the field Series X values.
The Edit Series dialog box will minimize and the current column will be selected.
Click with your mouse on C2 and drag with your mouse to the bottom of the column, while holding down the button.
Release the button, and click again on the arrow to the right of the field Series X values.
Do the same with the arrow to the right of the field Series Y values.
Select the B2 column data, and return to the Edit Series dialog box.
Step #4: Create the chart after swapping the X-axis and the Y-axis
Click on OK to exit the Edit Series dialog box.
The axis on your chart has switched.
Sales values appear on the Y-axis, and Quantity values appear on the X-axis.
Method #2: Rearrange the Data Source
With this method, the data source is changed first, then the scatter chart is created.
By default, MS Excel sets the first column of your data source on the X-axis, and the second column on the Y-axis.
When you rearrange the data in your source file, Excel collects the data and plots it on the X – axis and the Y-axis to match your needs.
Step #1: Select the data
Access the dataset created earlier.
Click with your mouse on the capital C at the top of your dataset to select the whole column.
Click right, and select Cut.
Click with your mouse on the capital B at the top of your dataset to select the whole column.
While holding down the button, drag column B to the right past column C.
Click right, and select Insert Cut Cells.
Quantity will now be in column C, and Sales in column B.
Step #2: Create a Scatter Chart
Select the cells in columns B and C.
Click with your mouse on B1. While holding the mouse button down, drag your cursor to C2 and then to the bottom of the column (Cell range B1:C11).
In the main menu bar, click on the Insert tab.
On the Insert ribbon, navigate to the Charts group.
Click the Insert Scatter (X, Y) icon to display the graph options.
Tip: Hover with your mouse over the icons to see their names.
The Scatter chart is plotted as displayed below.
Conclusion
You can use any of these two easy methods of switching the axis in Microsoft Excel for any chart.
The first method offers extra flexibility to adjust the chart axis. You do not need to change the data in your source file. While the second method, you change the data sequence first, before creating the chart.
Use the method that fits your needs the best.