Chart & Visualization Issues

How to Set Yearly Intervals on an Excel Gantt Chart Axis

Maira Mehtab Oct 21, 2026 868 views

Question details

The user wants to configure the horizontal axis of a Gantt chart in Excel to display distinct yearly intervals instead of default values.

How to Set Yearly Intervals on an Excel Gantt Chart Axis

Product: Excel
Device & OS: not provided
Scenario: Creating a long-term project timeline or Gantt chart utilizing a stacked bar chart, and needing the timeline markers to reflect calendar years.
Observed behavior: Because stacked bar charts use a standard value axis rather than a dedicated date axis, setting yearly intervals requires manual adjustments of date serial numbers, and major units like 365 days often display unexpectedly due to leap years.

Before you start

Before adjusting the chart axis, convert your project's precise start and end dates into Excel's numeric format (General or Number) so you know the exact 5-digit serial numbers to input into the axis bounds.

Solution 1Recommended

Adjust Axis Bounds and Major Units Manually

Configure the horizontal value axis of your stacked bar chart by setting custom date serial numbers and approximating a yearly major unit.

Since standard Gantt charts are created using Stacked Bar charts, Excel treats the horizontal timeline as a numerical 'Value Axis' rather than a true 'Date Axis'. You must use Excel date serial numbers to define the start and end of your timeline.

Find your date serial numbers

Select the cells containing your project's start date and end date. Right-click, select 'Format Cells', and change the format to 'General' to view the 5-digit serial numbers representing those dates.

Open Axis Options

Right-click the horizontal axis at the top or bottom of your Gantt chart and select 'Format Axis' from the context menu to open the formatting pane.

Set the Minimum and Maximum bounds

In the 'Axis Options' section, enter your start date's serial number into the 'Minimum' field and the end date's serial number into the 'Maximum' field.

Set the Major unit to approximate years

Change the 'Major unit' value to 365. If your timeline covers multiple leap years, using 365.25 can sometimes yield a more balanced interval display.

Adjust Axis Bounds and Major Units Manually

Dealing with Leap Year Variations: Because calendar years vary between 365 and 366 days, using a fixed numerical major unit on a value axis may cause your labels to drift slightly over a multi-year span (e.g., January 1st might slowly shift to December 31st).

Solution 2

Use a Chart Type with True Date Axis Support

If perfect calendar-year intervals are strictly required, use a Column or Line chart which natively supports a dedicated Date Axis, though this alters the traditional Gantt visual style.

Manage Project Timelines Easily

Create and Format Gantt Charts in WPS Spreadsheet

WPS Spreadsheet offers powerful, easy-to-use charting tools fully compatible with Microsoft Excel. Easily build stacked bar Gantt charts, handle complex date serial numbers, and customize axis bounds to manage your project milestones effectively without hassle.

1. Insert a Stacked Bar Chart: Open your project data in WPS Spreadsheet, select your data range, navigate to the 'Insert' tab, and click 'Chart' to insert a Stacked Bar chart.
2. Open Axis Properties: Double-click the horizontal axis of the chart. The 'Format Axis' pane will instantly appear on the right side of the screen.
3. Input Custom Bounds: Under 'Axis Options', type your specific date serial numbers into the Minimum and Maximum bound fields.
4. Apply Yearly Intervals: Adjust the Major unit to 365 to instantly generate a yearly interval spacing on your project timeline.

100% compatible with Microsoft Excel (.xlsx) chart formatsIntuitive right-side Format Object pane for quick axis adjustmentsLightweight application with smooth performance for large project datasetsFree to download and fully equipped for professional data visualization

microsoft office alternative - wps office

Frequently Asked Questions

Why can't I select 'Date Axis' on my Gantt chart?

In Excel, Gantt charts are typically made using horizontal Stacked Bar charts. Horizontal bar charts treat the horizontal axis strictly as a numerical value axis. The 'Date Axis' option is only available for the horizontal axes on vertical Column, Line, or Area charts.

How do I find the date serial number in Excel?

To find the serial number, simply type your date into a cell (for example, '1/1/2024'), right-click the cell, and select 'Format Cells'. Change the number format category from 'Date' to 'General' or 'Number'. The 5-digit number that appears is the date serial number.

Can I prevent axis labels from drifting due to leap years?

Because a bar chart uses a fixed value axis, it is difficult to completely prevent drifting over long periods using a static 365-day major unit. You can try setting the major unit to 365.25 to minimize the drift. For absolute precision, advanced users often create a hidden 'dummy' series using an XY Scatter chart to place custom data labels exactly where calendar years begin.

Excel & Data Problems