Chart & Visualization Issues

Create an Excel Gantt Chart with Category-Based Colors

Adam DavisAdam Davis Nov 22, 2026 868 views

Question details

Create a monthly product roadmap in Excel with a different color for each category.

Create an Excel Gantt Chart with Category-Based Colors
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.
Before you start

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.

Solution 1Recommended

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.

1
Add monthly headers

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.

2
Select the schedule grid

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.

3
Create the first category 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.

4
Add the remaining colors

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.

5
Adjust the roadmap

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.

Build a monthly Gantt grid with conditional formatting
How monthly coloring works: A month is colored if any part of the product's date range falls within it. Each header must be the first day of its month, and each end date must be on or after its start date.
Create a product roadmap

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. 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. 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. 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. 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.
Color scheduled months by product categoryWork with Microsoft Excel XLSX filesKeep product details and the roadmap together in one worksheet
microsoft office alternative - wps office

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.