In this tutorial. We will learn three practical examples of how to swap axis in Excel.
Excel charts are frequently used in reports and corporate presentations to visualize data. However, sometimes the default axes generated by the chart may not present the data as per our needs.
In such cases, just switching the axes can improve the display of the data and aid a business organization in making decisions. You can just swap the X and Y axis to change the orientation of the Excel chart and highlight different trends or patterns.
Here is the overview of the swapping axis in Excel.
Introduction to the X and Y Axes in Excel
In Excel charts, the X-axis represents the horizontal axis, while the Y-axis represents the vertical axis. The X-axis is often used to represent the independent variable or the category axis, while the Y-axis represents the dependent variable or the value axis.
You can make the Axis visible by enabling the Axis Title from the Chart Elements button located at the top-right corner of the graph.
You can also rename the axis name by double-clicking on the Axis Title.
Why Do We Swap Axis in Excel?
Swapping axes in Excel charts can be done for various reasons depending on the specific data and visualization requirements. Here are a few common reasons why you might choose to swap the axes in an Excel chart:
- Data Representation: Swapping the axis allows you to present your data in a different way, providing an alternative perspective on the information. It can help highlight different relationships or patterns within the data that may not be immediately apparent with the original axis arrangement.
- Visual Clarity: In some cases, swapping the axis can improve the clarity and readability of the chart. It may make it easier for the audience to understand and interpret the information being presented.
- Changing Perspectives: Swapping the axis can help you change the perspective or focus of the chart. It allows you to emphasize different variables or dimensions of your data, potentially leading to new insights or understandings.
- Comparisons: Swapping axes can be useful when comparing different datasets or variables. By swapping the axes, you can align similar categories or data points on the same axis, making it easier to compare and analyze the values.
- Custom Visualization: Swapping the axis provides flexibility in creating custom visualizations. It allows you to experiment with different chart types and combinations, enabling you to create unique and impactful visuals for your specific data needs.
Ultimately, the decision to swap the axis in an Excel chart depends on the specific data, visualization goals, and insights you want to convey. It is a technique that allows you to explore alternative ways of presenting and analyzing your data.
How to Swap Axis in Excel: 3 Practical Examples
We have a dataset that contains the Sales information of several sales representatives. Using this dataset, we create a chart to swap the axis in Excel.
1. Apply Select Data Feature to Swap Axis
In our chart, the Independent variable (Sales) is in the X axis and the Dependent variable (Profit) is in the Y axis. We will convert the X axis into the Dependent variable (Profit) and the Y axis into the Independent variable (Sales). We will use the “Select Data” option to serve our purpose. As soon as you create a chart in Excel, the “Select Data” option becomes available.
- Right-click on the chart area and choose “Select Data” from the list options.
- The “Select Data Source” dialog box pops up. Under the Legend Entries (Series) menu, press the Edit option.
- As a result, the Edit Series dialog box will appear showing the Sales data for the Series X values and Profit data for the Series Y values.
- After that, swap the values for both axes. Put the Profit data in the Series X values and Sales data in the Series Y values >> click OK.
- After that, from the Select Data Source dialog box, press OK.
- Finally, you will see that the values in both axes of the chart have been swapped.
Notes:
With this method, you can swap axes in any type of chart like Bar chart, Pie Chart, Bubble chart etc.
2. Manually Swap Data Series to Switch Axis in Excel
Another way of swapping the axis of your Excel chart is a kind of manual approach. However, for swapping the chart axis with Independent and Dependent variables, you have to regenerate your chart by implementing this method.
- For our corresponding example, just put the Sales data in the place of the Profit column and the Profit data in the place of the Sales column.
- Now, select the data range from D4 to E10. After that, from your Insert tab, go to,
Insert → Charts → Inser Scatter or Bubble Chart → Scatter
- So, your chart will be regenerated by swapping the axis.
3. Run VBA Code to Swap Axis in Excel
Now, I will discuss how you can swap the X and Y-axis in Excel using a VBA Macro. VBA stands for Visual Basic Application. This is the programming language for Excel. Using VBA code, we will switch the X and Y-axis from our dataset. Let’s see how to apply a VBA code to swap the X and Y-axis.
- Press ALT + F11 to bring up the Visual Basic Application You can do this by selecting the Visual Basic feature from the Developer tab.
- After that, Select Insert >> Module.
- Now, write down the following code in that Module, and press the Run button or press the F5 key to run the VBA code.
Sub swap_axis()
Dim Xsrs As Series
Set Mrng = ActiveSheet.Range("D5:E10")
With ActiveSheet.ChartObjects.Add(Left:=Mrng.Left, _
Width:=Mrng.Width, Top:=Mrng.Top, Height:=Mrng.Height)
.Chart.ChartType = xlXYScatterLines
'.Chart.SetSourceData Source:=Range("D5:D10,E5:E10")
Set Xsrs = .Chart.SeriesCollection.NewSeries
Xsrs.Name = "Sales vs Profit"
Xsrs.XValues = Range("E5:E10")
Xsrs.Values = Range("D5:D10")
End With
End Sub
- As a result, you will be able to swap the X and Y-axis in Excel.
How to Switch Rows or Columns in Excel
Here, we will create a Column chart where the Sales and Profit data are in the Column and the Sales Reps data are in the Row. If you want to switch the Row or Column, Excel provides you the way.
- Click on the Chart area >> Go to the Chart Design tab >> Select Switch Row/Column feature from the Data group.
- As a result, you can switch the Row or Column in the Column chart.
Note: This feature can be used for all charts except the Line chart with a single line.
How to Change Axis Value in Excel
In this section, we will change the axis value in Excel. To do that, from our Insert tab, we will create a Scatter chart. First, we will change the X-axis value and then the Y-axis value. Let’s follow the instructions below to learn!
- Click on the Horizontal (Value) Axis.
- As a result, the Format Axis window pops up. From that window, insert 0 in the Minimum Bounds box, and 800 in the Maximum Bounds box. Hence, set 200 as the Major units.
- Finally, you will be able to change the X-axis value.
- Similarly, you can change the Y-axis value.
Tips for Using Chart with Swapped Axis in Excel
- When you swap the X and Y axis in your chart, the layout and presentation of the chart will change. Make sure you understand the new layout as it affects the visualization of data.
- After swapping the axes, the axes labels may need to be adjusted to ensure that they are accurate and clear.
- Swapping the axes in a stacked chart sometimes makes it more difficult to interpret the data. So, make sure to label stacked segments properly.
- Swapping the axis sometimes makes the chart more effective than others. Experiment with different charts to find the best one suited for you.
- In some cases, you may need to use a secondary axis for a better understanding of the data.
Things to Remember
- Swapping the X and Y axis in an Excel chart can be a useful way to display your data in a more understandable way.
- Swapping the axis can be helpful particularly when you have data that is better understood if the dependent and independent variables are switched.
- If you have a large amount of data that is difficult to display in the chart, swapping the horizontal axis to a vertical one can make it more reader-friendly.
- Excel provides many customization options for charts including changing chart types, putting axis titles, swapping axis, changing axis labels, etc.
- When swapping the axis, the labels and titles associated with the original axes will also swap positions. After swapping, double-check that the axis labels and titles are still correctly assigned to the appropriate axis.
- If your chart contains multiple series, swapping the axis will interchange the position of all the series. This can affect the visual representation and interpretation of the data. Take this into account and review the chart to ensure it still communicates the intended information accurately.
- Before swapping axes, ensure that the chart you have selected is suitable for representing your data. Consider the data types, relationships, and the message you want to convey. Swapping axes may not always be appropriate or visually effective for certain datasets.
- After swapping the axis, confirm that the order of the data series remains logical and follows the intended narrative or progression.
Frequently Asked Questions
1. Does swapping the axis change the order of my data series?
Answer: Swapping the axis does not change the order of the data series by default. However, it is important to review the order of the data series after swapping to ensure it remains logical and follows the intended narrative or progression. Make any necessary adjustments if needed.
2. How can I adjust the formatting of the chart after swapping the axes?
Answer: After swapping the axis, you may need to make formatting adjustments to maintain the chart’s visual appeal and clarity. Use the various formatting options available in the Excel ribbon to resize the chart, adjust axis scales, modify gridlines, update data labels, or change the color scheme as needed.
3. Can I swap axes in all types of Excel charts?
Answer: Not all chart types in Excel support swapping axes. The most common chart types that allow axis swapping include column charts, bar charts, line charts, and area charts. Other specialized chart types may have limitations or different methods for swapping axes.
Download Practice Workbook
Conclusion
So, now we know how to swap the axis in an Excel chart. Swapping is convenient for presenting data in a meaningful and understandable way. By using the simple steps stated in the article, you can take advantage of this feature. Swapping the axis can improve your insight whether you use a scatter plot, bar chart, or any other type of chart in Excel. Hope the article has shed some light on your ways. Don’t forget to share your feelings in the comment box.
