To make a year-long Gantt chart in Excel, list each task with its start and finish dates, create a row of weekly dates across the year, and use conditional formatting to shade the weeks each task is active. A 54-column weekly timeline gives a clear one-year overview, including the partial weeks at the calendar year’s edges.
What a year-long Gantt chart shows
A Gantt chart places activities down the left side of a worksheet and time across the top. A colored bar in each task’s row shows when that work is scheduled. A year-long version is useful for seeing how projects, launches, maintenance, staffing, or recurring work fit together over a broad period.
“Year-long” can mean a calendar year, a fiscal year, or any rolling 12-month period. Decide which one you need before entering dates. The example below uses a calendar year selected in cell B1. A weekly scale is a practical compromise: it shows sequence and overlap without requiring 365 narrow daily columns. It is not intended to show exact start and finish times or daily handoffs.
Excel does not have a single built-in command that turns a task list into a complete Gantt chart. A reusable worksheet combines ordinary date values, a conditional-formatting rule, and consistent task data. The same design can be adapted for other years without rebuilding the timeline.
Plan the worksheet layout
In a blank workbook, use the following structure. The example allows for 20 tasks; extend the task rows or the formatting range if your schedule is longer.
- B1: the calendar year, such as 2026. You can put a label such as “Year” in A1.
- A4:E4: column headings: Task, Owner, Start, Finish, and Status.
- F4:BG4: weekly timeline dates. These 54 columns provide enough room for the year and its edge weeks.
- A5:E24: task information, with one task per row.
- F5:BG24: the chart area where conditional formatting will display the bars.
Keep start and finish dates in separate columns rather than typing a date range into one cell. Real Excel dates can be sorted, checked, and used in formulas; text such as “Jan 12–16” cannot be compared reliably as a date range. Leave unused task rows blank, and use the same date convention throughout the sheet.
Create the weekly timeline
With the year in B1, enter this formula in F4. It finds the Monday on or before January 1, so the first timeline column includes the week containing New Year’s Day.
With the calendar year in B1, enter =DATE($B$1,1,1)-WEEKDAY(DATE($B$1,1,1),2)+1 in F4. For 2026, the result is December 29, 2025, the Monday of the week containing January 1, 2026.
In G4, enter the next formula and fill it right through BG4.
With the preceding week’s date in F4, enter =F4+7 in G4. It returns seven days after F4; for the example year, that is January 5, 2026. Filling right advances each header by one week.
To fill the formula, select G4, then drag its fill handle across the remaining timeline columns, or copy it and paste across the range. Format F4:BG4 as dates using Home > Number Format; a short format such as “d mmm” keeps headers readable. The first and last displayed weeks may include days outside the selected year. That is expected: the rule below limits the colored work area to weeks that overlap the year.
Enter and check task dates
Type a descriptive task name in column A, an optional person or team in B, and valid start and finish dates in C and D. For example, a task that runs from January 12 through January 16, 2026, has those dates in C5 and D5. Enter dates as dates, not as labels containing extra words. Excel’s displayed date format can vary by regional settings, so check that a value is recognized as a date if it does not sort or calculate as expected.
The Status column can hold simple text such as Not started, In progress, or Complete. It is a note, not a progress calculation; the timeline described here shades the planned date range, rather than measuring percent complete. If you want to show progress, add a separate percentage-complete field and decide how it should be visually distinguished from the scheduled bar.
With the start date in C5 and the finish date in D5, enter =D5-C5+1 in another column if you want an inclusive calendar-day duration. For January 12 through January 16, the result is 5 days: the date difference is 4, and adding 1 counts both the start and finish dates. This is calendar duration, not working days; weekends and holidays are included.
Check that every finish date is on or after its start date. A reversed range will not produce a meaningful bar. If tasks can cross December 31, keep their true dates in the task list; the chart will show only the portion overlapping the selected year. For a project spanning several years, use one yearly view per year or choose a broader time scale.
Add the Gantt bars with conditional formatting
Select the chart area F5:BG24. On Excel for Microsoft 365 on Windows, choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter the formula below, choose Format, select a fill color, and confirm the rule. Apply it to the selected chart area if Excel does not set that range automatically.
With week-start dates in row 4, task start dates in column C, finish dates in D, and the selected year in B1, use =AND(F$4<=$D5,F$4+6>=$C5,F$4<=DATE($B$1,12,31),F$4+6>=DATE($B$1,1,1),$C5<>"",$D5<>"") as the conditional-formatting formula.
The rule checks whether the week overlaps the task’s date range and whether that week overlaps the selected calendar year. The dollar signs keep the timeline row and task-date columns anchored as Excel evaluates the rule across the range, while the week column and task row adjust. The blank-date checks prevent empty task rows from being colored. The formula treats each weekly column as the seven days beginning at its header date.
Choose a fill color with enough contrast against the worksheet, then click OK to save the rule. If you edit or add tasks, the bars update when the date cells change, provided the new task row is included in the rule’s Applies to range. To inspect or adjust that range later, use Home > Conditional Formatting > Manage Rules.
Make the chart easier to read and maintain
Basic formatting makes a wide worksheet much more usable. Keep task names and owners visible while scrolling, and give the timeline columns a consistent width. A light background for the chart area and one strong bar color helps distinguish scheduled work from empty weeks. Avoid relying on color alone for important status information; retain the written Status field or another text cue.
- Freeze the task headings and identifying columns: select G5, then choose View > Freeze Panes > Freeze Panes. This keeps rows 1–4 and columns A–F visible as you scroll.
- Use a date format in the timeline headers, and consider adding month labels in a row above them. Month labels can be typed or merged across the relevant weeks, but review them when changing the year.
- Keep the task list in a consistent order, such as by start date, workstream, or owner. Add a legend if several colors represent different meanings.
- Before sharing, check the selected year, the first and last task dates, and the conditional-formatting range. Confirm that dates around January 1 and December 31 appear as intended.
If you need a different year, change B1 and review the task dates. The weekly headers recalculate, and the formatting rule follows the year in B1. If you need a fiscal year beginning in a month other than January, the simple year-boundary checks in this example need to be redesigned around the fiscal-year start and end dates. Do not label a calendar-year view as a fiscal-year schedule without changing those boundaries.
Common problems and what they mean
No bars appear: check that the task dates are real Excel dates, that the start date is no later than the finish date, and that the conditional-formatting rule applies to F5:BG24 or the expanded task range. Also confirm that the selected year in B1 matches the dates you expect to see.
Bars appear in the wrong rows or columns: check the formula references and the Applies to range in Manage Rules. A copied rule with an unintended fixed row or column can make every task compare against the same dates. The formula above is designed for a rule starting at F5.
The timeline starts in the previous year: that is normal when January 1 falls after Monday. The first column begins on the Monday of that week, while the conditional format colors it only if the week overlaps the selected year and the task dates.
A task looks longer than expected: a weekly chart marks an entire week when any day in that week overlaps the task. A one-day task therefore appears in one weekly cell. Use a daily chart, a separate detailed schedule, or a more precise view when day-level duration matters.
The sheet prints too small: 54 timeline columns are broad for a portrait page. Try landscape orientation and fit-to-page settings, but avoid shrinking the text until it becomes unreadable. For a large schedule, print separate sections or share the workbook for on-screen review instead.
Weekly, monthly, and daily views
A weekly view is usually the clearest single-sheet choice for a year because it preserves enough detail to show overlapping work while remaining manageable on screen. Its trade-off is that it rounds activity to week-sized blocks. It tells the reader that a task touches a week, not which day it begins or ends.
A monthly view is more compact, with 12 time columns, and suits executive summaries or high-level roadmaps. It hides the order of tasks within each month and can make short activities appear as large as long ones. A daily view is more precise, but 365 or 366 date columns are difficult to navigate and print. One alternative is to keep a weekly overview and create a separate daily schedule for near-term work.
Excel’s conditional formatting is convenient for a lightweight, editable chart, but it is not a project-management system. It does not automatically manage dependencies, resource conflicts, approval workflows, or a version history of schedule changes. If those functions matter, compare the workbook approach with a dedicated project-planning tool. Keep Excel when a shared, familiar schedule and straightforward date ranges are sufficient.
Frequently Asked Questions
Is there a free year-long Gantt chart template in Excel?
A workbook you build from the layout and rules above can serve as a reusable template. Excel versions and organizational policies differ, so whether a ready-made workbook is available to you depends on your installation and workplace. The essential chart can be created with worksheet dates and conditional formatting.
Can I make a Gantt chart for a fiscal year?
Yes, but define the fiscal-year start and end dates first. The example uses January 1 through December 31, so its year formula and formatting boundaries must be adjusted for a fiscal year that starts in another month.
Why does a task appear for a whole week when it lasts one day?
Each column represents seven days, and the rule colors a column if even one day overlaps the task. Use a daily timeline for day-level precision or accept the weekly block as a planning-level indicator.
Can tasks span two calendar years?
Yes. Keep the task’s actual start and finish dates in the task list. In a one-year view, the formatting rule shows only the weeks that overlap the selected year; make a second view for the adjacent year to see the rest.
Will the bars update when I change dates?
Yes, the conditional-formatting rule recalculates when task dates change. If you add tasks below the original range, extend the rule’s Applies to range so those rows receive the same formatting.
Related
- Excel Custom Number Formats: How to Create and Use Them
- Unable to Insert an Image in Excel? Causes and Fixes
- Transpose Shortcut in Excel: Paste Rows as Columns
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Gantt Chart Excel Template for Event PlanningA Gantt chart Excel template for event planning lays tasks against calendar dates so you can see what…
- Excel Chart With Two Y-Axes: How to Make OneTo make an Excel chart with two different y-axis values, select the chart, choose Chart Design > Change…
- How to Insert a Dynamic Excel Chart in PowerPointTo insert a dynamic Excel chart in PowerPoint for Microsoft 365 on Windows, select the chart in Excel,…
- How to Add an Average Line to an Excel Chart: StepsTo add an average line to an Excel chart, calculate the average in a helper column, repeat that…
- Excel Stacked Bar Chart: Add, Move, and Format the LegendTo add a legend to a stacked bar chart in Excel for Microsoft 365 on Windows, select the…
- Excel Sparklines: Where to Find Them and How to InsertIn Excel for Microsoft 365 on Windows, go to Insert > Sparklines, then choose Line, Column, or Win/Loss.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.