How to Fix Incorrect Dates on an Excel Scatter Chart Axis
Question details
The user needs to correct the dates displayed on the x-axis of an Excel scatter plot so they reflect the actual source data instead of displaying an incorrect default starting date.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating or modifying a scatter plot that uses dates for the horizontal x-axis values.
- Observed behavior
- The scatter chart ignores the actual source dates and displays an incorrect starting date, often defaulting to early 1900 dates, because it is interpreting the dates improperly on a numerical axis.
Ensure your source data column contains valid numeric Excel dates rather than text strings, as scatter charts require numerical values to plot axes correctly.
Adjust Axis Bounds Using Excel Date Serial Numbers
Manually set the minimum and maximum bounds of the x-axis using the numeric serial values corresponding to your desired start and end dates.
Excel stores dates as sequential serial numbers starting from January 1, 1900. Because scatter plots use continuous numerical values for their x-axis, they rely on these serial numbers. If the bounds are set to 0, the chart displays 1900. You must convert your target dates into serial numbers to set correct axis limits.
Copy your desired start and end dates into blank cells. Right-click the cells, select 'Format Cells', and change the format to 'General' or 'Number'. Note the resulting 5-digit numbers (e.g., 44927 for Jan 1, 2023).
Double-click the horizontal x-axis on your scatter chart. This will open the 'Format Axis' pane on the right side of your screen.
In the Format Axis pane, under 'Axis Options', enter the 5-digit serial number for your start date into the 'Minimum' field and the serial number for your end date into the 'Maximum' field. Press Enter to apply.
Create Accurate Scatter Charts Easily with WPS Spreadsheet
WPS Spreadsheet provides robust chart editing tools with highly intuitive axis formatting, allowing you to build and customize data visualizations accurately without format struggles.
- 1. Select Data: Highlight your source data range, ensuring both the dates and the corresponding numerical values are selected.
- 2. Insert Chart: Navigate to the Insert tab on the top ribbon, click on the 'Scatter' icon, and choose your preferred scatter plot style.
- 3. Format Axis: Double-click the x-axis to open the formatting pane on the right.
- 4. Adjust Bounds: Under Axis Options, input the correct numerical serial numbers for your desired date range into the Minimum and Maximum fields.
Frequently Asked Questions
Why does my Excel scatter chart start at the year 1900?
Excel's date system starts calculating from January 1, 1900. If your scatter chart's minimum horizontal axis bound defaults to 0, it interprets that 0 as the year 1900. Adjusting the minimum bound to the serial number of your actual start date will fix this.
How do I easily find the serial number of a specific date in Excel?
Type the specific date into any empty cell, right-click it, and select 'Format Cells'. Change the number format from 'Date' to 'General'. The cell will display a 5-digit number, which is the exact serial number you need for your axis bounds.
Can I use a line chart instead to avoid date serial issues?
Yes. Unlike scatter charts, which treat the x-axis as continuous numerical values, line charts can treat the x-axis as categorical text or chronological dates automatically. If your data intervals are evenly spaced, using a line chart instead of a scatter chart is often easier when dealing with dates.
