Make an Excel Scatter Chart Use Column Data for the X-Axis
Question details
The user wants an Excel chart to use values from a worksheet column for its X-axis instead of sequential numbers.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A chart is based on a regular table, with X-axis data in a separate column and measurements in another column.
- Observed behavior
- The scatter chart shows sequential X-axis numbers when the intended X-value range is not assigned. Entries such as 5RP are text and cannot be plotted as numeric scatter values.
Identify the X-value column and the corresponding Y-value column, and make sure their ranges contain the same number of data rows. Check whether the X entries are numbers or text labels such as 5RP.
Assign the X and Y ranges to the scatter chart series
Use this method when the X-axis column contains numeric values.
Select the chart, then choose Chart Design > Select Data.
Under Legend Entries (Series), select the series you want to change and click Edit.
In Series X values, select the numeric cells in column D, excluding the header. In Series Y values, select the matching measurement cells in the same rows.
Click OK to close the dialogs. If necessary, choose Chart Design > Change Chart Type and select an XY (Scatter) chart.
Use a line chart for text X-axis labels
Use this option when the X-axis column contains identifiers rather than numeric values.
Set scatter chart X-values in WPS Spreadsheet
Use WPS Spreadsheet to assign separate X and Y ranges to a scatter chart. For text identifiers, choose a line chart and use the identifier column as category labels.
- 1. Open the workbook: Open WPS Spreadsheet and choose Open to select your Excel workbook.
- 2. Select the chart data: Click the chart, then use Chart Tools > Select Data to open its data settings.
- 3. Edit the series ranges: Select the series and click Edit. Set the X values to the numeric cells in column D and the Y values to the corresponding measurement cells.
- 4. Confirm the chart: Click OK and check that the chart is an XY scatter chart. If the X entries are text labels, use Change Chart Type to select a line chart and assign the labels as categories instead.
Frequently Asked Questions
Why does my Excel scatter chart show 1, 2, 3 instead of my column values?
The series may not have the intended X-value range assigned. Select the chart, choose Chart Design > Select Data, edit the series, and select the numeric X cells in Series X values.
Can I use 5RP as an X value in a scatter chart?
No. An XY scatter chart requires numeric X values. Use a numeric helper column if a numeric mapping is meaningful, or choose a line chart and use 5RP as a category label.
How do I show text labels on the horizontal axis?
Use a line chart, then choose Chart Design > Select Data. Click Edit under Horizontal (Category) Axis Labels and select the cells containing your labels.
Do the X and Y ranges need to be the same size?
Yes. Select one X value and one corresponding Y value for each data point, using matching rows and excluding column headers from the value ranges.
