Create Gantt Chart with Month/Date Columns

Dear Levigarret, 

Thanks for reaching out again! As I researched, to ensure your dataset works smoothly with an Excel Gantt chart template, we need to adjust the format so that Excel can interpret the project timelines correctly. 

Key Adjustments Needed 

Your current dataset is structured in a wide format, with months as column headers. However, most Gantt chart templates require a long format, where each row represents a task with its start date and duration. 

Automated Method for Reformatting 

To transform your data efficiently, I recommend using Power Query in Excel: 

1. Load Data into Power Query

• Select your table > Go to Data > Click From Table/Range.

1. Unpivot Month Columns

• In the Query Editor, select all columns except Project, Product, Employee, Role, and Activity.
• Right-click > Unpivot Other Columns.
• This will restructure your data into rows like: Project 4 – A | RE1 Mech | Month = Jan-2025 | Hours = 75

1. Filter Out Empty or Zero Values

• Click the dropdown on Hours, uncheck 0 and null values to keep only active work periods.

1. Group Data to Find Start and Duration

• Go to Home > Group By >
• Group by: Project/Product/Employee/Role/Activity
• New column “Start” = Min of Month
• New column “End” = Max of Month
• Add a Custom Column to calculate duration:

m 

Duration = List.Count({[Start]..[End]})  

1. Close & Load Data Back to Excel

• You now have a structured table with Task, Start Date, and Duration, ready for a Gantt chart template.

If you prefer staying within Excel formulas, you’d need a dynamic array formula that: Extracts non-zero (Task, Month) pairs using FILTER(). Identifies the first and last month per task. Calculates duration 

This method is more complex, so Power Query is the recommended approach for automation. 

I hope my information helps, if you have any further questions then don't hesitate to reach out again. I’m happy to assist! 

Best regards, 

Kristen - L - MSFT | Microsoft Community Support Specialist.

Thanks Herbert,

This looks to be a useful macro, but it doesn't exactly do what I am asking. I do nto need to avoid overlap or account for time off. I simply want to show what is in the spreadsheet screenshot as a gantt chart.

Excel 365 Pro Plus with Power Pivot and Power Query.

Do all the things that Sora-NG recommends.

This example is limited to two months.

With 25 activities, regular start times and hours during working hours.

Accounts for regular working hours, holidays and weekends.

With Pivot Tables and Pivot Charts.

https://www.mediafire.com/file_premium/pkad0s7y6zjuzad/02_27_18.xlsm/file

https://www.mediafire.com/file_premium/ya7d485ba2tx4b7/02_27_18.pdf/file

Thanks for you response Sora.

I have updated my post with a screenshot. I think it is adequately formatted.

My primary question is how to format the data in a manner that would facilitate using a Excel Gantt template. I think the dates would need to be in the rows themselves. I need to create an automated method for reformatting the data. I have played with using =SORT(CHOOSECOLS(UNIQUE(FILTER( but have been unsuccesful.

Dear levigarret,

Thanks for reaching out with your request to create a Gantt chart showing project assignments by employee across months. I understand you're working with a dataset where each row represents a project and employee, and the columns represent monthly hours.

Issue Identified

The data you provided appears to have inconsistent formatting, some rows have fewer columns than others, which causes errors when trying to generate a chart. This is likely due to missing month entries or uneven spacing in the pasted data.

What You Can Do

To proceed smoothly, please ensure:

• Each row has the same number of columns (including all months from Jan-2025 to Apr-2026).
• Empty cells should be filled with 0 or left blank consistently.
• The header row should match the number of columns in each data row.

Once the data is cleaned up, you can:

1. Use Excel or Google Sheets to organize the data.
2. Import it into a visualization tool like Power BI, Excel Gantt template, or Python (with Plotly or Matplotlib) to generate the chart.

We sincerely appreciate your cooperation and are happy to assist further. Please do not hesitate to reach out via private message if you have any additional details or screenshots that could help refine troubleshooting.

To protect forum policies and sensitive information, please share the information requested in the "View Direct Messages" section above. 

Here are the steps to open View Private Messages: 

To view private email options, sign in to Microsoft Community> use the email account that posted this thread information to Microsoft Community> > click Profile in the upper-right corner, and then select My Profile.

Feel free to send a private message with any additional details (e.g., screenshots, version info).  

Looking forward to your reply!  

Let me know how you'd like to proceed, and I’ll be happy to assist further!

Best Regards,

Sora-NG - MSFT | Microsoft Community Support Specialist.