5 Ways to Switch the X and Y Axis in Microsoft Excel

Today, I’ll show you how to switch the X and Y axes in Excel in this quick and effortless Microsoft Excel tutorial.

You often use Microsoft Excel to store data, make calculations, and create visualizations to extract actionable insights. However, the automatic Excel chart creator might not plot the graph your way. You might have sales figures over time, but you want time on the vertical axis (Y-axis) instead of the horizontal axis (Y-axis).

Or, you might want to change the values of the chart axes to visualize your Excel graph from a different perspective. Excel charts are highly flexible and customizable so you can visualize the datasets the way you want.

Find below all the tried and tested methods to swap the chart axes, like switching X to Y and vice versa.

Switch X and Y Axis in Excel by Swapping the Data

This is a manual method you can use when the Switch Row/Column feature won’t work in the Select Data Source dialog.

Sample dataset to swap axes
Sample dataset to swap axes

Let’s consider, that your dataset and chart are highly similar to the above sample data.

The values in column A, Salesperson per site, are plotted in the X axis of the chart.

The sales figures of column B, Sales Revenue, are plotted in the Y axis of the graph.

Now, you want to swap the values in the X and Y axes to visualize the dataset in a completely different perspective. For example, X on Y to Y on X, and vice versa.

Copy and paste Y axis values
Copy and paste Y axis values

Firstly, copy and paste the entire column B to a different column of the worksheet.

Copy X axis values on Y axis
Copy X axis values on Y axis

Now, copy column A from its original position and paste it in the place of column B, to overwrite existing values.

You shall see that Excel has changed the chart according to the change in real time.

Copying Y axis values over X axis
Copying Y axis values over X axis

Then, copy the other column, Sales Revenue, on column A to overwrite old values.

That’s it! You’ve successfully swapped the values of the X and Y axes to change the data visualization.

Switch X and Y Axis in Using Switch Row/Column

Switch Row/Column is the easiest way to swap the X and Y axes. However, it might not generate an accurate visualization of the transposed values. You’ll need to manually edit the output chart to make it meaningful or usable.

Switch Row Column command
Switch Row Column command

Using this method is really simple. Go to your worksheet and click on the chart to activate the Chart Design tab.

Now, press Alt + JC + W to use the Switch Row/Column command.

Switch X and Y axis using Switch Row Column
Switch X and Y axis using Switch Row Column

Excel shall switch the data series accordingly.

Switch X and Y Axis Using the Select Data Menu

This is yet another manual method to accurately switch the X and Y axes to create meaningful and alternative visualizations.

Select Data command
Select Data command

Go to your Excel chart and right-click. In the context menu, click on the Select Data option.

Remove data points
Remove data points

You’ll see the Select Data Source dialog box.

Click the Remove button to delete all the cell range references of the chart.

Choose Y axis coordinates
Choose Y axis coordinates

Now, click on the Chart data range field and choose the Y-axis cell range, the Footfall column. Previously, this column was plotted on the X-axis. You can find it in the first screenshot of this section.

Edit horizontal axis
Edit horizontal axis

Click the Edit button inside the Horizontal Axis Labels field.

Select X axis range
Select X axis range

Now, select the values of the Sales column. Previously, it was plotted on the Y-axis. Click OK to insert the values.

Switched X and Y axis using Select Data
Switched X and Y axis using Select Data

You’ve successfully switched the X and Y axis data points.

Switch X and Y Axis in a PivotChart

Select dataset and click PivotChart
Select dataset and click PivotChart

Select the input dataset of your chart and click on the PivotChart dropdown menu in the Insert tab.

Click on the PivotChart option from the dropdown to open the Create PivotChart dialog.

Create PivotChart
Create PivotChart

On Create PivotChart, click on the Existing Worksheet option and choose a cell on the worksheet where you want the PivotChart.

You can now start configuring your PivotChart. In the PivotChart Fields sidebar, you’ll see your datasets, like Sales and Footfall.

PivotChart Fields
PivotChart Fields

Drag Sales to Values and Footfall to Axis to create Sales on Footfall chart, where Footfall values are on the X-axis and Sales values are on the Y-axis.

Drag Sum of Sales to Axis
Drag Sum of Sales to Axis

To switch the X and Y axes, simply drag the Sum of Sales from Values into Axis.

Drag FootFall to Values
Drag FootFall to Values

Then, drag Footfall from Axis into Values. It shall become the Sum of FootFall.

Switched X and Y axis in PivotChart
Switched X and Y axis in PivotChart

You’ve successfully made the chart modifications. This is the Footfall on Sales chart.

Switch X and Y Axis in Excel Using VBA

If you wish to programmatically switch the axes in the chart, you can use Excel VBA. Find below the script you can use:

VBA script to switch axes
VBA script to switch axes
Sub SwitchXY()

    ' Prompt for the name of the chart
    Dim chartName As String
    chartName = InputBox("Enter the name of the chart that needs modification")

    ' Check if the chart exists in the active sheet
    Dim cht As ChartObject
    On Error Resume Next
    Set cht = ActiveSheet.ChartObjects(chartName)
    On Error GoTo 0

    ' If the chart doesn't exist, exit the subroutine
    If cht Is Nothing Then
        MsgBox "No chart named '" & chartName & "' found in the active sheet.", vbInformation
        Exit Sub
    End If

    ' Switch X and Y data
    Dim temp As Variant
    temp = cht.Chart.SeriesCollection(1).XValues
    cht.Chart.SeriesCollection(1).XValues = cht.Chart.SeriesCollection(1).Values
    cht.Chart.SeriesCollection(1).Values = temp

End Sub

The script will automatically swap the underlying data of the X and Y axes. All you need to do is enter the appropriate chart name you want to modify.

Firstly, create a VBA macro by following the instructions mentioned in this Excel tutorial:

📒 Read More: How To Use The VBA Code You Find Online

You can run the macro by pressing Alt + F8. In the Macro dialog that shows up, click on the SwitchXY maco and hit the Run button.

Enter chart name
Enter chart name

You’ll see a dialog box where you can enter the name of the chart.

Finding chart name
Finding chart name

To find the chart name, click on it on the worksheet and find the name in the Name Box, near the Formula Bar.

Switched X and Y axis using VBA
Switched X and Y axis using VBA

Excel shall instantly overwrite the X-axis data points with Y-axis data points, and vice versa.

Conclusions

Now you know the best methods to switch the X and Y axes in Excel.

If you’re new to Excel, you can start with the manual methods that involve manually swapping the data points of X and Y axes.

You can also try out the Switch Row/Column command if the option is available in the Select Data Source dialog or use the Edit option to remove and re-add the underlying data points.

If you’re comfortable with the complex features of Excel, you can try out the PivotChart and Excel VBA-based methods.

If the article helped you to switch axes in your chart, share your feedback or suggestions in the comment box. Also, if you know any other way to swap axes, you can add that in your comment.

About the Author

Tamal Das

Tamal Das

I'm a freelance writer at HowToExcel.org. After completing my MS in Science, I joined reputed IT consultancy companies to acquire hands-on knowledge of data analysis and data visualization techniques as a business analyst. Now, I'm a professional freelance content writer for everything Excel and its advanced support tools, like Power Pivot, Power Query, Office Scripts, and Excel VBA. I published many tutorials and how-to articles on Excel for sites like MakeUseOf, AddictiveTips, OnSheets, Technipages, and AppleToolBox. In weekends, I perform in-depth web search to learn the latest tricks and tips of Excel so I can write on these in the weekdays!

Related Posts

Comments

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃