Skip to content
ArumSoft Learning
KO
    Excel templateProject management

    Excel Gantt Chart Template: Bars That Draw Themselves from Your Task List

    Type each task with a start date and a duration. The end dates, status and Gantt bars are calculated for you, on a daily or weekly timeline that starts wherever you like.

    Sheets:
    5
    Works with:
    Excel for Microsoft 365, Excel 2016 or later, Excel for Mac and Excel for the web
    Updated:

    Gantt Chart Maker.xlsx

    Excel workbook (.xlsx) · 86 KB · 5 sheets

    Download template

    Free to download. No sign-up needed.

    What you can do with it

    • Turn a task list into a Gantt chart without drawing anything
    • Switch between a daily and a weekly timeline
    • See done and remaining work in each bar, plus milestones
    • Count durations in calendar days or workdays
    Start Here dashboard of the Excel Gantt chart template showing tasks, milestones, project start and finish, overall progress and a progress by phase chart
    The Start Here dashboard with the sample website project that comes in the file.

    What's inside

    The workbook has 5 sheets. You only type on a few of them; the rest calculate.

    SheetWhat it's forYou enterCalculated for you
    Start HereDashboard with six key numbers and progress by phaseNothingTasks, milestones, project start and finish, overall progress, late tasks
    TasksOne row per task or milestone (room for 60)Phase, task, owner, type, start, duration, % completeEnd date and status (Complete, In Progress, Late or Not Started)
    GanttThe chart: 52 columns of days or weeksFirst date (B3) and scale (D3)Bars, milestones and a red column for today
    ListsDropdown choices: phases, owners, types and settingsYour phases and team namesNothing
    ChecksSix schedule checksNothingOK or Review for each check

    Before you start

    The template works as soon as you open it. Cell colors tell you what each cell is for:

    Pale yellowYour entries. Type or paste your data here.
    GreenFormulas. They update on their own, so don't type over them.
    WhiteLabels and optional notes.
    1. Open the file. If Excel shows a yellow Protected View bar, click Enable Editing. Files downloaded from the internet always open this way.
    2. Save your own copy with File then Save As so you always have the original to start over from.
    3. Clear the sample data. Replace the sample tasks on Tasks with your own. Clear contents only (press Delete); don't delete rows, because each row on Gantt shows the task in the same row on Tasks.

    Step-by-step guide

    Work through these in order the first time. After that, you'll mostly repeat the regular update steps.

    1. Set up phases and owners

      On Lists, replace the sample Phases (Plan, Design, Build and so on) with the stages of your project, and the sample Owners with your team. The dropdowns on Tasks update right away.

      Leave Types, Scale and Duration Counts as they are; formulas look for those exact words.

    2. Add your tasks

      On Tasks, add one row per task:

      • Phase and Owner: pick from the dropdowns.
      • Task: a short name. It becomes the label on the Gantt chart.
      • Type: Task, or Milestone for a single date such as a sign-off or launch.
      • Start: the first day of the task.
      • Duration (days): how long it takes, for example 5. Milestones don't need one.
      • % Complete: type 40% or 0.4.

      End and Status fill in on their own. Status shows Late (in red) when the end date has passed and the task isn't 100% complete.

    3. Choose calendar days or workdays

      Above the Tasks header, cell F3 sets how durations count. With Calendar days, a 5-day task that starts on a Thursday ends on Monday. With Workdays, Saturdays and Sundays are skipped, so the same task ends the following Wednesday.

    4. Read the Gantt chart

      Gantt lists every task with a bar across the timeline. Dark green is the part that is done (based on % Complete), light green is what is left, and a ◆ marks a milestone. The column for today is shaded red.

    5. Move the timeline or change the scale

      Type the first date you want to see in B3 on Gantt. Leave it blank to start at the earliest task. Pick Week or Day in D3:

      • Week: each column is a week starting on Monday, so 52 columns cover a year.
      • Day: each column is one day, so you see about seven weeks in detail. Weekends are shaded grey.

    6. Update progress each week

      Once a week, change % Complete for the tasks you worked on. Bars, status and the dashboard update instantly. If a task slips, change its Start or Duration rather than its end date.

    7. Check the dashboard and Checks

      Start Here shows the number of Tasks and Milestones, the Project Start and Project Finish, Overall Progress and Late Tasks, plus a progress bar for each phase.

      Checks flags tasks with no start date, duration or owner, % Complete outside 0 to 100%, late tasks, and a project that runs past the 52 columns of the Gantt sheet.

    How the formulas work

    You don't need to understand the formulas to use the template, but they're a good way to learn. Click any cell in Excel to see its formula in the formula bar.

    End dateTasks!G5
    =IF(OR(B5="",E5=""), "", IF(D5="Milestone", E5,
   IF($F$3="Workdays", WORKDAY(E5, MAX(1,N(F5))-1), E5+MAX(1,N(F5))-1)))

    A milestone ends on its start date. Otherwise the end is the start plus the duration minus one day, because the start day counts as day one. WORKDAY does the same while skipping weekends.

    Learn this: How to Use the IF Function in Excel →
    StatusTasks!I5
    =IF(B5="", "", IF(N(H5)>=1, "Complete", IF(AND(G5<>"", G5<TODAY()), "Late",
   IF(N(H5)>0, "In Progress", "Not Started"))))

    A task at 100% is Complete. Otherwise its end date is compared with TODAY(), so a task turns Late the day after it should have finished.

    Learn this: How to Use TODAY and NOW in Excel →
    Gantt barGantt!F5
    =IF(AND($C5<F$4+IF($D$3="Day",1,7), $D5>=F$4),
   IF(MAX(F$4,$C5)<=$C5+ROUND(($D5-$C5+1)*$E5,0)-1, "d", "r"), "")

    Each cell checks whether the task overlaps that column's day or week. If it does, it returns d (done) or r (remaining) depending on % Complete. Conditional formatting turns d dark green and r light green and hides the letters. The full formula also handles milestones and empty rows.

    Learn this: How to Use Conditional Formatting in Excel →
    Overall progressStart Here!B10
    =SUMPRODUCT((Tasks!$D$5:$D$64<>"Milestone")*(Tasks!$B$5:$B$64<>""),
   Tasks!$F$5:$F$64, Tasks!$H$5:$H$64) / SUMPRODUCT(..., Tasks!$F$5:$F$64)

    A weighted average: each task's % complete counts in proportion to its duration, so finishing a 20-day task moves the number more than finishing a 2-day one. Milestones are left out.

    Learn this: SUMPRODUCT function playground →

    Tips and limits

    • Capacity: 60 tasks. To add more, extend the formulas on Tasks and Gantt down, and change $64 to your last row with Ctrl+H (set Look in to Formulas and Within to Workbook).
    • Sorting Tasks also reorders the Gantt chart, because each Gantt row shows the task in the same row. Sort by Start to get a classic staircase layout.
    • The template has no task dependencies. If one task can't start until another ends, set its Start to the day after the other task's End.
    • To print the chart, use the Gantt sheet. It is set to landscape and fits one page wide.

    Learn the skills behind this template

    TutorialHow to Use Conditional Formatting in ExcelTutorialHow to Use the IF Function in ExcelTutorialHow to Use TODAY and NOW in ExcelTutorialHow to Use Data Validation in ExcelPlaygroundSUMPRODUCT function playground

    Was this guide helpful? Spot a mistake? Send feedback · admin@arumsoft.com