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.
