How to Manage Excel Gantt Chart Data Without Breaking Formulas
Question details
The user wants to manage and rearrange Excel Gantt chart data while preserving formulas and conditional formatting alignment.

- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A user is managing a project schedule in an Excel Gantt chart and needs to rearrange or add data without breaking linked formulas and conditional formatting.
- Observed behavior
- The goal is to keep formulas and conditional formatting aligned and updating automatically when Gantt rows are added, moved, or sorted.
Before converting the range, make sure your Gantt chart includes a header row and no fully blank columns inside the data area, because Excel Tables rely on headers and contiguous data.
Convert the Gantt Chart Range to an Excel Table
Use this to keep formulas and conditional formatting aligned when rows are added, moved, or sorted.
Excel Tables apply structured references and automatically extend formatting and formulas to new rows, so row changes do not leave broken references.
Select the Gantt chart data range, including the header row and all project schedule columns.
In WPS Office Spreadsheets, go to the Insert tab and click Table.
In the Create Table dialog, check My table has headers, then click OK.
Add, move, or sort rows within the table; formulas and conditional formatting extend automatically through structured references.

Separate Source Data from the Gantt Grid for Major Schedule Changes
Use this when you need to change working days, staffing, weekends, holidays, or overall project logic without editing the Gantt formulas.
Keep Excel Gantt Chart Formulas Intact with WPS Office Tables
WPS Office Spreadsheets supports Excel Table conversion, structured references, and conditional formatting, so you can manage Gantt chart schedules without breaking formulas.
- 1. Open the Gantt workbook in WPS Office: Launch WPS Office Spreadsheets and open the .xlsx workbook that contains the Gantt chart.
- 2. Select the Gantt data range: Highlight the chart data including the header row and schedule columns.
- 3. Click Insert > Table: Go to the Insert tab and choose Table, then confirm My table has headers and click OK.
- 4. Use structured references in formulas: Update key formulas to refer to table column names so new or moved rows are included automatically.
- 5. Save the file as an Excel workbook: Save as .xlsx to retain compatibility with Microsoft Excel and the structured table.

Frequently Asked Questions
Will converting my Gantt chart range to a Table change existing formulas?
No, existing formulas continue to calculate, but after conversion formulas that use structured references update automatically when rows are added or moved. You can still keep fixed cell references if needed.
How do I add rows to a Gantt chart Table without losing conditional formatting?
Add rows inside the table by right-clicking a table row and choosing Insert Table Rows Above or Below, or drag rows. The table extends formatting and formulas automatically.
Can I use a separate source table for working days and holidays?
Yes, create a separate table for dates, weekends, holidays, staffing, and non-working days, then reference those columns from the Gantt grid so schedule changes stay centralized.
Does this work when the file is later opened in Microsoft Excel?
Yes, WPS Office saves .xlsx files and Excel Tables, so the structured references and conditional formatting remain compatible with Microsoft Excel.
What should I do if my Gantt chart has blank columns inside the range?
Remove fully blank rows or columns inside the range before inserting the table, or include them as part of a contiguous data area so the table can detect headers correctly.




