Re-entering data can be tedious, especially for longer spreadsheets. This problem must be quite common as Excel has simplified the process of switching the X and Y axes!
Excel is one of the most preferred digital spreadsheets. Not only can you create complex databases, but you can also further simplify the representation through graphs! However, learning how to create graphs can be tricky. Especially for newer users, you may input all your data correctly but in incorrect axes!
If you relate to this situation, you have nothing to be worried about because we’re here to help. Keep reading this article to learn more about how you can switch the X and Y Axis in Excel.
How to Switch X and Y Axis in Excel?
By default, Excel picks the first selected series as the X axis. If you do not want to go through the hassle of editing the data, you can simply arrange the data you want on the X axis to precede the row with data you want on the Y axis.
You can easily swap your graph’s X and Y axis from the Switch Row/Column option on the Chart Design ribbon in Excel. This works for almost all charts except the scatter and bubble chart.
Follow these steps to switch the axes on your graph in MS Excel:
- Launch MS Excel to open your workbook.
- From your workbook, select your graph.
- A new tab, Chart Design, will appear on the menu bar; Select it.
- From the ribbon, locate the Data section, then click the Switch Row/Column option.
From Select Data Source
You can also find the Switch Row/Column button on the Select Data Source window on Excel. You can open the select data source window through the Select Data option on the Chart Design tab.
Here are the steps you can refer to switch X and Y axis in Excel through the Select Data Source window:
- Open your Excel file.
- Select the diagram from the workbook. This should make the Chart Design tab appear on the menu bar.
- Hop on to Chart Design and click on the Select Data Source option from the data section. You can also right-click on your diagram and choose Select Data to open this window.
- On the new window, locate and select the Switch Row/Column button. This will automatically prompt Excel to swap the data value between the X and Y axis.
- Click on OK to confirm the changes.
For Scatter/Bubble Chart
You have to take the longer route if you’ve used the scatter or bubble chart to represent your data. All you have to do is interchange the data values of each axis.
The Switch Row/Column option is greyed out for these diagrams as the data range is too complex. However, you can swap the series value of all charts from the Select Data Source dialog box in Excel. Follow these steps to swap the values of the X and Y axes:
- Open your Excel workbook.
- From your spreadsheet, select your scattered graph.
- Right-click on the diagram and click on the Select Data option.
- A new window, Select Data Source, will appear on your screen. Under Legend Entries (Series), select Edit.
- Copy-paste the value under Series X values and Series Y values.
- Select OK on the dialog box. Repeat this step for the Select Data Source window.
What to Do If Switch Row/Column is Greyed Out?
Sometimes, when you switch from the scatter graph to other diagrams the Switch Row/Column button is greyed out. In this case, you will have to manually swap the data series from the X axis to Y axis.
You can still edit data the entries to switch data from the Select Data Source dialog box through these steps:
- Open Microsoft Excel to open your workbook.
- Right-click the graph and click on Select Data.
- From the Select Source Data window, select Edit under Legend Enteries (Series)
- In the Edit Series window, copy the data under Series values, then select remove the value. Click OK.
- Press Windows key + V to open the clipboard and turn it on.
- From Select Source Data Window, select Edit under Horizontal (Category) Axis Labels. Copy the data value under the Axis label range then remove it, then select OK.
- Repeat Step 3 to open the Edit Series dialog box.
- Select Windows key + V to open the clipboard. From the clipboard, copy the Axis label range value and paste it under Series values. Click OK.