Flip x and y axes in Excel graph

This should not be very difficult, but I cannot figure out how to do it.

I have a table similar to this

 %low %high 0 0 12 1 13 26 ...
19 90 94
20 95 100

When I graph it, excel defaults to having the first column on the x axis and plotting the second and third column as y values. I want the first column to be on the y axis instead. I assume there is an easy way to do this, but I cannot figure it out. Most of the things I have found from searching have suggested the "Switch Row/Column" button, but that does something else.

Thanks for the help.

3

4 Answers

You can manually select what you wish to graph.

Here is my sample data:

I select to create a scatterplot graph. Upon editing the data source, I click the Add button.

You can select whatever you want for series name but I select the column header. X Values are the values in your X column of course. Y values are one of the Y columns.

Repeat the process for the second set of data.

5

Here's a simple VBA routine that creates an XY Scatter chart from data that is arranged with Y in the first column and one or more sets of X in subsequent data. Select the range of data, or select a single cell within that range, and run the code.

Sub ChartFromInput_YXX() ' assumes first column of input range has Y values ' assumes subsequent columns have X values ' assumes first row of input range has series Dim rngDataSource As Range Dim iDataRowsCt As Long Dim iDataColsCt As Integer Dim iSrsIx As Integer Dim chtChart As Chart Dim srsNew As Series If Not TypeName(Selection) = "Range" Then '' Doesn't work if no range is selected MsgBox "Please select a data range and try again.", _ vbExclamation, "No Range Selected" Else Set rngDataSource = Selection If rngDataSource.Cells.Count = 1 Then ' expand to data range including active cell Set rngDataSource = rngDataSource.CurrentRegion End If With rngDataSource iDataRowsCt = .Rows.Count iDataColsCt = .Columns.Count End With '' Create the chart Set chtChart = ActiveSheet.Shapes.AddChart.Chart With chtChart .ChartType = xlXYScatterLines '' Remove any series created with the chart Do Until .SeriesCollection.Count = 0 .SeriesCollection(1).Delete Loop For iSrsIx = 1 To iDataColsCt - 1 '' Add each series Set srsNew = .SeriesCollection.NewSeries With srsNew .Name = rngDataSource.Cells(1, 1 + iSrsIx) .Values = rngDataSource.Cells(2, 1) _ .Resize(iDataRowsCt - 1, 1) .XValues = rngDataSource.Cells(2, 1 + iSrsIx) _ .Resize(iDataRowsCt - 1, 1) End With Next End With End If
End Sub

0

If you need a repetitive task, the accepted solution might be time consuming. The great Jon Peltier has a simple add-in for this. See his page.

As stated in this other documentation page:

Switch XY Axes

This is a placeholder for the documentation page for Switch XY Axes.

I.e., no documentation is available. Nevertheless, it is really worthwhile trying it. It is really easy to learn.

1

For occasional axis-swapping needs this tutorial shows propably the easiest way.

1. Right-click the chart and click Select Data in the context menu.
2. In the popping out Select Data Source dialog box, please click to highlight the Y column, and then click the Edit button in the Legend Entries (Series) section.
3. Now the Edit Series dialog box comes out. Please swap the Series X values and Series Y values, and then click the OK buttons successively to close both dialog boxes.

See the link for pictures.

3

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy