How to Swap Axis in Excel: 3 Practical Examples

Method 1 – Apply Select Data Feature to Swap Axis

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

  • 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

  • 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

  • From the Select Data Source dialog box, press OK.

7-Select Ok feature

  • You will see that the values in both chart axes have been swapped.

8-Swapping the axis value

Notes:

You can swap axes in any type of chart like Bar chart, Pie Chart, Bubble chart etc.


Method 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. To swap the chart axis with Independent and Dependent variables, you have to regenerate your chart by implementing this method.

  • 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

  • Select the data range from D4 to E10. From your Insert tab, go to,

Insert → Charts → Inser Scatter or Bubble Chart → Scatter

10-Selection of a scatter chart from the Charts group

  • Your chart will be regenerated by swapping the axis.

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


Method 3 – Run VBA Code to Swap Axis in Excel

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. 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.
  • Select Insert >> Module.

12-Open a Module to write down the VBA code

  • 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

  • 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

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

  • 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

Change the axis value in Excel. To do that, from our Insert tab, we will create a Scatter chart. Change the X-axis value and then the Y-axis value.

  • Click the Horizontal (Value) Axis.

15-Select the Horizontal (Value) Axis

  • The Format Axis window pops up. From that window, insert 0 in the Minimum Bounds box, and 800 in the Maximum Bounds box. Set 200 as the Major units.

16-Change Minimum and Maximum Bounds options

  • You will be able to change the X-axis value.

17-Changing the X-axis value

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

Download Practice Workbook


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.


Related Articles


<< Go Back To Formatting Chart Elements in Excel | Excel Chart Elements | Excel Charts | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo