Chart & Visualization Issues

Set a Date-Based Y-Axis Range in an Excel Line Chart

Tauseeq MagsiTauseeq Magsi Nov 12, 2026 869 views

Question details

Set a meaningful vertical date range in an Excel line chart when dates from different years create unsuitable Y-axis bounds.

Set a Date-Based Y-Axis Range in an Excel Line Chart
Product
Microsoft Excel
Device & OS
not provided
Scenario
The user wants to chart month-and-day values on the Y-axis without the original years affecting the axis scale.
Observed behavior
Excel treats dates as serial numbers, so dates from widely different years can cause the Y-axis to start or end at unexpected dates.
Before you start

Make sure the Y-axis source cells contain valid Excel dates rather than text. Choose one reference year that can represent every month-and-day value you want to display.

Solution 1Recommended

Create helper dates with one common reference year

Use this method when the chart should compare only the month and day, not the original year stored in each date.

Excel stores each date as a serial number. Rebuilding every Y-axis date with the same year keeps the chart scale within one calendar year while preserving the month and day.

1
Add a helper column

Insert a new column next to the original Y-axis date values and name it something such as Chart Date.

2
Build dates using a common year

In the first helper cell, enter =DATE(2024,MONTH(B2),DAY(B2)), replacing B2 with the cell that contains the original date.

3
Fill the formula down

Drag the fill handle or copy the formula down so every source date has a corresponding helper date in the same reference year.

4
Use the helper values in the chart

Select the chart, then go to Chart Design > Select Data and edit the series so the Y values reference the helper-date column.

5
Format labels as month and day

Right-click the Y-axis, choose Format Axis, open Number, select Custom, and enter d mmm to display labels such as 1 Mar and 5 Nov.

Create helper dates with one common reference year
Year-independent date labels: The helper dates retain the correct month and day while preventing distant original years from expanding the Y-axis range.
WPS Spreadsheets

Create a Controlled Date Y-Axis with WPS Spreadsheets

WPS Spreadsheets lets you create helper dates, update chart series, and control axis bounds so a line chart can display month-and-day values without original years distorting the scale.

  1. 1. Open the worksheet in WPS Spreadsheets: Open the Excel workbook in WPS Office and select the sheet containing the line chart and source dates.
  2. 2. Create a common-year helper column: In an empty column, enter =DATE(2024,MONTH(B2),DAY(B2)) and fill the formula down for all charted dates.
  3. 3. Update the chart data series: Click the chart and use Chart Tools > Select Data to replace the Y-axis source range with the helper-date column.
  4. 4. Set axis bounds and label format: Right-click the Y-axis, choose Format Axis, set the required Bounds, then apply the custom number format d mmm.
Create helper date formulas with DATE, MONTH, and DAY functions.Set custom minimum and maximum date bounds for chart axes.Format chart labels as d mmm for clear month-and-day display.Open and edit Microsoft Excel .xlsx and .xls chart workbooks with compatible formatting.
microsoft office alternative - wps office

Frequently Asked Questions

Why does my Excel chart Y-axis start at 1900 when my data contains modern dates?

Excel stores dates as serial numbers. If your Y-axis includes dates from very different years, automatic scaling can include an early serial-date range. Create helper dates using one common year before charting.

Can I show only the month and day on an Excel chart axis?

Yes. Right-click the chart axis, select Format Axis, open Number, choose Custom, and enter d mmm. This displays labels such as 1 Mar without showing the year.

How do I set a fixed date range on a chart Y-axis?

Right-click the Y-axis and choose Format Axis. Under Axis Options > Bounds, enter the Minimum and Maximum using the same reference year as your helper dates.

Do helper dates change my original date data?

No. Helper dates are separate formula values in another column. Your original dates remain unchanged, while the chart uses the normalized dates for its Y-axis.