How to Make a Gantt Chart in Excel - YouTube

Hello, and welcome to Vertex42. Watch this video to learn how I create this

Gantt Chart template including features like the scroll bar and the ability to

show the progress of each task.

The essential information for a Gantt Chart is a list of tasks with start and end dates.

After making the columns wider, I'll add some data to work with.

I'm going to speed up the video here for a bit.

To use the default date format, I'll use CTRL+SHIFT+3

Then finish the table.

The next step is to create the date range or timeline for the chart area.

I'll start with November 1st for now.

I want to display just the day of the month,

so I'll use the custom number format "d"

It's important to realize that these

cells are storing the full date but only displaying the day of the month.

I want to show the first letter of the day of the week in the cell below the date.

First, I'll use the text function to display the abbreviation for the day of

the week. Then I'll use the left function to grab just the first letter of that text.

Next, I'll merge these seven cells to display the full date of the first day of the week.

and I need to change the date format back to the default.

Now I'm going to select the group of seven columns and copy it to the right.

To make the chart area dynamic,

I'll first add a place to enter the project start date.

Then I'll link the first cell in the chart area to that date.

Then I'll use a formula to add one day to each of the next dates

now when the project start date changes, the chart area will change.

When I start formatting, I usually turn the gridlines off.

I'll insert a couple rows at the top for a title later.

I like table headers that have a dark background with a light font.

I'll reduce the font size of the days and then turn on Shrink-To-Fit.

I'll select the entire worksheet and change the vertical alignment to middle.

I'll add some indenting to the tasks.

Horizontal borders make a Gantt chart easier to read, so we'll put those in now.

I want the project start date to be a longer date format, so I'll merge the cells and make

a custom number format.

I'll display the day of the week, the day, the month abbreviation, and the full year.

I'll add a border to show that this date is meant to be edited.

Next, I'll just make the other chart labels a bit more interesting.

We can condense things a bit more, also.

I'll use the format painter to copy the formatting to the next two weeks.

Now for the tricky part. We could add the bars of

the Gantt chart by changing the fill color manually, but

that's way too time-consuming. Instead, we use conditional formatting to shade

this cell when the date at the top of the column is within the start date and the end date.

First, we'll select all these cells,

Go to "Conditional Formatting" > "New Rule" > then "Use a Formula"

We'll use the logical function "AND" and set this date

greater than equal to the START, comma, the date less than or equal to the END.

It's important to create the formula based on the upper left cell in the range.

We need to let the columns of the top row change

as well as the rows of the start and end dates.

Select the purple theme color.

And that's it! Actually, we have some more cool stuff to do.

So our timeline currently starts on the same day as the project start. But, I wanted to always

start on Monday. First, I'll name cell B3 "project_start" using the name box.

Then, I'll edit this formula to subtract the weekday of the project start.

You can press tab to choose the selected name

We want option 3 where Monday is zero.

Now the weeks in the timeline will start on Monday, regardless.

Some quick cleanup and then I'll add a couple more weeks to the chart.

Press CTRL+SpaceBar to select the column.

Now I'm going to add an input that will let me scroll a Gantt Chart.

CTRL+D copies down. Then I'll change the label to "Display Week"

Start with a 1 and center it. Then I'll add the border.

Now I'm going to name this cell using the name box. Call it "display_week"

Then edit the first date in the timeline again

to add the display_week - 1 times 7.

Now when I change the display week,

the dates in the timeline will update, making the Gantt chart appear to scroll.

The Scroll Bar form control is in the developer ribbon.

To turn on the developer ribbon, go to File > Options > Customize Ribbon.

Then select the developer box.

To insert the Scroll Bar, go to Developer > Insert and select the scrollbar form control.

Then click and drag to draw the scrollbar.

Right-click on it to select Format Control.

Select the cell to link to, which for us is the display week.

Now you can use the scroll bar to change the display

Adding a line to mark today's date is pretty simple.

Go to Conditional Formatting > New Rule > Use a Formula.

The formula is just going to be a comparison of the date at the top of the column

to the function TODAY.

We'll add red borders so we can still see the bars in the chart.

Done. On to the next step.

After inserting new columns, the "Insert Options" button gives options for formatting.

I'll choose "Format Same as Right"

So these cells are formatted as dates,

so I'll change them back to the "General" format.

This first new column is for who the task is assigned to.

I need to turn on "Wrap Text" and reduce the font size a bit.

This column will be for entering the current progress or percent complete.

I'm going to use conditional formatting to add progress bars within these cells.

These are called "Data Bars"

I need to edit the formatting rule to represent 0 to 100%

So I'll set the Type to "Number" and set the Minimum to 0 and the Maximum to 1.

And I'll make it gray so it doesn't stand out so much.

We need to move these labels now. Cut and Paste.

I'll add the project title and placeholders for company name and project lead.

I want to represent the progress by shading a portion of the bar with a conditional formatting rule

that works essentially like this:

where 1s are shaded and 0s are not.

This is kind of hard to see so I'll change the font to white.

I'm going to create this formula in stages using names to make it easier to read.

The date at the top of the column needs to be within the start and end dates,

But, now the end is calculated by adding the start date plus days_complete - 1 day.

and I need the date row to remain fixed (add a $ sign).

This formula doesn't work yet because I haven't defined the names

I'll go to Formulas > Name Manager > New.

Enter task_start, Set the Scope to this sheet,

Then select the start cell and make the row number relative (no $ sign)

I'll create one for task_end as well.

These are what we call "relative named ranges"

I'll create one more for the task_progress column.

Now I need to expand the day's complete to finish the formula.

This is the task_progress times the total days which is task_end - task_start + 1.

Ah, I forgot to convert the boolean to 1s and 0s.

There we go. I'll copy that over and test it out.

Looks like it's working, so I'll copy the formula.

Go to Home, then I need to hit ESC because I'm still in edit mode.

I'll select the range and then add a new conditional formatting rule.

I'll paste the formula then choose a gray fill color.

Great! I'll clear these cells then try it out.

The last thing I'll do is define the print settings.

A Gantt Chart usually works better in landscape orientation.

I set margins to Narrow, but I want the top and bottom margins smaller,

so I'll change them to half an inch.

Then change the scaling to fit on one page wide

and I don't want to print the scroll bar.

With objects like the scrollbar, you can Right-Click, go to Properties and uncheck Print Object.

Now let's go to Page Layout and set rows to repeat on each page.

I'll add page numbers to the footer.

I'll finish with adding my logo.

I hope watching this video has helped you learn at least a few new things about Excel.

This is the first video of a new series

I'll be creating other useful spreadsheets and showing how to add features to this Gantt Chart

So if you want more, remember to subscribe and tell us what you liked.