Gantt charts are a type of bar chart often used to communicate project schedules by showing how tasks follow on from each other in sequential fashion. More details on Gantt charts can be found here.

Gantt charts can be incredibly detailed and sophisticated – programs like Microsoft Project should be used if a high degree of complexity, asset allocation, etc. is required. However, in many cases a simple chart in Excel would suffice. QuickChart’s Gantt chart functionality makes these charts easy to produce.

Simple Gantt Chart

Charts of this type can easily be created with a single-click. The data just needs to be arranged in three columns. The first column contains the names of the tasks to be charted. These should be grouped together in sequential fashion.

The second column marks the start of each parallel phase of activity. In the chart shown there are two parallel groups – Tasks A1-A8 starting on 1/1/2020 and Task B1-B4 starting on 8/2/2020. To achieve this, assign a start date to the first task in a group in the second column.

The third column lists the number of days that each task will take to complete. These correspond to the length of the bars in the chart. Which days are considered to be “working days” can be configured in the Settings dialog box (see Settings below).

To create the chart, just highlight the table and press the Gantt button on the Excel ribbon toolbar.

Creating a Single-Click Gantt Chart

You can also choose to label the tasks with the number of days (total elapsed and actual working days) in the Settings.

Gantt Chart with Labels Denoting Elapsed and (Worked) Days

The single-click Gantt chart is not “live”, which means that it won’t update automatically with changing data. However, this can be achieved using the “=QC.GANTT(…)” formula.

Using the “=QC.GANTT(…)” Formula

The “=QC.GANTT(…)” formula takes three parameters:

  • The list of tasks, with or without the headings.
  • An array of working days – {2,3,4,5,6} is equivalent to Monday to Friday (using the Excel day numbering convention where Sunday = 1). This parameter could also set to an Excel range containing the relevant day numbers.
  • A list of holiday dates – these are extra dates to be added to the length of tasks. If you have no dates, just enter 0 as the final parameter.

The formula returns an array with the same number of rows as the source data and with four columns – The task name, the start date, the task length (including non-work days and holidays) and the data for task labels.

Like most of the other QuickChart formulae, this is an array formula, so you must use “Ctrl-Shift-Enter” when you complete it, and select the full range required (although the most recent versions of Excel provide much better support for array formulae). More details on array formulae here.

Once you’ve entered the formula, just select a cell in the formula range and press the “Gantt” button on the ribbon (as you do to make the single-click version) or right-click and the “Make Gantt Chart” option should be at the top of the context menu.

Settings

The Settings for Gantt Charts are accessed through the Gantt drop-down menu on the Excel ribbon toolbar.

Gantt Chart Settings

The settings are as follows:

  • Included Days – Affects the single-click charts only and changes which days should be included for calculating the total time elapsed for a task
  • Fix Minimum – This fixes the minimum date on the X-axis such that the first task starts at the left-most point on the chart.
  • Label Tasks – Adds labels showing total days elapsed and the actual working days, e.g., 10 (6) implies that 10 days have elapsed, of which 6 were working days. This could occur if the previous task ended on a Friday. The following weekend would be counted within the time of the current task, so there would be 6 working and 4 non-working days in the period.