How to Make a Gantt Chart in Excel

Creating a Gantt Chart Template in Excel

In this video, the presenter demonstrates how to create a Gantt Chart template in Excel, including features like the scroll bar and the ability to show the progress of each task.

Setting up the Table

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

• Use CTRL+SHIFT+3 to apply the default date format.

• Finish entering data into the table.

Creating the Timeline

• Create a timeline for the chart area starting with November 1st.

• Use custom number format "d" to display just the day of the month.

• Use text function to display abbreviation for day of week and left function to grab first letter of that text.

• Merge these seven cells to display full date of first day of week.

• Copy group of seven columns to right.

Making Chart Area Dynamic

• Add place to enter project start date and link first cell in chart area to that date.

• Use formula to add one day to each of next dates so that when project start date changes, chart area will change.

Formatting Chart Area

• Turn off gridlines and insert rows at top for title later.

• Apply dark background with light font for table headers.

• Change vertical alignment of entire worksheet to middle.

• Add indenting for tasks and horizontal borders for easier readability.

Adding Finishing Touches

• Merge cells and use custom number format to display day of week, day, month abbreviation, and full year for project start date.

• Add border to show that project start date is meant to be edited.

• Use format painter to copy formatting to next two weeks.

• Use conditional formatting to shade cell when date at top of column is within start and end dates.

• Name cell B3 "project_start" using name box and edit formula to subtract weekday of project start so that weeks in timeline will always start on Monday.

Adding a Scroll Bar and Conditional Formatting

In this section, the speaker demonstrates how to add a scroll bar to a Gantt chart and use conditional formatting to mark today's date and represent progress.

Adding a Scroll Bar

• Add an input for scrolling the Gantt chart.

• Use the Scroll Bar form control in the developer ribbon to change the display week.

• Insert the Scroll Bar by going to Developer > Insert and selecting the scrollbar form control.

• Select the cell to link to, which is "display_week".

Marking Today's Date with Conditional Formatting

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

• Create a formula that compares today's date with the date at the top of the column.

• Add red borders so bars in chart are still visible.

Representing Progress with Data Bars

• Add new columns for task assignment and progress tracking.

• Use conditional formatting with data bars to represent progress within cells.

• Edit formatting rule to represent 0% - 100% using gray color.

• Create formula using names for easier readability.

• Expand days complete by multiplying task_progress times total days (task_end - task_start + 1).

Clearing Cells and Setting Print Settings

In this section, the speaker clears cells and sets print settings for a Gantt Chart.

Clearing Cells

• The speaker clears cells before trying out the Gantt Chart.

Setting Print Settings

• The speaker defines print settings for a Gantt Chart.

• A Gantt Chart usually works better in landscape orientation.

• The speaker sets margins to Narrow.

• The top and bottom margins are changed to half an inch.

• Scaling is changed to fit on one page wide.

• The scrollbar is removed from printing by going to Properties and unchecking Print Object.

• Rows are set to repeat on each page.

• Page numbers are added to the footer.

• The speaker finishes with adding their logo.

Conclusion

In this section, the speaker concludes the video.

Conclusion

• Watching this video has helped viewers learn at least a few new things about Excel.

• This is the first video of a new series where other useful spreadsheets will be created and features will be added to this Gantt Chart.

• Viewers are encouraged to subscribe and provide feedback.