Sometimes, when you create a chart in Excel, you may want to switch the axis in the chart (i.e, interchange the X and the Y-axis)
It’s really easy and in this short tutorial, I will show you how you can switch axis in Excel charts with a few clicks.
So let’s get started!
Understanding Chart Axis
If you create a chart (for example column or bar), you will get the X and Y-axis.
The X-axis is the horizontal axis, and the Y-axis is the vertical axis.
Axis has values (or labels) that are populated from the chart data.
Let’s now see how to create a scatter chart, which will further make it clear what an axis is in an Excel chart.
For creating a chart, I will be using the below data set, which contains products in column A, Sales value in column B, and Quantity value in column C.
In order to create a chart, you need to follow the below steps:
- Select a range of values that you want to present on a chart (in this example B1:C10)
- Go to the Insert tab
- Select Insert Scatter chart icon
- Choose the Scatter chart
As a result, you get the scatter chart as shown below (where I have highlighted the axis using the arrow).
This chart has the X-axis (horizontal) with values from the Sales column and Y-axis (vertical) with values from the Quantity column.
When you create a chart in Excel, it automatically decides the range that needs to be shown in the axis.
Now that’s all good! But what if I want a chart where Sales is in Y-Axis and Quantity in X-axis.
Thankfully, Excel allows you to easily switch the X and Y axis with a few clicks.
Let’s see how to do this!
Switch X and Y Axis in Excel
Now, if you want to display Sales values on the Y-axis and Quantity values on X-axis, you need to switch the axis in the chart.
Below are the steps to do this:
- You need to right-click on one of the axes and choose Select Data. This way you can also change the data source for the chart.
- In the ‘Select Data Source’ dialog box, you can see vertical values (Series), which is X axis (Quantity). Also, on the right side there are horizontal values (Category), which is Y axis (Sales). You have to click on the Edit on the left side in order to switch axes.
- In the pop up window, you can see that Series X values are range “=Sheet1!$B$2:$B$10”, and the Series Y values are range “=Sheet1!$C$2:$C$10”.
- In order to switch values, you have to swap these two ranges, so that the range for series X becomes a range for series Y and vice versa. So, in Series X values, enter “=Sheet1!$C$2:$C$10”, and in Series Y values, enter “=Sheet1!$B$2:$B$10”.
- After you confirm changes, you will be redirected back to the Select Data Source window, and click OK.
Finally, your chart has a switched axis. As you can see, Sales values are now on the Y-axis, and Quantity values are on the X-axis.
Rearrange the Data to Specify the Axis
The above method works great when you have already created the chart and you want to swap the axis.
But if you haven’t created the chart already, one way could be to rearrange the data so that Excel picks up the data and plots it on the X and Y axis as per your needs.
Excel by default sets the first column of the data source on the X-axis, and the second column on the Y-axis.
In this case, you can just move Quantity in column B, and Sales in column C.
Switching the axis option in a chart gives you more flexibility for adjusting the chart axis. Also, this way you don’t need to change any data in your sheet.
So these are two simple and easy ways to switch the axis in Excel charts.
While I have shown an example of a scatter chart in this tutorial, you can use the same steps to switch axis in case of any chart in Excel.
I hope you found this Excel tutorial useful!
Other Excel tutorials you may also like: