Fix Excel Scatter Plot Showing 1, 2, 3 Instead of X Values
Question details
The X-axis on an Excel scatter plot displays sequential integers (1, 2, 3...) rather than the selected source values, even though the Y-axis values are correct.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Creating an XY scatter plot using data copied and pasted from a formula (like an IF statement) as values, resulting in incorrect X-axis plotting.
- Observed behavior
- The chart ignores the selected X-axis values and substitutes them with a default 1, 2, 3 sequence because the source data contains hidden text characters or apostrophes.
Verify your X-axis data by checking its cell alignment; by default, true numbers align to the right, while numbers stored as text align to the left.
Convert Text to Numbers to Fix the X-Axis
Remove hidden apostrophes or text characters from your X-axis data so the chart recognizes the values as true numbers.
When data is copied from an IF statement or imported from external sources, empty strings or hidden apostrophes often remain. Even if you format the range as numbers later, Excel still treats these cells as text. A scatter plot strictly requires numerical data for the X-axis. If it encounters text, it defaults to a sequential 1, 2, 3 scale.
Highlight the entire column or range of cells that contains the values you want to appear on the X-axis.
Navigate to the 'Data' tab on the ribbon and click 'Text to Columns'. In the wizard that appears, simply click 'Finish' immediately. This forces Excel to re-evaluate the cells and drops hidden text formatting.
Type the number 1 in any blank cell and copy it. Select your X-axis data, right-click, choose 'Paste Special', select 'Multiply' under the Operation section, and click OK.
Once the data is converted to true numbers, click on your scatter plot. The X-axis should automatically update to display your actual data values instead of the sequential numbers.
Easily Format Data and Create Scatter Plots with WPS Spreadsheet
WPS Office provides a highly compatible spreadsheet tool that makes data cleaning, formatting, and chart generation straightforward. Its intuitive error-checking tools help you spot and fix text-formatted numbers instantly.
- 1. Open your data file: Launch WPS Spreadsheet and open your existing .xlsx workbook.
- 2. Identify text numbers: Look for a small green triangle in the corner of your data cells. Click the warning icon and select 'Convert to Number'.
- 3. Select chart data: Highlight your X and Y data columns simultaneously.
- 4. Insert scatter plot: Go to the 'Insert' tab, click the 'Scatter' chart icon, and choose your preferred chart style to accurately plot your data.
Frequently Asked Questions
Why does my scatter plot ignore my X values?
A scatter plot requires strictly numeric data for both axes. If your X values are stored as text (often due to leading apostrophes or empty spaces from pasted formulas), the software cannot plot them mathematically and defaults to a sequential series (1, 2, 3).
How can I quickly tell if my numbers are stored as text?
By default, valid numbers align to the right side of a cell, while text aligns to the left. You might also see a small green error indicator triangle in the top-left corner of the cell warning you that the number is stored as text.
Does formatting the cells as 'Number' fix the issue?
No, simply changing the cell format from 'General' to 'Number' via the Home tab does not change the underlying data type if a hidden apostrophe or space already exists. You must force the spreadsheet to re-evaluate the data using tools like 'Text to Columns' or multiplying the range by 1.
Will moving columns fix the scatter plot X-axis issue?
Moving columns is not necessary if your X range contains valid numeric data. As long as the X-axis data range is correctly assigned in the 'Select Data' chart menu and the values are formatted as true numbers, the scatter plot will display correctly.
