Creating Gantt Charts Using MS Office Excel

If you have been working on a project, you have probably heard about Gantt charts. Gantt chart is a type of chart that shows the timeline of the tasks detailed in a project. Additionally, Gantt charts are often included with details such as; the assignee and progress for software development projects. Start and end dates are common to all Gantt charts.

Recently, while I was looking for a tool to create my Gantt chart, websites such as TeamGantt, Monday, Canva, etc., emerged in my search results. However, as a beginner in creating Gantt charts, I found it a bit complicated but more detailed yet, precise. Considering the time I have allocated for creating Gantt charts, I worry that learning how to manipulate the tools on the websites could take a bit of time. So, I decided to use the tool that I am very familiar with- and that is Microsoft Office Excel.

Let me walk you through my Gantt chart creation journey for the Student Information System project using MS Office Excel.

Task Detailing

Before I get on with creating the Gantt chart itself, the first thing I did was identify what tasks I will do for the project. After listing all the tasks my team and I will have to undergo, I also set the expected start and end date with the default date data type. The timeline for each task includes extra time for debugging and reworks. If the definition of scope and limitation takes four days to finish, I also gave it additional four days for reworks and crisis control.

The picture below shows the list of tasks, including the start and end date of each task.

Setting the Timeline into Dynamic

It is important to note that Excel is an excellent tool for calculations and algorithms, great for automation. When creating the timeline, I started with the dates that will be our basis for understanding the Gantt Chart’s timeline. Columns A to F of my sheet is allocated for the task details. So, my timeline started in Column G, Row 5 (G5).

For the dates, this is the information that I would like to show;

Note:

02-Nov-20 — is contained in cell G4
2,3,4,5,6,7,8- is contained in cell G5, H5, I5, J5, K5, L5, M5 respectively
M, T, W, T, F, S, S- is contained in cell G6, H6, I6, J6, K6, L6, M6, respectively

To do this, I first set the second row with the start date, which was November 2, 2020. Then, I wanted excel to only pick up the day of that date by setting the number format to custom- type-“d” or “dd.” Type d will not show 0 before single-digit days like 2 to 9. The type dd will show 0 before the single-digit days showing it as 02 to 09.

Although the cells on the second row only show the days, these cells contain the date data. Thus, to set the first row, we will only link the date from the first day of the week using the formula =G5 (where G5 is the cell containing the day 2 in the second row). After the cell G4 is linked to cell G5, we will then change the cell’s number format to its default data type by clicking CTRL+SHIFT+3. It will now revert to its default data type showing the full date: 02-Nov-20.

Then, I worked on the third row, which contains the first letter of the days of the dates in the second row. To do this, I used the formula: “=LEFT(TEXT(G5,”ddd”),1)”

The text function here converts the day (ddd) value to text. The left function here picks up the number of characters letter from the left, which is set to 1. Thus, the left function picks up the first letter from the left and returned it as the cell’s value.

After finishing the date formats’ set-up, it is now time to automate the start date of the project if there might be some revisions. In cell C2, I typed in “Project Start:” and have the cell D2 be the project start date container. I also made sure to place cell D2 in a thick border-box so any users could understand that the cell data is changeable.

For cell D2, I set it to a long date number format, so it returns the date’s complete details. Then, I will set cell G5 to pick up the project start date as its date by linking cell G5 to cell D2 using the formula “=D2” (in cell G5). The next cell, H5, will then be used with the formula “=G5+1”. This automatically increments the date to 1 from the previous cell. Then, I dragged to copy the formula from cell H5 to cells I5 to M5. Now, whenever the date in cell D2 changes, G5 will automatically adjust the date it contains. Then, the succeeding dates will also change automatically.

After finishing the formula for the dates, I then copied the cell columns’ formula and format by highlighting the seven date columns (Columns G-M). Then dragged it to the next column while pressing CTRL + SPC. If the column you copied did not show an increment in dates, make sure to set the auto-fill options to “Fill Series”- this option will automatically adjust your cells’ formula to increment the first copied data from the last data of the original.

Formatting

To make my chart easy on the eyes, I set excel to not show the gridlines by going to the View tab and unchecking gridlines. This will help me view my work without any lines that are not part of the chart.

Then, I set the task rows’ borders to have borders on the top and bottom in gray color. Not placing borders on the left and right side gives the user the idea that there are still next lines of data, which will be the case for this Gantt chart.

The fill colors for my dates were set in different gray hues to make the dates different from the timeline, but this difference will also be not too much. I simply want to make my chart look more on the mute side, so the reader’s eyes could focus on the timeline itself.

Formulating the Bars

Now let’s proceed to how I created the bars. For the Gantt chart, as you may have observed, we can simply fill the cells manually. But, this can be time-consuming and also prone to errors for complicated tasking. Thus, I have set the bars to automatically fill the cells depending on the task’s start and end dates.

To do this, we are going to make use of Excel’s conditional formatting.

1. First, highlight the cells of the timeline.

2. Then, click on Conditional Formatting (which can be found in the Home tab).

3. Then click on New Rule…

4. You will then be prompted to select a Rule Type. Select the last option.

5. In the formula bar, I inputted the formula “=AND(G$5>=$D7,G$5<=$E7)”.

a. Where G5 is the cell containing the leftmost day date.

b. Where D7 is the start date, and

c. E7 is the end date

The formula means that if the cell is greater than or equal to the start date AND less than or equal to the end date, it will return the value true. So, the cell is formatted with a blue fill.

6. Then, click format to set the style for the cell. In my case, I only set a midnight blue fill for the cells, which will prove the formula true.

7. Click, OK.

After clicking OK, the cells automatically colors, which shows a midnight blue filled cell for each task’s inclusive dates. Whenever you adjust the start and end dates, the bars also automatically adjust so, you won’t have to worry about changing the timeline’s bars.

Current date identifier

To effectively use my Gantt charts, I want the Gantt chart to have the current data determiner. To do this, I am again going to make use of the conditional formatting. I want my current date identifier to simply change the fill color of the day date to red.

As you can see in the image below, The current date, 01/12/2020 is highlighted in red.

1. Highlight the cells which you wish to be formatted. By which I chose to highlight only the day date cells for a simple identifier.

2. Click on conditional formatting.

3. Click on New Rule…

4. On the Edit formatting rule prompt, choose the last rule type.

5. In the formula bar, I put “=G$5=TODAY()”.

6. Then, click on format and set it as you like, which I have set to have a red fill

7. Click, OK.

The today function of Excel returns the current date value. So, I have used a formula that will simply compare the current cell to the current date. If the cell contains the current date value, the formula will return the value true. Thus, the current date will be filled with red color (which by default, day date cells are gray colored).

Progress Column

Now that I have finished working on the timeline, I also want the chart to show the progress data. I set column C to contain progress data. However, I also want those cells to have a more visual representation of the progress. To do this, I used conditional formatting again.

1. Highlight the cells containing the progress data.

2. Click on conditional formatting.

3. Click on Data bars.

4. Set minimum and maximum to number data types.

5. Set the minimum value to 0 and the maximum value to 1.

6. For the bar appearance, I chose the bar to have the color blue-gray.

7. Click, OK.

Now, when I put a 100% progress, the progress bar will fill 100% of the box.

Automating Display Weeks

I have mentioned earlier that there are possibilities for project start changes. However, I want to make sure that my starting day always starts on Monday. So, I am going to create a formula for this.

1. First, I clicked on cell G5.

2. Input the formula “=$D$2-WEEKDAY(project_start,3)+(display_week)*7”.

3. Then, I named cell D2 to project_start and D3 as display_week using the name box.

4. In the formula tab, I clicked on the name manager and created the following names:

a. task_end — Scope: current sheet, refers to end date cell (=Sheet2!$E11).

b. task_progress- Scope: current sheet, refers to progress cell (=Sheet2!$C11).

c. task_start- Scope: current sheet, directs to start date cell (=Sheet2!$D11).

Progress showing in the timeline bars

Because the progress chart was a success, I also wanted to incorporate it into the timeline bars. So, I used conditional formatting again.

1. Highlight all the cells of the timeline bars.

2. Click on Conditional Formatting.

3. Click on New Rule…

4. In the Edit Formatting Rule prompt, choose the last option.

5. Use the formula “=1*AND(G$5>=task_start,G$5<=task_start+(task_progress*(task_end-task_start+1))-1)

6. Format the progress cell accordingly which I formatted to blue gray fill.

7. Click, OK.

What happens now is, when I type a 100% progress for a task, say, the definition of scope, the bar will now be a full-colored blue-gray instead of midnight blue. This will help the viewer understand that the task is already done even without viewing the progress column. The viewer can also be informed that one task is already near the deadline, yet the progress is still lesser than the progress percentage expected.

For example, the Designing database task here is still in 20% progress. It is expected to have finished by November 30, 2020, and today is already December 01, 2020. I will then be informed by a single glance with the chart, “Oh, the database design task is not yet done, but the deadline is already overdue!”

Adding the Scrollbar for data viewing

It can be difficult for me to view the timeline from the project start date to the project end date with a very long timeline. So, I added the scroll bar so the data shown to me will be limited to a few data sets.

To add the scroll bar;

1. Add the Developer tab

a. Click on File

b. Click on Options

c. Choose Customize Ribbon

d. Check “Developer” under the main tab list.

e. Click OK.

2. In the Developer tab, click Insert

3. Choose scroll bar

4. Place the scroll bar wherever you think it is suitable.

5. Right-click on the scroll bar

6. Click Format Control.

7. In the Control Tab, indicate the cell link to the display_week or cell D3.

8. I set the maximum value to 200, incremental change 1, page change 0.

9. Click OK.

Now, the scroll bar is useable! I can now scroll weeks with a single weekly incremental change to the data.

That concludes the steps I did in creating the Gantt chart using MS Office Excel.