Keep Gantt Chart Entries Aligned with Dates in Excel
Question details
Keep task or vacation information associated with the correct dates when the dates displayed in an Excel Gantt chart change.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A user changes the week dates displayed across a Gantt chart and wants schedule entries to follow their dates.
- Observed behavior
- Text or numbers typed directly into the chart grid stay in their original cells when the date headers change.
Check which cells contain the timeline dates and the task start and end dates. Move any manually typed grid entries into source columns before replacing those grid cells with formulas.
Use source dates and formulas to populate the Gantt grid
Use this approach when task labels or vacation entries need to follow changing date headers.
Conditional formatting changes cell appearance; it does not move values typed into the cells. Store each entry and its dates in source columns, then let formulas and formatting display it in the grid.
Enter each task or vacation label in column B, its start date in column E, and its end date in column F. Enter timeline dates in row 5, starting at G5.
In G6, enter =IF(AND(G$5>=$E6,G$5<=$F6),$B6,"") and fill the formula across and down the Gantt grid. Each label will appear under headers that fall within its start and end dates.
Select the grid starting at G6, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter =AND(G$5>=$E6,G$5<=$F6), choose a fill color, and apply the rule.
Change a date in row 5 and check that the labels and colored cells update. Adjust the cell references if your source columns or timeline start in different locations.

Use a calendar template for directly editable entries
Use a calendar instead when you want to type events into individual date boxes rather than generate them from task dates.
Keep Gantt Chart Dates and Labels in Sync in WPS Spreadsheets
Use source date columns, formulas, and conditional formatting in WPS Spreadsheets to display task labels against the correct timeline dates.
- 1. Open the schedule: Open WPS Spreadsheets and use Open to select your Excel Gantt chart workbook.
- 2. Organize the source columns: Enter task labels in column B, start dates in column E, end dates in column F, and timeline dates in row 5 starting at G5.
- 3. Add the label formula: In G6, enter =IF(AND(G$5>=$E6,G$5<=$F6),$B6,"") and fill it across and down the chart grid.
- 4. Format matching dates: Select the grid starting at G6. Use Home > Conditional Formatting to create a formula-based rule with =AND(G$5>=$E6,G$5<=$F6), and choose a fill color.
- 5. Check and save: Change a timeline date to verify the display, then choose File > Save As to save the workbook in XLSX format.

Frequently Asked Questions
Why don't my typed Gantt entries move when I change the dates?
The entries are stored in fixed worksheet cells, while changing row 5 only changes the date headers. Put the entries in source columns and use formulas to show them under matching dates.
Can conditional formatting move text to another cell?
No. Home > Conditional Formatting controls appearance, such as fill color. Use a formula in the grid to display a label from a source cell.
How do I show a task that overlaps part of a week?
If G5 is the first day of a seven-day week, use =AND(G$5<=$F6,G$5+6>=$E6) as the condition in the grid formula and conditional formatting rule. It checks whether the task overlaps any day of that week.
Why did a task disappear after I changed the timeline?
The task may fall outside the dates currently shown in row 5. Check its start and end dates in columns E and F, then extend or change the displayed timeline.
Can I type directly into cells that contain Gantt formulas?
Typing into a formula cell replaces its formula. Edit the task label and dates in the source columns instead, or use a calendar template if direct entry is more important.




