Create an Excel Gantt Chart with Category-Based Colors
Question details
Create a monthly product roadmap in Excel with a different color for each category.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user has product names, categories, start dates, end dates, and durations and wants to display them in a Gantt-style roadmap.
- Observed behavior
- Products should appear in rows, months in columns, and scheduled months in the color assigned to each product category.
Arrange products in column A, categories in B, start dates in C, end dates in D, and durations in E. Make sure the start and end dates are real Excel dates, and reserve column F onward for monthly headers.
Build a monthly Gantt grid with conditional formatting
Use formula-based formatting to color every month that overlaps a product's schedule.
This approach creates a Gantt-style grid rather than an Excel chart object. The formulas use start and end dates; the duration column is optional.
Enter the first day of the first month in F1, such as 1/1/2025. In G1, enter =EDATE(F1,1), then fill the formula to the right. Format the headers as months using Home > Number Format.
Select the cells starting at F2 and extending across all month columns and down through all product rows. Keep F2 as the active cell when creating the rule.
Choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter =AND($B2="Category1",$C2<>"",$D2<>"",F$1<=$D2,EOMONTH(F$1,0)>=$C2), choose Format > Fill, select a color, and click OK.
With the same grid selected, create another formula rule for each category. Replace Category1 in the formula with the exact category name, such as Category2, and choose a different fill color.
Use Home > Format > Column Width to narrow the monthly columns. Check that each rule's Applies to range covers the entire schedule grid under Conditional Formatting > Manage Rules.

Make a category-colored Gantt grid in WPS Spreadsheet
Use formula-based conditional formatting in WPS Spreadsheet to turn product dates into a monthly, color-coded roadmap.
- 1. Open your schedule: Open the workbook in WPS Spreadsheet and arrange product, category, start date, and end date in columns A through D.
- 2. Create the month headers: Enter the first day of a month in F1. Enter =EDATE(F1,1) in G1, fill it to the right, and format the headers as months.
- 3. Add a category rule: Select the schedule grid beginning at F2. Choose Home > Conditional Formatting > New Rule, select the formula-based option, and enter =AND($B2="Category1",$C2<>"",$D2<>"",F$1<=$D2,EOMONTH(F$1,0)>=$C2). Set a fill color and confirm the rule.
- 4. Add colors and save: Repeat the rule for each category with a different fill color, then use File > Save As to save the workbook as an XLSX file.

Frequently Asked Questions
Why isn't a product's month being colored?
Check that its category exactly matches the rule, its start and end dates are real dates, and the rule's Applies to range includes the cell. Check the range under Home > Conditional Formatting > Manage Rules.
Will a month be colored if a product starts halfway through it?
Yes. The formula colors a month when any part of the date range overlaps it, provided the header contains the first day of that month.
Do I need a duration column for this roadmap?
No. The conditional formatting rules use the start date in column C and the end date in column D. You can keep duration in column E for reference.
How do I add another product category?
Select the schedule grid and add a rule under Home > Conditional Formatting > New Rule. Replace Category1 in the existing formula with the new category name and select a different fill color.




