Data organization has been one of Excel’s biggest benefits with its unique capacity and ability to organize large streams of data into orderly sheets and charts.
With our data being organized, analysis of the data gets a lot easier.
Excel’s formula and calculation capabilities are also top-notch, and our topic of discussion today is How to Switch Axis in Excel.
When representing data in various forms in Excel, we may need to make use of charts containing X and Y-axis – that is the horizontal and vertical axis respectively – and then we may come to a point in our data organization when we feel like Switching Axis on the Chart.
It’s quite simple and straightforward and in this course, I will be showing you how you can do this with some simple steps.
Let’s get started!
Here’s How to Switch Axis in Excel (Switch X and Y-Axis)
Understanding the Axis of Charts
It has become common knowledge from mathematics that when a chart graph is drawn, it comprises of axis and this axis is what forms the base for data representation on the chart.
The Y and X-axis are axes in the Cartesian coordinate and they two together form the coordinate plane.
Some chart graphs contain up to three axes (X, Y, and Z). X Y and Z axes are used to represent 3D drawings in mathematics while the X and Y-axis are used to represent 2D drawings.
For the purpose of this course however, we shall be dwelling on the X and Y-axis.
When a chart is created, either bar or column, X and Y-axis will always emerge.
The Y-axis represents the vertical component of the chart while the X-axis represents the horizontal component of the chart.
The significance of these axes is that values from our dataset find representation on each of the axis according to the user’s preference.
Let’s dive deeper now by gaining an understanding of how to produce a scatter chart in Excel.
This will help us gain a more in-depth understanding of what and how an axis looks like in Excel and gain insight on how to switch axis in Excel.
In making a quick illustration of a chart on Excel, let’s assume the dataset below containing goods in a particular store, their prices and amount sold.
Price values are represented in column B, and we have the amount sold in column C.
Now that we have our dataset outlined above, we now proceed to develop a scatter chart.
In creating a chart, we follow the steps outlined below:
- The first thing to do is to mark the range of values you wish to represent on the chart (for the dataset above B1:C12)
- Now move to the insert tab
- On getting to the insert tab, click on the scatter chart icon.
- Then click on it.
The immediate implication of following these steps is that you will get a chart just like the one shown below.
Considering the chart shown below, both the Y and X-axis has been indicated with an arrow.
If you pay detailed attention to the chart above, you will discover that the price column is represented on the X-axis and the amount sold is represented on the Y-axis.
Don’t forget, the Y-axis is the vertical axis while the horizontal axis is the X-axis.
Excel decides the range that needs to be shown in the axis when you want to produce a chart.
This is what I mean, if you pay attention to the Y-axis on the scatter chart, you will discover that the values there are in the range of ten, we have (10, 20, 30, 40, 50, 60, 70, and 80).
This is the value range Excel wishes to use and so the values representing our various data fall within that range and are thus represented on the chart.
Now that I have walked you through the steps involved in representing your dataset on a scatter chart indicating both X and Y-axis, it is also worthy of note that sometimes in data organization and presentation, we may want to make column B the Y-axis and column C the X-axis.
Worry no more as to how to go about that, Excel has Excellent features that allow for switching of axis with some few steps.
I will now be showing you these quick steps. Shall we?
Switching between Y and X-axis within Excel
To achieve the aim of making the price values in column B appear on the Y-axis and the amount sold in column C to appear on the X-axis, it is needful that we initiate a switching protocol between the two axes on the chart.
I will be highlighting the simple steps to take below:
- The first thing to do is to go to your chart that has already been produced with the price values on Y-axis and the amount sold on X-axis and then click on one of the axes and then hit Select Data from the menu that shows. By so doing, you can alter the chart’s data source.
- On clicking Select Data, the Select Data Source will appear, and then you will see different options like add, edit, remove, etc., now you click on Edit so as to initiate the switching process.
- When you click Edit, the window that shows up will display the range of values on the X-axis and the range of values on the Y-axis. You then interchange the values for each other, enter the range value for X in Y and that of Y in X.
- After the changes have been made to the values, you then click OK on the Select Data Source Window.
And then, the axes are switched. And from the pictorial illustration below, you can see that the price values are now on the Y-axis and the amount sold is also now on the X-axis.
Let me show you another method by which you can also achieve the results above:
Re-ordering the Dataset to Reset the Axis Manually
The method I talked about earlier is applicable to an already created chart.
In that method, the chart is already created and then we just make certain changes to its data source to switch the axis.
This method which we shall be looking into is applicable when the chart hasn’t been created yet.
In this method, you can order and tailor your data in such a way that it falls to your desired axis.
From the method we considered earlier, we see that Excel sets the first column of the dataset on the X-axis and the second column on the Y-axis.
This is a default setting in Excel and having this understanding goes a long way in helping us order our dataset to get the desired result.
In the example illustration we used in the first method, all we have to do is just shift all the values in the price column to the column representing amount sold.
By the time you do this, it will reflect on your chart in accordance with your desires.
Like I said earlier, Excel has quite a vast number of usefulness and features that make data organization quite easy. The option of switching axis in a chart gives room for adjustment and proper arrangement of data suiting one’s needs.
The two methods covered in this course are simple and straightforward ways of Switching Axis in Excel Charts.
The first is switching the axis of an already created chart by interchanging the series values range in the Data Source Dialogue Box and then the second method is altering your dataset manually to determine the placement of the values on the axis.
Please take note that you can also apply any chart in Excel to your dataset.
I only referenced the scatter chart for the specific example in this article.
Also, you can follow the same methods for any other chart you adopt on Excel.
One very good thing about this feature in Excel is that you can always re-edit your work to achieved a desired result.
I sincerely hope and believe you found this piece educative and applicable.