How to Swap Axis in Excel (3 Practical Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

1-how to swap 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.

2-Renaming the Axis Titles of a Scatter chart


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.

3-Selection of the Select Data option

  • The “Select Data Source” dialog box pops up. Under the Legend Entries (Series) menu, press the Edit option.

4-Choose 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.

5-Before changing the X-axis and Y-axis values in the Edit Series dialog box

  • 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.

6-Changing the X-axis and Y-axis values in the Edit Series dialog box

  • After that, from the Select Data Source dialog box, press OK.

7-Select Ok feature

  • Finally, you will see that the values in both axes of the chart have been swapped.

8-Swapping the axis value

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.

9-Manually swapped table data

  • 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

10-Selection of a scatter chart from the Charts group

  • So, your chart will be regenerated by swapping the axis.

11-Creation of a scatter chart by manually swapping the table’s data


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.

12-Open a Module to write down the VBA code

  • 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

13-Type VBA code and run the code

  • As a result, you will be able to swap the X and Y-axis in Excel.

14-Output of the VBA code


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.

22-Choose the Switch RowColumn feature

  • As a result, you can switch the Row or Column in the Column chart.

23-Switching 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.

15-Select 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.

16-Change Minimum and Maximum Bounds options

  • Finally, you will be able to change the X-axis value.

17-Changing the X-axis value

  • Similarly, you can change the Y-axis value.

18-Changing 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.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo