'%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)
Fix Excel Today-Date Line Not Covering All Tasks in Charts
Question details
A vertical today-date line in an Excel stacked bar chart (often used for Gantt charts) fails to extend across the entire vertical height of the chart, leaving some tasks uncovered.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Creating a project timeline or Gantt chart and adding a vertical indicator line to highlight the current date.
- Observed behavior
- The vertical today-date line stops short and does not reach the top of the chart area or intersect all task bars.
Make sure your chart data is fully selected and unhide any secondary axes temporarily so you can accurately adjust their bounds.
Adjust the Secondary Vertical Axis Bounds
Configure the secondary vertical axis bounds to match the scale of your tasks so the date line stretches across the entire chart area.
When adding a vertical line to a horizontal bar chart (like a Gantt chart), the line is usually plotted on a secondary axis. If the maximum bound of this secondary axis is higher than the line's data values, the line will fall short of the top of the chart.
Click on your chart, go to the 'Chart Design' tab, select 'Add Chart Element', hover over 'Axes', and click 'Secondary Vertical' to make it visible.
Right-click the newly displayed secondary vertical axis (usually on the right side of the chart) and select 'Format Axis' from the context menu.
In the Format Axis pane under 'Axis Options', set the Minimum bound to 0 and the Maximum bound to 1 (if your date line Y-value is 1). Press Enter to apply.
To keep the chart clean, do not delete the axis. Instead, in the Format Axis pane, expand the 'Labels' section and set 'Label Position' to 'None'.
Verify the Today-Date Series Data Range
Ensure the data points feeding into your today-date line are correctly configured and referencing the right cells.
Reverse the Category Axis Order
Fix alignment issues by ensuring your primary task categories are plotted in reverse order, which is standard for Excel Gantt charts.
Create Flawless Gantt Charts in WPS Spreadsheet
WPS Spreadsheet provides intuitive and precise chart formatting tools, making it exceptionally easy to build advanced project timelines. Managing secondary axes and custom data lines is straightforward, ensuring your today-date lines always align perfectly.
- 1. Insert a Stacked Bar Chart: Open your project data in WPS Spreadsheet, select your task dates and durations, go to the Insert tab, and choose a Stacked Bar Chart.
- 2. Add the Date Line Data: Right-click the chart, choose 'Select Data', and add a new series for the Today Date, ensuring it points to your current date cell.
- 3. Adjust Secondary Axis Settings: Change the Date series to a Scatter with Straight Lines on the Secondary Axis. Right-click the secondary vertical axis, set its Maximum bound to 1, and your line will instantly cover all tasks.
Frequently Asked Questions
Why is my secondary axis missing in Excel?
A secondary axis only appears if a data series is assigned to it. Right-click a data series in your chart, select 'Format Data Series', and check the 'Secondary Axis' bubble. Once assigned, you can view and adjust its bounds via the 'Add Chart Element' menu.
How do I add a vertical line to a horizontal bar chart?
The most reliable method is to add a new data series using an X-Y Scatter plot type. Assign this scatter series to the secondary axis, set the X-value to your specific date or target number, and use Y-Error Bars (or Y-values from 0 to 1) to create the vertical line.
Does checking 'Categories in reverse order' break my secondary axis?
It doesn't break it, but it moves the horizontal axis to the top of the chart by default. You can fix this by right-clicking the primary or secondary horizontal axis, selecting Format Axis, and adjusting the 'Horizontal axis crosses' setting to reposition it at the bottom.
Why does my today-date line shift when I add new tasks?
If you add new tasks, the primary axis scale changes, but your secondary axis might be static. Ensure your secondary Y-axis maximum is dynamically linked to the task count or locked to 1 (if using a 0-1 coordinate system for a scatter line).
