Visualizing our data in Excel in the form of a chart can be super useful in helping to display trends. Charts like bar charts, line charts, scatter plots and so on are generally easy to make, but sometimes depending on the order of the columns of data it can sometimes look a little weird.
In this simple guide, we are going to run through two different approaches when setting up the X and Y axis on basic charts. Generally this will work best when we are working with two columns.
We will cover:
- The X and Y Axis explained
- Swapping the Axis by arranging columns
- Swapping the Axis using the Select Data options
The X and Y Axis in our charts
Let’s look at a really basic sample data set – a persons salary over time based on the years of experience they have. In one column we have their lists of salaries, and in the other we have a counter of the number of years – 1, 2, 3 and so on.
With the data set up the way it is in this table, when choosing a line chart we are given the below output automatically.
The X axis here is the one that runs along the bottom (salary), while the Y axis is the one that runs from bottom to top on the left (years). This looks a little odd and while we can see the upwards trend it is almost telling us the opposite story of what is really happening – that the more salary you have the higher the number of years experience you gain.
Generally what we would want in this kind of example is for the independent variable (years) to run along the X axis, and then we can see if that results in a trend in the dependant variable (in this case salary).
Let’s now take a look at two different ways we can flip this chart so that the X and Y axis are in a more readable order.
Swapping the Axis by arranging columns
This is more correct preparation than an actual tweak, but it is a good habit to get into! Let’s take a step back and flip the order of our two columns so that we start with Years Experience, followed to the right by Salary. From here when we make the chart it automatically displays it the way we want:
This comes back to what we said earlier about the value of independent and dependant variables and which impacts which. If we start with the independent value on the left then the chart output is far more logical.
Let’s say we are working with a more complex dataset, or have made some cosmetic tweaks that we don’t want to backtrack on – then this is where we move on to our next approach.
Switch the Axis using Select Data in Excel
Going back to our original example, we can actually quite easily flip the axis by using the built in Select Data feature in the chart options.
To access this right click on the chart itself, and click the Select Data option.
It will take us to the below window, where we need to click on the circled Edit button:
This brings up a box with three different fields:
Series Name: This is simply the cell you want to use as the header
Series X Values: The data you want to plot along the X axis
Series Y Values: The data you want to plot along the Y axis
The Series name part you can leave as is or change depending on what you want the header to say (or just type a custom one in the chart settings), but essentially we just need to swap the fields selected in the Series X and Series Y boxes! This would then look like the below:
Press OK and we are now presented with a flipped chart! Easy!
As we can see, this now looks far more readable and we didn’t have to make any real changes to the data itself.
This sums up our guide on how to switch x and y axis in Excel charts.
For more handy guides on working with Excel, be sure to check out our Excel Tips page.