'%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)
How to Create a Monthly Gantt Chart for Recurring Tasks in Excel
Question details
The user wants to construct a Gantt chart in Excel that displays multiple instances of recurring tasks over a monthly timeline.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Tracking project schedules, employee shifts, or maintenance workflows where the same task repeats multiple times across weeks or months.
- Observed behavior
- The user needs a method to group multiple start and end date ranges under a single shared task name on the chart's vertical axis, while displaying the timeline across the horizontal axis.
Prepare your raw project data by ensuring every recurring task instance has a clear start date and an exact duration calculated in days before building the chart.
Use a Helper Table and Stacked Bar Chart
The most effective method is structuring your data into a helper table to group multiple date ranges under a single task name, then plotting it as a stacked bar chart.
A standard Excel bar chart cannot natively plot recurring dates on the same row. By creating a helper table, you can separate each recurring task instance into its own row while keeping the exact same task name, allowing Excel to plot multiple bars along the same horizontal plane.
Set up three columns: Task Name, Start Date, and Duration (Days). For every time a task repeats, add a new row with the same exact Task Name, its new Start Date, and its Duration.
Highlight the data in your helper table. Go to the 'Insert' tab on the ribbon, click the column/bar chart icon, and choose 'Stacked Bar' from the 2D Bar options.
Right-click the horizontal axis (dates) at the top or bottom of the chart and select 'Format Axis'. Adjust the Minimum and Maximum bounds using the numerical serial values of your project's start and end dates. Set the Major units to roughly 30 to reflect monthly intervals.
Click on the first data series (representing the Start Dates) in the chart. Right-click and choose 'Format Data Series', then navigate to the 'Fill & Line' bucket icon and set 'Fill' to 'No Fill' and 'Border' to 'No Line'. This makes the bars float like a Gantt chart.
Right-click the vertical axis (task names) and select 'Format Axis'. Check the box for 'Categories in reverse order' to display your tasks properly from top to bottom.
Create a Gantt Chart using Conditional Formatting
If setting up a complex chart is difficult, a grid-based conditional formatting approach can visually achieve the same recurring Gantt chart effect.
Build Complex Gantt Charts Easily with WPS Spreadsheet
WPS Spreadsheet offers powerful data management and charting capabilities, making it simple to organize recurring tasks and visualize them through customized stacked bar charts. Enjoy a seamless experience for all your complex project management needs.
- 1. Prepare Data: Open your project data in WPS Spreadsheet and structure your helper table with Task Name, Start Date, and Duration.
- 2. Insert Chart: Highlight the data, go to the 'Insert' tab, select 'Chart', and choose the 'Stacked Bar' option.
- 3. Create Gantt Effect: Double-click the first data series (start dates) and change the fill color to 'No Fill' to reveal your floating timeline.
- 4. Adjust Timeline Axis: Right-click the horizontal axis to configure your minimum/maximum date boundaries and set the intervals to represent months or weeks.
Frequently Asked Questions
Why are there huge gaps before my Gantt chart bars start?
Spreadsheet charts start the date axis at a default zero value (January 1, 1900) if bounds aren't explicitly set. To fix this, find the numeric serial value of your earliest start date, right-click the horizontal axis, choose 'Format Axis', and enter that number as the Minimum bound.
How do I change the timeline to show months instead of days?
Right-click the horizontal axis and select 'Format Axis'. Under the Axis Options menu, change the 'Major unit' from a small number of days to approximately 30 days to represent monthly gaps. You can also right-click the axis, select 'Number Format', and change it to 'mmm-yy' (e.g., Jan-24).
Why do my tasks appear in the wrong order on the vertical axis?
By default, bar charts place the first category from your data table at the bottom of the chart. You can reverse this by right-clicking the vertical axis, selecting 'Format Axis', and checking the box labeled 'Categories in reverse order'.
Can I add task labels directly onto the Gantt bars?
Yes. Click the visible task bars in your chart, click the chart elements '+' icon (or go to Chart Design > Add Chart Element), and select 'Data Labels'. You can then format these labels to show the task name, duration, or specific dates by clicking on the labels and selecting 'Format Data Labels'.
