How to Stop an Excel Chart Axis from Starting at January 1, 1900
Question details
The user needs to prevent an Excel or WPS Spreadsheet chart axis from starting at January 1, 1900 when the source date column contains zero or blank values.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- The chart source date column contains zero values or blank dates that are interpreted as serial number zero, forcing the horizontal axis to include January 1, 1900.
- Observed behavior
- The chart’s horizontal date axis starts at January 1, 1900 even though the visible data contains later dates.
Open the workbook and identify the chart source range. Check the date column for blank cells, zero values, or formulas that evaluate to 0 before adjusting the axis.
Remove or exclude zero and blank date values from the chart source
Use this when blank cells, zeros, or formulas returning 0 in the date column are pulling the chart axis back to serial date 0.
Excel and WPS Spreadsheet store dates as serial numbers. A blank or zero date cell is interpreted as serial number 0, which corresponds to January 1, 1900 in the 1900 date system.
Click the chart, then open Chart Tools or right-click the chart and choose Select Data to review the source range.
In the date column of the source range, locate empty cells, cells containing 0, or formulas that return 0.
Delete the invalid date rows, clear the zero cells, or adjust formulas so blank and zero dates are not included in the plotted range.
Click the chart and press F9 or re-select the data range so the horizontal axis no longer includes January 1, 1900.
Set a custom minimum date on the horizontal axis
Use this when you cannot remove blank or zero cells and need the axis to ignore them without changing the source data.
Fix a chart axis that starts at January 1, 1900 in WPS Spreadsheet
Open the workbook in WPS Spreadsheet, remove blank or zero date values from the chart source, or set the horizontal axis minimum to the first valid date. WPS Spreadsheet preserves Excel chart behavior and saves back to .xlsx format.
- 1. Open the workbook in WPS Spreadsheet: Launch WPS Office, click Menu, and open the .xlsx or .xls file containing the chart.
- 2. Inspect the chart source data: Click the chart, then use Chart Tools or right-click Select Data to check the date column for blank or zero cells.
- 3. Remove invalid date rows or set the axis minimum: Delete or clear zero and blank date cells. If they remain, right-click the horizontal axis and choose Format Axis, then set Minimum to your first valid date.
- 4. Verify the chart axis: Confirm the horizontal axis no longer starts at January 1, 1900 and save the workbook.
Frequently Asked Questions
Why does my Excel chart axis show January 1, 1900?
Excel and WPS Spreadsheet store dates as serial numbers. A blank or zero cell in the source date column becomes serial 0, which maps to January 1, 1900. Remove those cells or set a custom axis minimum to fix it.
How do I remove zero or blank dates from a chart source?
Select the chart, choose Select Data or Chart Tools, check the date range, and clear or delete blank and zero cells. Then refresh the chart.
Can I set the chart axis minimum to a specific date in WPS Spreadsheet?
Yes. Right-click the horizontal axis, choose Format Axis, set Minimum to a fixed date such as January 7, 2017, and leave Maximum on Automatic.
Does WPS Spreadsheet support Excel date axis formatting?
Yes. WPS Spreadsheet supports .xlsx and .xls workbooks and the same Axis Options for date axes, so you can correct the 1900 date label without changing files.
Will copying data into a new workbook fix the January 1, 1900 axis?
Sometimes. Copying date data into a new workbook can force valid date conversion, but you still need to remove zero or blank cells or set the axis minimum if serial 0 remains.
