'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Fix Excel XY Scatter Plot Showing Numbers Instead of Dates
Question details
The user needs to display actual dates on the X-axis of an Excel XY scatter plot instead of sequential numbers like 0, 1, 2, and 3.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating or modifying an XY scatter chart using data that includes dates on the horizontal axis.
- Observed behavior
- The chart's horizontal X-axis defaults to displaying sequential integers despite the source data and chart settings appearing to contain dates.
Ensure your source data does not contain any hidden spaces or text formatting characters, as this often prevents the spreadsheet from recognizing the cells as valid dates.
Convert Text to Valid Dates and Reconfigure Axis Settings
By ensuring the underlying data format is properly recognized as dates and explicitly setting the axis options, you can force the chart to display dates.
Excel often plots sequential numbers (1, 2, 3...) when it fails to interpret the X-axis data as continuous numbers or dates. This usually stems from the source data being formatted as text.
Select the source cells containing your dates. Right-click the selection, click 'Format Cells', navigate to the 'Number' tab, select 'Date', and choose your preferred date format.
If formatting alone doesn't change the values, keep the cells selected, go to the 'Data' tab, click 'Text to Columns', and click 'Finish' to force Excel to recognize them as dates.
Right-click the scatter plot and select 'Select Data'. Ensure the 'Horizontal (Category) Axis Labels' or 'Series X values' properly reference your newly formatted date column.
Right-click the horizontal (X) axis on the chart and select 'Format Axis'. Under 'Axis Options', confirm the minimum and maximum bounds correctly represent your date range's serial numbers.
Create Accurate Scatter Plots with Dates in WPS Spreadsheet
WPS Spreadsheet makes it simple to create XY scatter charts with automatic date recognition. Easily format your axes and manage data visualizations without complex workarounds.
- 1. Open your data file: Launch WPS Spreadsheet and open the document containing your data.
- 2. Select your data: Highlight the columns containing both your dates and the corresponding numeric values.
- 3. Insert the scatter chart: Go to the 'Insert' tab on the ribbon, click the 'Scatter' chart icon, and choose your preferred chart style.
- 4. Customize the axis: Double-click the horizontal X-axis to open the Format Axis pane on the right side and customize the date display options as needed.
Frequently Asked Questions
Why does my Excel chart show 1, 2, 3 instead of actual dates?
This happens when Excel fails to recognize the data in the X-axis column as numerical date values, often because they are formatted as text. It can also occur if a standard Line chart is used instead of an XY Scatter chart, causing Excel to treat the dates as categorical text labels.
How do I force an Excel axis to show dates?
First, ensure your source cells are formatted as Dates. Then, right-click the horizontal axis, select 'Format Axis', and under the 'Axis Options' section, change the 'Axis Type' to 'Date axis' if the option is available for your chart type.
Does changing the format of the cell automatically update the scatter plot?
Not always. Changing cell formatting from 'Text' to 'Date' might not immediately refresh the chart's underlying data interpretation. You often need to use the 'Text to Columns' tool to force the conversion, or simply recreate the chart after formatting the cells.
